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

Previous Post
SQL SERVER – Delete Duplicate Records – Count Duplicate Records Links
Next Post
SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed

Related Posts

5 Comments. Leave new

  • 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’.

    Reply
  • 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

    Reply
  • Imran Mohammed
    August 20, 2008 10:36 pm

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

    Reply
  • This is Siraj,

    I am trying to login mixed mode to windows mod please explain this issue in sqlserver.

    Reply

Leave a Reply Cancel reply

Exit mobile version