In this article we will show you the step by step approach to backup SQL Database using the SQL Server Management Studio, and SQL Query. For this demonstration we are going to use the Adventure Works 2014 database in our SQL Server.
Before we start performing backup, let me show you the actual size of a database. For this, right-click on the Adventure Works Database, and select the properties from the context menu.
As you see, it is 207.25 MB. Remember, you can use this properties window to check the Permissions as well.
Or you can use the following Stored Procedure to check the database size.
-- Backup SQL Server Database Example USE [AdventureWorks2014] GO EXEC sp_spaceused;
Backup SQL Database Example
In order to backup SQL database, right-click on the Adventure Works will open the context menu. Please select the Tasks option, and then select the Back Up.. option as we shown below.
Once you select the Back Up.. option, a new window called Back Up database will be opened as we shown below.
Database: You can use the drop down list to change the Database that you want to backup at any point of time.
Backup Type: There are two types of Backups: Full, and Differential. For now, we are selecting the Full.
Back up to: This property has two option: Disk, and URL. It means, you can save the Backup file in either URL (Company Websites), or DISK. For now, we are leaving the default Disk.
If you observe the below screenshot, SSMS automatically provided the default path as the location to save the backup file. You can use Add, Remove buttons to add new path, and remove existing path. First, let me remove the default path by selecting the path, and clicking the Remove button.
In order to add new path, please click on the Add button. By clicking that button, a new window called Select Backup Destination will be opened as shown below. By default, it is pointing to the default Backup path. Click on the … button to change the destination location
From the below screenshot you can see, we are selecting the D drive -> 1 BACKUPS Folder -> AdventureWorks2014Dup as the file name
Please cross check all the fields before we proceed to next tab.
Under the Overwrite media section, please change the back up to the existing media set option from Append to the existing backup set to overwrite all existing backups.
Use this tab to set the expiry date and time, or to encrypt the backup with custom certificates.
If your database size is larger than please select the Compress backup option as we shown below.
Once you are done configuring all the required settings, please click OK to create a backup.
Let me open the folder. From the below screenshot, you can the backup file.
Create an Encrypt Backup of SQL Database
First, let me change the Destination folder, and file name to AdventureWorks2014Dup2
In order to create an encrypted back up, you have to select the Back up to a new media set, and erase all existing backup sets option.
- New Media set name: Please provide the name of the backup file.
- New media set description: provide a valid description, which is optional.
Under the Algorithm property, you can select the Algorithm as per you requirements. For now, we are selecting the AES 256 algorithm
Currently we don’t have certificates that’s why it is showing empty. But in real-time you might have few certificates to select.
Click OK to close the window.
Backup SQL Database using T-SQL Query
Following examples will help you understand the steps involved in creating backup files to file system using the transact SQL queries.
Backup SQL Database to Default Location
This example will create a backup file to the file system at the default location. And the location is: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup. Here, path may be different for you. It all depends upon the SQL Version, and installed location
-- Backup SQL Server Database Example BACKUP DATABASE AdventureWorks2014 TO DISK = 'AdventureWorks2014Dup3.bak' GO
From the below screenshot, you can see the backup file
Backup SQL Database to Custom Location
This example will create a backup file to the file system. Here we are going to use the custom location. Please change the location as per your requirements.
-- Backup SQL Server Database Example BACKUP DATABASE AdventureWorks2014 TO DISK = 'D: BACKUPS\AdventureWorks2014Dup4.bak' GO
From the below screenshot, you can see the backup file in D folder.
Differential Backup SQL Database to Custom Location
This example will create a differential backup, and save that backup file to the file system.
-- Backup SQL Server Database Example BACKUP DATABASE AdventureWorks2014 TO DISK = 'D: BACKUPS\AdventureWorks2014_Differential.bak' GO
From the below screenshot, you can see the differential backup file in D folder.
Thank You for Visiting Our Blog