Mirrored Database Backup Feature in SQL Server


Jan 7, 2013

Introduction

In this article we will take a look at Mirrored Database Backup Feature in SQL Server. However, this feature is very seldom used by DBA’s. Using this 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

Highly Recommended:- As a DBA your "TOP PRIORITY" should be to make sure your database backups are useful in case of any Disaster and should should know how to recover your database from a Disaster. Learn Different Types of Database Backup Options Supported in SQL Server and How to Fix Database Corruption Issues in SQL Server.

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 transactional 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.bak'
	MIRROR	TO	DISK = 'F:\DatabaseBackups\AdventureWorks_MirrorCopyTwo.bak'
	MIRROR	TO	DISK = 'G:\DatabaseBackups\AdventureWorks_MirrorCopyThree.bak'
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.bak'
RESTORE VERIFYONLY FROM DISK = 'F:\DatabaseBackups\AdventureWorks_MirrorCopyTwo.bak'
RESTORE VERIFYONLY FROM DISK = 'G:\DatabaseBackups\AdventureWorks_MirrorCopyThree.bak'

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

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."