SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

MIRRORED Backup in SQL Server Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

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.

This is Part 12 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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

Read 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.. Begin Tutorial.. Next Page..

Related Articles

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement