MIRRORED Backup in SQL Server Step by Step Tutorial with Examples

Using MIRRORED Backup feature a DBA can create up to 3 identical copies of a database backup. This feature is available in SQL Server 2005 Enterprise Edition and later versions.

Advantages of Mirror Backup in SQL Server

DBA can rely on the identical copy of the database backup during scenarios when one of the Full or Differential or Log backup file is corrupted or damaged.

Disadvantage of Mirror Backup in SQL Server

You need to reserve additional disk space across multiple drives to store database backup files.

Best Practices

• DBA should backup the Mirror Database Backup files to different drives to avoid losing backups due to drive failures
• Use Backup Compression feature in case if you are using SQL Server 2008 Enterprise Edition and higher versions
• Using RESTORE VERIFYONLY command always verify the database backup file whether it is readable or not after every successful backup operation

TSQL Script to Create Full Backup of a Database along with 3 Mirror Copies

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\DatabaseBackups\AdventureWorks.bak'
MIRROR TO DISK = 'E:\DatabaseBackups\AdventureWorks_MirrorCopyOne.bak'
MIRROR TO DISK = 'F:\DatabaseBackups\AdventureWorks_MirrorCopyTwo.bak'
MIRROR TO DISK = 'G:\DatabaseBackups\AdventureWorks_MirrorCopyThree.bak'
WITH FORMAT
GO

TSQL Script to Create Differential Backup of a Database along with 3 Mirror Copies

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\DatabaseBackups\AdventureWorks.bak'
MIRROR TO DISK = 'E:\DatabaseBackups\AdventureWorks_MirrorCopyOne.bak'
MIRROR TO DISK = 'F:\DatabaseBackups\AdventureWorks_MirrorCopyTwo.bak'
MIRROR TO DISK = 'G:\DatabaseBackups\AdventureWorks_MirrorCopyThree.bak'
WITH DIFFERENTIAL
GO

TSQL Script to Create Log Backup of a Database along with 3 Mirror Copies

Database should be in Full recovery model to perform Transaction Log backups. To know read Database Recovery Models in SQL Server.

BACKUP LOG AdventureWorks
TO DISK = 'D:\DatabaseBackups\AdventureWorks.bak'
MIRROR TO DISK = 'E:\DatabaseBackups\AdventureWorks_MirrorCopyOne.trn'
MIRROR TO DISK = 'F:\DatabaseBackups\AdventureWorks_MirrorCopyTwo.trn'
MIRROR TO DISK = 'G:\DatabaseBackups\AdventureWorks_MirrorCopyThree.trn'
GO

How to verify database backup file whether its readable or not in SQL Server

RESTORE VERIFYONLY FROM DISK = 'D:\DatabaseBackups\AdventureWorks.bak'
RESTORE VERIFYONLY FROM DISK = 'E:\DatabaseBackups\AdventureWorks_MirrorCopyOne.trn'
RESTORE VERIFYONLY FROM DISK = 'F:\DatabaseBackups\AdventureWorks_MirrorCopyTwo.trn'
RESTORE VERIFYONLY FROM DISK = 'G:\DatabaseBackups\AdventureWorks_MirrorCopyThree.trn'

Read How to Restore Database in SQL Server to know the detailed steps which you need to follow to restore a database in SQL Server.