Restore from Media Set

Question

Restore from Media Set

AVB

SSChampion

Points: 11418
More actions
September 20, 2004 at 9:47 am

#102211

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??

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

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.

AVB SSChampion Points: 11418 More actions · Answer 2

Thanks Peter. I will have to look into your product.Mirrored backups would be pretty nice. Any words on my restore SP though????

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

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'

Ray Mond SSCertifiable Points: 7159 More actions · Answer 4

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.

AVB SSChampion Points: 11418 More actions · Answer 5

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.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 6

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.

AVB SSChampion Points: 11418 More actions · Answer 7

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!

AVB SSChampion Points: 11418 More actions · Answer 8

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) ??

Ray Mond SSCertifiable Points: 7159 More actions · Answer 9

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.

AVB SSChampion Points: 11418 More actions · Answer 10

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??

Nilssond Hall of Fame Points: 3285 More actions · Answer 11

I would add a predecessor statement like:

exec Master..xp_CmdShell 'NET USE \\server\sharename <password> /USER:<DomainName\Username'

Ray Mond SSCertifiable Points: 7159 More actions · Answer 12

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.

AVB SSChampion Points: 11418 More actions · Answer 13

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.

AVB SSChampion Points: 11418 More actions · Answer 14

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.