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
Launch the Back Up Database Dialog Box
Review the Backup Settings
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.
Run the Backup Command
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 INITto the SQL statement.
- Using Powershell, add
-Initializeto 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.