DB Backup Query.

Conventional and Split File Backup and Restore

Just a day before working on one of the projects, I had to take a backup of one database of 14 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 8 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 7 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Let us see an example of a conventional one-file backup using the AdventureWorks database.

BACKUP DATABASE DATABASENAME

TO DISK='C:\BACKUP\SINGLE\DATABASENAME.BAK'

 

let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE DATABASENAME

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME1.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME2.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME3.BAK'

 

 

Restoring a backup from a single-file backup is quite easy. Let us go over an example where we restore the AdventureWorks database from a single backup file

RESTORE DATABASE DATABASENAME

TO DISK='C:\BACKUP\SINGLE\DATABASENAME.BAK'

 

let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

 

RESTORE DATABASE DATABASENAME

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME1.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME2.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME3.BAK'

 

Mirror Backup of the file

It is quite a common practice to create an exact copy of the backup and store it to several places to deal with any catastrophes which might affect the place where the database is stored. Once a full backup is accomplished DBAs generally copy the database to another location in their network using a third party tools like robocopy or native DOS commands like xcopy.

In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.

Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.

Example 1. Single File Backup to Multiple Locations using Mirror

BACKUP  DATABASE  DATABASENAME

TO= 'C:\BACKUP\SINGLE\DATABASENAME.BAK'

MIRROR TO DISK='C:\BACKUP\MIRROR\DATABASENAME.BAK'

WITH FORMAT

If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMAT reinitializes the backup.

 

Split File Backup to Multiple Locations using Mirror

We have earlier seen an example where we can have multiple split files of large database backup files. SQL Server Mirror functionality also supports backup of the split files

 

BACKUP DATABASE DATABASENAME

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME1.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME2.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME3.BAK'

MIRROR TO DISK='C:\BACKUP\MIRROR\DATABASENAME1.BAK'

                      DISK='C:\BACKUP\MIRROR\DATABASENAME2.BAK'

                      DISK='C:\BACKUP\MIRROR\DATABASENAME3.BAK'

WITH FORMAT

 

 

REFRESH THE VIEW

EXEC sp_refreshview 'LimitView4'

EXEC SP_REFRESHVIEW  'VIEWNAME'