SQL Server Mirrored Database Backups

One of my DBA friend asked me if there is any other tool besides Robocopy to transfer database backups from Production Instance to another server for safekeeping. I asked him if he is aware that this feature is available since SQL Server 2005 Enterprise version and later versions but he was not aware that this feature already exists in SQL. So here I am writing about this useful feature that DBAs rarely use but it can be a very useful in situation where one of the Full, Differential or Tlog backup gets corrupted and we need to recover database from backups.

The only disadvantage of using this feature is that we need additional disk space to contain multiple backups.

A mirrored backup at a given time will allow us to create 2 – 4 identical copies of a database backup. Mirrored backup can be taken in local computer and also over the network.

Now let me give an example of Mirrored Full backup:

BACKUP DATABASE [AdventureWorks2012]
TO DISK =‘S:\Database\Backup1\AdventureWorks_20140908_1.bak’
MIRROR TO DISK =‘S:\Database\Backup2\AdventureWorks_20140908_2.bak’
MIRROR TO DISK =‘S:\Database\Backup3\AdventureWorks_20140908_3.bak’
MIRROR TO DISK =‘S:\Database\Backup4\AdventureWorks_20140908_4.bak’
WITH FORMAT
,CHECKSUM
,INIT
,NAME = N’AdventureWorks2012-Full Database Backup’
,COMPRESSION
,STATS = 10

We have to use the WITH FORMAT option to create new mirrored backup set else Database Backup Command with fail.

Hope this article explains the subject and if there is any issue then you can post a note and I will be happy to answer your question .