MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

















Trending SQL Server Tips










MIRRORED Backup in SQL Server Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: FILE Backups in SQL Server

Page 12 / 16


Click here to read this tutorial from the beginning...

Introduction

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 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'

Note: 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.




Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Previous Page.. Next Page..


Learn More...




Last Updated On: Feb 12, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: