SQL Server 2005 onwards includes the mirroring of backup media sets to provide redundancy of your critical database backups. Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. These malfunctions are very serious because backups are the last line of defense against data loss. SQL Server 2005 Standard Edition supports only a single backup copy during your backup operations. Depending on your requirements, SQL Server Enterprise allows you to create up to four mirrored media sets.
Benefits
Mirrored backup media sets improve availability by minimizing downtime during restore operations. A damaged backup could result in a longer restore time, or a restore failure. As databases grow, the probability increases that the failure of a backup device or media will make a backup unrestorable. Restoring a database is time sensitive, and mirrored backup media sets give you added protection to get your application fully functional more quickly.
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 AdventureWorks2012
TO DISK = ‘d:AdventureWorksBackup1.bak’
MIRROR TO DISK = ‘d:AdventureWorksBackupCopy.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.
Example 2. Multiple File Backup to Multiple Locations using Mirror
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘d:AdventureWorksBackup1.bak’,
disk = ‘d:AdventureWorksBackup2.bak’
MIRROR TO DISK = ‘d:AdventureWorksBackupCopy.bak’,
DISK = ‘d:AdventureWorksBackupCopy1.bak’
with format
Example 3. Only 4 Mirror are allowed (1 Backup file + 3 Mirror files)
Example 4. Different number of backup file & Mirror files – Error Occur
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
thanks for informative post. i am pleased sure this post has helped me save many hours of browsing other similar posts just to find what i was looking for.
good post for startup DBAs that they have this option and for sure they have consider enterprise option only when they recommand for new projects