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)

About these ads

7 thoughts on “SQL SERVER – Taking Multiple Backup of Database in Single Command – Mirrored Database Backup

  1. 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.

  2. 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.

    ok
    1-
    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—–
    and
    2-
    backup db to disk = ’2-C-drive ‘it is throughing error
    here {2} means—– laptop C drive path.——

  3. 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.

    Questions:
    Would you advise and recommend the best options?
    Thanks,
    Edwin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s