SQL Server DATABASE BACKUP With Multiple Methods Using Transact-SQL

Every database developer and Database Administrator should be familiar with these methods. I will first provide a brief introduction to SQL Server databases and backup.

About SQL Server DATABASE

A database is a collection of data sets, SQL Server database consists of at least two physical files on the hard drive, an MDF file and an LDF file. The MDF file contains all of the real data. The LDF file, or Log file, contains change records for ecah data. By default, both the MDF and LDF files are located in the SQL Server, as in the following screen.

 

About SQL Server BACKUP

Backs up a complete SQL Server database to create a database backup, or one or more files or file groups of the database to create a file backup (BACKUP DATABASE).

We can create a SQL Server DATABASE BACKUP with multiple methods using Transact-SQL, as in the following:

• Full backups to disk
• Differential backup
• File level backup
• Filegroup backup
• Full backups with progress stats
• Backup with description
• Mirrored backup
• Backup with multiple options
• Backup with [Media Name]
  
• Backup with Expiration Date
  
• Backup with Logical Backup Device
• Backup with Logical Backup Device for tape

Now I am creating a SQL Server database backup using all the above method of the [DBAWorks] database.

Full backups to disk

A Full backup is a complete backup of the database and the To Disk specifies the location to create the backup. Now take the database "DBAWorks" to create a backup.

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\DBAWorks.BAK'
GO

 

 

 Differential backup

A Differential backup is based on the latest full backup of data. A Differential backup we can be created using a .BAK or .DIF file with the Differential command.

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks].DIF'
WITH DIFFERENTIAL
GO

 

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks1].BAK'
WITH DIFFERENTIAL
GO

 

 File level backup

Now I am creating a File level backup. First I will specify the logical filename within the database that can be obtained with the command sp_helpdb [DBAWorks]

 For example:

USE [Master]
GO
BACKUP DATABASE [DBAWorks] FILE= 'DBAWorks'
TO DISK = 'D:\backup\NewBackup\[DBAWorks].FIL'
GO

Filegroup backup

Now I am creating a Filegroup backup. First I will specify the logical filename within the database that can be obtained with the command sp_helpdb [DBAWorks]

 For example:

USE [Master]
GO
BACKUP DATABASE [DBAWorks] FILEGROUP = 'PRIMARY'
TO DISK = 'D:\backup\NewBackup\[DBAWorks].FLG'
GO

Full backups with progress stats

Stats display the progress of the backup. By default it shows the progress after every 10%. You can set the stats value to whatever you want.

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks_DefaultStats].BAK'
WITH STATS
GO

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks_statsValue].BAK'
WITH STATS=5
GO

 

Backup with description

This option gives the backup name. The maximum size is 255 characters we can use it, this can later be used with some of the restore commands to see what is contained with the backup.

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks_Des].BAK'
WITH DESCRIPTION = 'Full backup for DBAWorks'
GO

Mirrored backup

Mirrored backup allows you to create multiple copies of the backups, preferably to different locations.

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks_M].BAK' --main drive
MIRROR TO DISK = 'D:\backup\NewBackup\[DBAWorks_mirror].BAK'-- mirror drive you can change path
WITH FORMAT
GO

Backup with multiple options

You can define multiples options in it as in the following:

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorksP].BAK'
MIRROR TO DISK = 'D:\backup\NewBackup\[DBAWorks_mirror1].BAK'
WITH FORMAT, STATS
GO

 

Backup with [Media Name]

A database backup to disk using a format to create a new media set.

USE [Master]
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\DBAWorks_Media.Bak'
WITH FORMAT,
MEDIANAME ='my_SQLServerBackups',
NAME = 'Full Backup of DBAWorks';
GO

Backup with Expiration Date

You can specify an expiration date for creating the backup as in the following:

USE [Master];
GO
BACKUP DATABASE [DBAWorks]
TO DISK = 'D:\backup\NewBackup\[DBAWorks_date].Bak'
WITH EXPIREDATE = '01/31/2015'
GO

 Backup with Logical Backup Device

Now we create a logical backup device, DBABackup, for a backup disk file. The example then backs up the [DBAWorks] database to this logical backup device.

USE [Master];
GO
EXEC sp_addumpdevice 'disk', 'DBABackup',
'D:\backup\NewBackup\[DBAWorks_Drive].bak';
GO
BACKUP DATABASE [DBAWorks]
TO DBABackup
WITH FORMAT

You can also create network device as same

Syntax:-

USE [Master];
GO
EXEC sp_addumpdevice 'disk', 'networkdevice',
'\\<servername>\<sharename>\<path>\<filename>.bak'

 

 Backup with Logical Backup Device for tape

Now we create a logical backup device, DBATapeBackup, for a backup disk file. The example then backs up the [DBAWorks] database to this logical backup device.

USE [Master];
GO
EXEC sp_addumpdevice 'tape', 'DBATapeBackup', '\\.\tape0';
USE [DBAWorks];
GO
BACKUP DATABASE [DBAWorks]
TO TDBATapeBackup
WITH FORMAT,
MEDIANAME = 'MyDBABackup_Tape',
MEDIADESCRIPTION = '\\.\tape0',
NAME = 'Full Backup of DBAWorks'
GO

 You can see the following query how many backups have been created in the database.

SELECT a.database_name ,
b.physical_device_name ,
a.type ,
a.expiration_date ,
a.name ,
a.description
FROM msdb.dbo.backupset a
LEFT JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id

 

You can also see in the following path: