As a SQL Server consultant, I often come across interesting scenarios and questions from clients. Recently, a client approached me with a backup strategy that involved taking two copies of their database simultaneously. They believed that this approach would reduce the size of the backup file. Intrigued by their claim, I asked them to share the script they were using.

Here is the script they provided:

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
, DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO

Upon reviewing the script, I realized that it did not actually create two copies of the database. Instead, it split the backup file into two parts. When restoring the database, both parts would be required. This splitting of the backup file was the reason why the size appeared to be reduced.

To achieve the desired outcome of having two separate copies of the database, the client needed to use the MIRROR TO command. Here is the corrected script:

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
MIRROR TO DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO

With this script, the backup is mirrored to another location, creating an exact copy of the database. When checking the size of the backup file, it will not be reduced.

It’s important to understand the difference between splitting a backup file and mirroring a backup. Splitting a backup file can be useful for distributing the backup across multiple storage devices, but it does not create independent copies of the database. Mirroring a backup, on the other hand, creates an exact replica of the database in a separate location.

If you’re interested in learning more about mirrored backup and restore, I have written an in-depth article on the topic. You can find it here: [link to the article]

Do you use the MIRROR TO command in your production environment? Share your experiences and thoughts in the comments below!