We've got collections of database and log backups created by the usual maintenance plans on a 2000 Enterprise machine. I'm trying to run through a restore onto a new 2005 machine (Developer Edition on my test workstation) using the collection of .bak and .trn files copied from the 2000 server. When I try to restore to a new database on 2005 via SSMS, and select all the .bak and .trn files for the restore, I get the ol' "The volume on device '[trimmed]' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set." error.
I'm assuming this just means that SQL Server can't verify that these log backups are in fact part of a functional "set", even if they aren't part of a traditional backup media set. Is there any way to tell SSMS, "It's okay man, just restore the database from these files, in this order - trust me," or is the only solution restoring every individual log file one at a time? (Which seems to work fine, though is a tremendous pain with any more than a few log backups.) Seems like there ought to be a good one-shot method to restore a bunch of backups to a different server, and I'm just not finding it.Did you try just writing a script to do this? You can use xp_cmdshell to execute a dir which is piped to a file. You can then BCP that file into a table. Then iterate across the table restoring one file after another.|||Hmm, assuming there's no in-built functionality in SSMS to facilitate this, then I suppose that's the most straightforward solution (short of doing ugly tricks with backup info in msdb). This approach would depend on getting the transaction logs in the right order, but with the time stamps in the file names, and time stamps on the files themselves, I don't think this would be an issue. Thanks for the idea fuel. I can't shake the feeling that it can be made ever so slightly more elegant, though. ;-)|||
The problem is that when you specify multiple backup files for one restore operation, it looks to SSMS as if you are trying to give it a stripe set. It then does a standard check to make sure that all of the files you specify belong to the same stripe set and that there are no missing files. This of course fails because the files are not a stripe set, but sequential log backups.
So, probably your best bet for now is to create a script. Very high on my list of "backup/restore tools I'd like to build" is a wizard that understands what backup files are required to get database X restored to time Y, and does the right thing. (we should have all the info needed in the MSDB backup history tables as long as the backups haven't been moved) Unfortunately, I haven't managed to scrape together the needed resources yet.
