SQL SERVER – Mirrored Backup Introduction and Explanation

SQL Server 2005 Enterprise Edition and Development Edition supports mirrored backup. Mirroring a media set increases backup reliability by adding redundancy of backup media which effectively reduces the impact of backup-device failing. While taking backup of database, same backup is taken on multiple media or locations.

T-SQL code to take Mirrored Backup :

BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AdventureWorksBackup.bak'
MIRROR
TO DISK = 'd:\AdventureWorksBackupCopy.bak'
WITH FORMAT;

Above script will create two backups at two different locations, if backup of one location is corrupted backup from another location will work fine.

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

5 thoughts on “SQL SERVER – Mirrored Backup Introduction and Explanation

  1. Hi Dave,

    The above commad is giving syntax error
    Here goes the error. Please advice wheather it is syntax error or Did I miss anything in the command.

    Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ‘MIRROR’.

    Like

  2. Hello Dave

    Above code is for multiplexing on same server, that can be done by

    BACKUP DATABASE AdventureWorks
    TO DISK = ‘c:\AdventureWorksBackup.bak’,
    DISK = ‘d:\AdventureWorksBackupCopy.bak’
    WITH FORMAT

    My problem is that i have to mirror it on different server which is on same network but there is no dns. When is use below given command it raise error

    BACKUP DATABASE AdventureWorks
    TO DISK = ‘c:\AdventureWorksBackup.bak’
    MIRROR
    TO DISK = ‘\\Myserver\ABCdb\BackupCopy.bak’
    WITH FORMAT

    error is

    Msg 3201, Level 16, State 1, Procedure BackupABCdb, Line 9
    Cannot open backup device ‘\\Myserver\ABCdb\BackupCopy.bak’. Operating system error 5(Access is denied.).
    Msg 3013, Level 16, State 1, Procedure BackupABCdb, Line 9
    BACKUP DATABASE is terminating abnormally

    So, if possible kindly tell me the solution for this problem.
    thanks

    Like

  3. @Bijay Kant

    This might be because of these two things,

    1. First check if the folder on the network drive ( other machine) is shared on not. if it is not shared, it cannot be opened.

    2. Normally when you create a shared folder, you do not give full permissions over the folder to others, to write and make changes, if that is the case then change the properties of the shared folder,

    right click shared folder-> properties->sharing, check if “share this folder is checked and below that click permissions tab and then select full control and select change, click ok.

    Now do the backup

    BACKUP DATABASE database_name
    to DISK = ‘\\computer_name\shared_folder_name\backupcopy.BAK’
    WITH FORMAT

    It should work now.

    Hope this helps.
    Imran.

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

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