SQL Backup

A database backup in SQL Server is a copy of database data stored separately to allow recovery in case of data loss or system failure.It has methods to create a full database backup such as:

• Using SQL Server Management Studio (SSMS) : Create backups through the graphical interface.
• Using Transact-SQL (T-SQL) : Connect to the Database Engine, open a new query window and execute backup commands.

Syntax

BACKUP DATABASE databasename TO backup_device [ ] [ WITH with_options [] ] ;

Here:

• databasename: The name of the database that needs to be backed up.
• backup_device [ DISK | TAPE ] :Specifies one or more backup devices (from 1 to 64) that will be used for the backup operation.
• WITH with_options: Defines one or more additional backup options.

Below are various options for creating backups: 

1. Create a full SQL Server backup to disk

We can create a full SQL Server backup to disk using the following statement:

BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName.BAK'
GO

Here, BACKUP DATABASE DatabaseName is the instruction. The "TO DISK" option specifies that the backup should be written to drive, as well as the backup location and filename.

2. Create a differential SQL Server backup

We can create a differential SQL Server backup using the following statement:

BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName.BAK'  
WITH DIFFERENTIAL  
GO

3. Create a file-level SQL Server backup

We can create a file-level SQL Server backup using the following statement:

BACKUP DATABASE DatabaseName FILE = 'DatabaseName'  
TO DISK = 'C:\DatabaseName_DatabaseName.FILE'
GO

Here, "WITH FILE" option is used to define a file backup in this command. The logical filename of the database must be defined.

4. Create a filegroup SQL Server backup

We can create a filegroup SQL Server backup using the following statement:

BACKUP DATABASE DatabaseName FILEGROUP = 'ReadOnly'  
TO DISK = 'C:\DatabaseName.FLG'
GO

5. Create a full SQL Server backup to multiple disk files

We can create a full SQL Server backup to multiple disk files using the following statement:

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK',
DISK = 'D:\DatabaseName_2.BAK',
DISK = 'E:\DatabaseName_3.BAK'
GO

Here, instead of one big file, this command uses the "DISK" option several times to write the backup to three smaller files of similar size.

6. Create a full SQL Server backup with a password

We can create a full SQL Server backup with a password using the following statement:

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK'
WITH PASSWORD = 'Q!W@E#R$'
GO

Here, "WITH PASSWORD" command produces a backup with a password that must be entered when the database is restored.

7. Create a full SQL Server backup with progress stats

We can create a full SQL Server backup with progress stats using the following statement:

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK'
WITH STATS
GO

Here, "WITH STATS" command generates a complete backup and also shows the backup's progress. The default setting is to display improvement every ten percent.

Here is another sample for showing stats after every 2%.

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK'
WITH STATS = 2
GO

8. Create a SQL Server backup and give it a description

We can create a SQL Server backup and give it a description using the following statement:

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK'
WITH DESCRIPTION = 'Full backup for DatabaseName'
GO

To give the backup a name, "WITH DESCRIPTION" command uses the description option. This can be used for any of the restore commands in the future to see what's in the backup. The maximum number of characters is 255.

9. Create a mirrored SQL Server backup

We can create a mirrored SQL Server backup using the following statement:

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK'
MIRROR TO DISK =  'D:\DatabaseName_mirror.BAK'
WITH FORMAT
GO

This choice allows you to make several backup copies, ideally in different locations.

10. Specifying multiple options for SQL Server Backups

We can specify multiple options for SQL Server Backups using the following statement:

BACKUP DATABASE DatabaseName 
TO DISK = 'C:\DatabaseName_1.BAK'
MIRROR TO DISK =  'D:\DatabaseName_mirror.BAK'
WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$'
GO

Example:

Creating Database:

CREATE DATABASE GeeksDB;
GO

To use this database:

USE GeeksDB;
GO

Backup this database: 

BACKUP DATABASE GeeksDB
TO DISK = 'D:\Backup\GeeksDB.bak'
 WITH FORMAT,
 MEDIANAME = 'GeeksDBBackup',
 NAME = 'Full Backup of GeeksDB';
GO

Output: