Home » CodeProject » SQL Server 2005 Onwards – Mirrored Backup || Cool Feature

SQL Server 2005 Onwards – Mirrored Backup || Cool Feature

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/)


2 Comments

  1. carol says:

    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.

  2. Dilip says:

    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

Leave a Reply

%d bloggers like this: