Backup SQL Database

Let us see the step by step approach to backup SQL Database using the SQL Server Management Studio and SQL Query. For this Backup SQL Database demonstration, we are going to use the Adventure Works database in our SQL Server.

Before we start performing the SQL Server backup, let me show you the actual size of a database. For this, right-click on the SQL Server Adventure Works Database, and select the properties.

Backup SQL Database 2

It is 207.25 MB. Use this properties window to check the Permissions as well.

Backup SQL Database 3

Or you can use this Stored Procedure to check the database size.

-- Backup SQL Server Database Example
USE [AdventureWorks2014]
GO
EXEC sp_spaceused;
Backup SQL Database 4

Backup SQL Database using T-SQL Query

The following SQL examples will help you understand the steps involved in creating database 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, the 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
Processed 24328 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24330 pages in 6.411 seconds (29.647 MB/sec).

See the backup file

Backup SQL Database 23

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:\1 BACKUPS\AdventureWorks2014Dup4.bak'
GO
Processed 24328 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24330 pages in 7.658 seconds (24.819 MB/sec).

See the Database backup file in the D folder.

Backup SQL Database 25

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:\1 BACKUPS\AdventureWorks2014_Differential.bak'
GO
Processed 40 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 1 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.197 seconds (1.625 MB/sec).

See the differential backup database file in the D folder.

Backup SQL Database 27

Backup SQL Database using Management Studio

To backup the SQL database, right-click on the Adventure Works, select the Tasks option, and then select the Back Up.. option.

Backup SQL Database 5

Once you select the Back Up.. option, a new window called Back Up database will open.

Database: You can use the drop-down list to change the Database that you want to backup at any point in time.

Backup SQL Database 7

Backup Type: There are two types of SQL Database Backups, and they are Full and Differential. For now, we are selecting the Full.

Backup SQL Database 8

Back up to: This property has two options: 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.

As you can see, SSMS automatically provided the default path as the location to save the backup file. You can use Add, Remove buttons to add a new path and remove the existing path. First, let me remove the default path by selecting the path and clicking the Remove button.

Backup SQL Database 9

To add a new path, please click on the Add button. By clicking that button, a new window called Select Backup Destination will open. By default, it is pointing to the default Backup path. Click on the … button to change the destination location

Backup SQL Database 10

We are selecting the D drive -> 1 BACKUPS Folder -> AdventureWorks2014Dup as the file name

Backup SQL Database 11

Please cross-check all the fields before we proceed to the next tab.

Backup SQL Database 12

Media Options: 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.

Backup SQL Database 13

Backup Options: Use this tab, to set the expiry date and time, or to encrypt the backup with custom certificates. If your database size is huge, please select the Compress backup option.

Backup SQL Database 15

Please click OK to create a Database backup in SQL Server.

Backup SQL Database 16

Let me open the folder to see the backup file.

Backup SQL Database 17

Create an Encrypt Backup of SQL Database

First, let me change the Destination folder, and file name to AdventureWorks2014Dup2

Backup SQL Database 18

Media Options: To create an encrypted Database backup in SQL Server, you have to select the Back up to a new media set and erase all existing backup sets option.

  • New Media set name: Name of the backup file.
  • New media set description: A valid description.
Backup SQL Database 19

Backup Options: Under the Algorithm property, you can select the Algorithm. We are choosing the AES 256 algorithm

Backup SQL Database 20

Currently, we don’t have certificates; that’s why it is showing empty. But in real-time you might have few certificates to select. Next, click OK to close the window.

Backup SQL Database 21