SQL Backup

In SQL Server, a backup, or data backup is a copy of computer data that is created and stored in a different location so that it can be used to recover the original in the event of a data loss. To create a full database backup, the below methods could be used :

1. Using the SQL Server Management Studio in Microsoft SQL Server. 

2. Using the Transact-SQL:

• Connect to the Database Engine.
• Open New Query

Syntax

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

Where, 

• databasename is the database that needs to be backed up.
• backup_device [  DISK | TAPE ] declares a list of backup devices from 1 to 64 to be used for the backup operation.
• WITH with_options [ ] defines one or more options mentioned below:
• COMPRESSION | NO_COMPRESSION defines whether backup compression is performed on this backup or not.
• DESCRIPTION could have a maximum of 255 characters and describes the backup set.
• NAME could have a maximum of 128 characters and describes the name of the backup set.
• FORMAT [MEDIANAME] [MEDIADESCRIPTION] could be used while using media for the first time or to overwrite all existing data.

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: