Web.QHMIT.com

Backup a Database in SQL Server 2017

SQL Server provides an easy way to create a backup of a database. Backups can be done either with Transact-SQL, PowerShell, or via the GUI.

Here, I'll demonstrate how to create a backup using the SQL Operations Studio (now called Azure Data Studio) GUI, then using Transact-SQL, then finally, with SQL Server Powershell.

Create a Backup via the SQLOPS/Azure Data Studio GUI

  1. Open the Database Dashboard

    In the left pane, right-click the database that you'd like to back up and select Manage.

    Screenshot of SQLOPS with the Manage option highlighted

  2. Launch the Backup Database Dialog Box

    Click the Backup button.

    Screenshot of SQLOPS with the Backup button highlighted

  3. Review the Settings

    This dialog box gives you the opportunity to change any of the settings if required.

    For our example, leave it at the default settings and click Backup to create the backup.

    Screenshot of Backup dialog box

  4. Backup Complete

    Once the backup is complete, a message is displayed confirming that the backup succeeded.

    Screenshot of SQLOPS with the Backup Database succeeded message

Backup a Database Using Transact-SQL

Screenshot of backing up a database using Transact-SQL.

You can perform the same backup as above using T-SQL.

To do this, open a new query window and execute a BACKUP statement.

The BACKUP statement accepts various options (just like the GUI option), but you can also run a simple backup with a minimum of code.

Below is an example of a simple backup script on a Linux or Mac system. The script specifies the database to backup, and the location to back it up to.

On a Windows system, the path will use backslashes:

After running this code, the backup file will be located at the specified location.

Backup a Database using PowerShell

SQL Server 2017 supports Windows PowerShell, which is a scripting shell, typically used to automate administration and deployment tasks.

The PowerShell language supports more complex logic than Transact-SQL scripts, which gives you the ability to build more sophisticated scripts for your backups and other tasks.

The following code will create a backup just like the previous examples. Just replace MyServer with the name of your server.

You can also specify a location

After running this code, the backup file will be located at the default location.

You can also specify -BackupAction Database to explicitly state that it is a full backup. However, this is the default option.

You can see the full documentation for the Backup-SqlDatabase command at the Microsoft website.

Overwriting Backup Files

If you perform multiple backups using the same file name for the backup file, you might notice that each time you run a backup, the file size of the backup file increases.

This is because each successive backup is adding itself to the existing file. It's doing this because you are using the same file name and you have not explicitly specified that each backup should overwrite any existing file.

There's an option that allows you to overwrite the existing file.

Using Unique File Names

However, it's often a good idea to create a full backup with a unique filename (typically including the date in the file name). Having a unique filename will mean each backup will be a separate file.

Also, depending on the size of your database, and how much new data is being entered into it, you may wish to supplement your full backups with differential backups. A differential backup captures only the data that has changed since the most recent full backup.