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:

DISK = N'D:\AdventureWorks.bak'
, DISK = N'E:\AdventureWorks.bak'
NAME = N'AdventureWorks-Full Database Backup'

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:

DISK = N'D:\AdventureWorks.bak'
MIRROR TO DISK = N'E:\AdventureWorks.bak'
NAME = N'AdventureWorks-Full Database Backup'

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 (https://blog.sqlauthority.com)

SQL Backup and Restore, SQL Scripts
Previous Post
SQLAuthority News – SQLAuthority News – Presenting at South East Asia SharePoint Conference – Demo Details
Next Post
SQL SERVER – Corrupted Backup File and Unsuccessful Restore

Related Posts

12 Comments. Leave new

  • Dear Pinal Sir,

    I was download one database from internet but i
    don’t know how to attach it to sql server so please tell me

    thank you

    Ashutosh S.

  • ivan mohapatra
    May 26, 2011 5:28 pm

    let me make it clear

    1- is my windows server where all the website and database is been hosted and is live .
    2 -is my laptop.

    if i am taking full backup from my laptop of database
    backup db to disk = ‘1-C-drive ‘ it is running successfully
    as per example
    here {1} means——- server C drive path—–
    backup db to disk = ‘2-C-drive ‘it is throughing error
    here {2} means—– laptop C drive path.——

  • Bharath Ayyagari
    September 9, 2011 5:28 pm

    How to create Database Backup using Triggers.

  • Sir,
    I have Microsoft SQL Server 2008 R2 Standard edition that hosted Microsoft Dynamics AX 2009 databases.

    IT Teams did not perform any backup on OS.

    The databases was backing up in local drive by previous DBA.
    I am planning to purchase an external hard drive 5 TB and connected to it via USB.
    Then, performed the SQL Database backup.

    Would you advise and recommend the best options?

  • Way we use WITH FROMATE, INIT command sir.

  • NICE

  • Thanks sir your experience and knowledge share is wonderful.
    I am reading your article from last two months and i found an amazing things.

  • i am using sqlserver 2005 and is not working.

    Backup mirroring is not available in this edition of SQL Server. See Books Online for more details on feature support in different SQL Server editions.


Leave a Reply