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 GUI, then using Transact-SQL, then finally, with SQL Server Powershell.
Create a Backup via the SQLOPS GUI
Open the Database Dashboard
In the left pane, right-click the database that you'd like to back up and select Manage.
Launch the Backup Database Dialog Box
Click the Backup button.
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.
Once the backup is complete, a message is displayed confirming that the backup succeeded.
Backup 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 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 the SQLOPS GUI, click on Advanced Configuration and under Backup to the existing media set, select Overwrite all existing backup sets.
- Using the SSMS 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.
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.