MIRRORED Backup in SQL Server Step by Step Tutorial with Examples

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'

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

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples