SQL SERVER – Taking Multiple Backup of Database in Single Command – Mirrored Database Backup

SQL SERVER – Taking Multiple Backup of Database in Single Command – Mirrored Database Backup: "

I recently had a very interesting experience. In one of my recent consultancy works, I was told by our client that they are going to take the backup of the database and will also a copy of it at the same time. I expressed that it was surely possible if they were going to use a mirror command. In addition, they told me that whenever they take two copies of the database, the size of the database, is always reduced. Now this was something not clear to me, I said it was not possible and so I asked them to show me the script.

Here was their script:

BACKUP DATABASE [AdventureWorks] TO

DISK = N'D:\AdventureWorks.bak'

, DISK = N'E:\AdventureWorks.bak'

WITH FORMAT, INIT,

NAME = N'AdventureWorks-Full Database Backup'

GO

This script was very interesting to me. There is nothing wrong with it; however it does not duplicate your data. In fact, it splits your backup file in two parts and, when you restore, you will need both of these files. This was the reason why the size of the backup file was reduced whenever they took a backup on two places.

What they really needed was the following script wherein the full backup would be mirrored to another backup location:

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

In this case, when you check the size of the backup, you will notice that its size is not reduced as well.

I have previously written on this subject in very much detail. Here is the in-depth article about the same topic:

SQL SERVER – Mirrored Backup and Restore and Split File Backup

Do you use this MIRROR TO command in your production environment?

Reference : Pinal Dave (http://blog.sqlauthority.com)

Filed under: Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology "