Mirrored Backups

Mirrored backup is a feature available in SQL Server 2005 Enterprise Edition and Development Edition. Mirroring a media set increases backup reliability by adding redundancy of backup media which effectively reduces the impact of backup-device failing. While taking backup of database, same backup is taken on multiple media or locations.

The following command creates a mirrored backup of database Deepak. It will create two backups at two different locations. If backup of one location is corrupted or if the drive containing one of these backup fails, we can utilize the backup file from other location.

BACKUP DATABASE Deepak
TO DISK = 'C:\DeepakBackup.bak'
MIRROR
TO DISK = 'D:\DeepakBackupCopy.bak'
WITH FORMAT

Incase the backup available in C:Drive is corrupted then we can use the one from D:drive as shown below,

RESTORE DATABASE Deepak
FROM DISK ='D:\DeepakBackupCopy.bak'

The below command creates backup of the database Deepak to two devices,

BACKUP DATABASE Deepak
TO DISK = 'C:\DeepakBackup1.bak',
DISK = 'D:\DeepakBackup2.bak'

Similarly while restoring backup from devices we need to mention both the devices if not the below error message will be thrown. I have spitted the backup of database Deepak to C: and D: drive and while restoring I’ve mentioned only a single device and hence I receive the error message as shown below.

RESTORE DATABASE Deepak
FROM DISK ='D:\DeepakBackup1.bak'
WITH REPLACE

Msg 3132, Level 16, State 1, Line 1

The media set has 2 media families but only 1 are provided. All members must be provided.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

The correct syntax should be specified as shown below indicating both the devices while restoring the databases.

RESTORE DATABASE Deepak
FROM DISK ='C:\DeepakBackup1.bak',
DISK='D:\DeepakBackup2.bak'
WITH REPLACE

Similarly the below command splits the backup to two devices and also mirrors them.

BACKUP DATABASE Deepak
TO DISK = 'C:\DeepakBackup1.bak',
DISK = 'D:\DeepakBackup2.bak'
MIRROR
TO DISK = 'E:\DeepakBackup1Copy.bak',
DISK = 'F:\DeepakBackup2Copy.bak'
WITH FORMAT

While restoring the database we need to specify the exact device name. In the above example the backup of the file C:DeepakBackup1.bak is mirrored to E:DeepakBackup1Copy.bak and similarly D:DeepakBackup2.bak is mirrored to F:DeepakBackup2Copy.bak and hence we need to specify the exact device while restoring.

In the below example I’ve specified the file name from C: and E: drive which is incorrect as both holds the same backup file.

RESTORE DATABASE Deepak
FROM DISK ='C:\DeepakBackup1.bak',
DISK='F:\DeepakBackup2Copy.bak'
WITH REPLACE

Msg 3227, Level 16, State 1, Line 1

The backup media on “C:DeepakBackup1.bak” is part of media family 1 which has already been processed on “E:DeepakBackup1Copy.bak”. Ensure that backup devices are correctly specified. For tape devices, ensure that the correct volumes are loaded.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Instead I need to mention C: and F: drive or D: and E:drive while restoring as shown below. Both are correct. Thus using mirrored backups we can mix the devices and restore in case of emergency.

RESTORE DATABASE Deepak
FROM DISK ='D:\DeepakBackup2.bak',
DISK='E:\DeepakBackup1Copy.bak'
WITH REPLACE