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)
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’.
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
@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.
This is Siraj,
I am trying to login mixed mode to windows mod please explain this issue in sqlserver.