SQL Server 2016: Backup a Database

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 SQL Server Management Studio GUI.

Here, I'll demonstrate how to create a backup using the SQL Server Management System GUI, then using Transact-SQL, then finally, with SQL Server Powershell.

Create a Backup via the GUI

  1. Launch the Back Up Database Dialog Box

    Screenshot of backing up a database in SQL Server 2016.

    In the Object Explorer, right-click on the database you'd like to back up, and select Tasks > Back Up... from the contextual menu.

  2. Review the Backup Settings

    Screenshot of backing up a database in SQL Server 2016.

    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 OK to create the backup.

    You can change the database here if you accidentally selected the wrong one at the previous step.

  3. Backup Complete

    Screenshot of backing up a database in SQL Server 2016.

    You'll receive a message when the backup is complete.

    Click OK to close the message and dialog box.

    The backup file will now be located at the specified location.

Backup a Database Using Transact-SQL

Backup a Database using PowerShell

SQL Server 2016 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.

  1. Open PowerShell

    Screenshot of backing up a database using PowerShell.

    Right-click on the database and select Start Powershell.

  2. Run the Backup Command

    Screenshot of backing up a database using PowerShell.

    Enter the command for creating the backup and press Enter (or Return, depending on your keyboard).

    The backup will run immediately.

    Sample Code

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

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

    You can also specify a 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 ran all of the above examples exactly as they are, you might have noticed that each time you ran it, the file size of the backup file increased.

This is because each successive backup is adding itself to the existing file.

It's doing this because we are using the same file name, and we 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 the GUI, click on Media Options in the left menu of the Back Up Database dialog box, and select Overwrite all existing backup sets in the Overwrite Media section.
  • Using SQL add WITH INIT to the SQL statement.
  • Using Powershell, add -Initialize to the command.

Saving Backup Files

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.