Restore from Media Set
-
I created 3 disk backup devices to Server A,B and C. all on server A. I then back up our production database to the 3 devices via one Job. I created a Media Set with these 3 devices and have successfully backed up the database. Step two of my job is to run an SP that restores the production server DB from Server A onto Server B so I have an updated version of the DB. Problem is I am not sure how to code my SP to update including all the devices/Media Set. This is what I have so far:
RESTORE DATABASE Database_Name
FROM DISK = 'c:\..\, \\ServerB\..\, \\ServerC\..\'
WITH MEDIANAME = 'ALL SERVERS'
Is SQL writng a portion of the DB to all 3 devices forcing me to have all files to restore?? Or is it doing a full backup to all devices??
-
SQL is writing a portion of the DB to all 3 devices. Thus, you need all 3 files when you perform the restore.
SQL 2005 will support mirrored backups. If you need mirrored backups for SQL 2000, try our product, MiniSQLBackup. In addition, it is able to compress and encrypt your backups.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Thanks Peter. I will have to look into your product.Mirrored backups would be pretty nice. Any words on my restore SP though????
-
If you want to restore database from server A to server B you need to do the following.
1. Create the procedure to restore the database on server B.
2. Execute the procedure using linked server.
Also, please specify the UNC path for server a.
RESTORE DATABASE Database_Name
FROM DISK = '\\ServerA\.\, \\ServerB\..\, \\ServerC\..\'
WITH MEDIANAME = 'ALL SERVERS'
-
The suggestion provided by sa24 should work.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Thanks for the responses. I have created an SP that will restore the database on Server B when there isn't a media set involved but the sample I provided you for mulitple backup devices is not complete nor does it work. I was hoping maybe someone had code for a similiar situation. Thanks for your time.
-
What's the error message?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
This is from the logs. I have delved into permissions issues but I run all instances of SQL on all servers with the same account- an enterprise admin account.
BackupDiskFile:
penMedia: Backup device '\\SERVER A\..\,\\SERVER B\..\, \\SERVER C\..\' failed to open. Operating system error = 53(The network path was not found.). The network paths are all valid, I have triple checked them. Unless my syntax is incorrect(which had a successful syntax check) I am lost!
-
How do you break up a media set (don't want to delete the devices if I don't have to)??? Can I just delete it from the backupmediaset table or do I need to remove all associations in the other 3 tables (backupset,backupfile and backupmediafamily) ??
-
My guess is that server B does not have adequate rights to the other servers' folders. Check your SQL Server startup service account. It cannot be a LocalSystem account if you want to access remote folders. You have probably set this right on server A, since you could back up to servers B and C.
See http://support.microsoft.com/?kbid=555128 for more details.
To quickly check if the startup service account has rights to remote folder rights, run master..xp_cmdshell 'dir \\serverC\..' from within Query Analyzer on server B.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Peter thanks for the suggestions. I don't mean to take up all your time. I double checked all permisions for the SQL Agent and Server and have run xp_cmdshell dir... that works fine between all three servers. The SQL agent and Server has always been set up with a domain enterprise admin account and run with those credientials. Unless my coding is wrong(most likely) I am unsure of what the problem is. Here is what I run in QA to test
RESTORE DATABASE MTS_AVB1
FROM DISK = '\\AVB1\C$\MSDE\MTS + MASTER\MTS RMT BACKUP\mts.bak,\\brokerage135\c$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\mts3.bak,\\BROKERAGESQL\F$\BACKUP\MTS\mts2.bak'
WITH MEDIANAME = 'TEST MEDIASET'
Also, how do you "break" media sets so I can get my original backup devices back that I added to a set??
-
I would add a predecessor statement like:
exec Master..xp_CmdShell 'NET USE \\server\sharename <password> /USER:<DomainName\Username'
-
I have never used the MEDIANAME option, and do not know the effects of doing so. Why do you need to use the MEDIANAME option if you are backing up to disks?
Were you successful in restoring the database when running the query you described in Query Analyzer?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Good question about the MEDIANAME option, you can assign MEDIANAME to a disk backup device (not sure why I did at the time). I since recreated 3 disk backup devices without a medianame, I have backed up the DB in one step of one job with these devices and have attempted to restore with this:
Restore Database MTS_AVB1
From DISK = '\\AVB1\c$\msde\backup\MTS_TEST,\\Brokeragesql\f$\backup\mts\MTS_TEST2,\\Brokerage135\c$\bkg\sqldata\MTS_TEST3'
WITH MOVE 'MTS_dat' TO 'c:\msde\data\MTS.mdf',
MOVE 'MTS_log' TO 'c:\msde\data\MTS.ldf',
REPLACE
GO
But it keeps erroring with:
failed to open. Operating system error = 3(The system cannot find the path specified.).
All those UNC paths are correct. I get that error if I remove the code from WITH MOVE to the end too.
-
I Figured it out!!! This is what I needed to do:
Restore Database MTS_AVB1
From DISK = '\\Brokerage135\c$\bkg\sqldata\MTS_TEST3',
DISK = '\\Brokeragesql\f$\backup\mts\MTS_TEST2',
DISK = '\\AVB1\c$\msde\backup\MTS_TEST'
WITH MOVE 'MTS_dat' TO 'c:\msde\data\MTS.mdf',
MOVE 'MTS_log' TO 'c:\msde\data\MTS.ldf',
REPLACE
GO
you have to have Disk = before each location. Thanks for the help and advice.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply