Is it possible to backup multiple databases to a single .bak file and then restore individually?
The answer should be an obvious "yes" - this is one of the main options when setting up a Maintenance Plan. Select multiple databases, then specify a single file. When I do this, I get one .bak file containing a backup set for each database. The individual backup sets for each database are all there and look fine when I look into the contents of the file.
But when I try and restore any database after the first in the set, the restore wizard still only sees the files associated with the first database. On the Files page, I only have two entries (data file, log file) always for the first database. The restore task then throws an error:
System.Data.SqlClient.SqlError: Logical file [first source db] is not part of database [target db]. Use RESTORE FILELISTONLY to list the logical file names.
And when I use
RESTORE FILELISTONLY I only get results for the first database (same as what I see on the Files page in the restore wizard).
I could script out the restore task and set the Logical File Names manually but I don't see any easy way to discover them, if they even exist. It looks like the first backup in the media set establishes the Logical File Names for the entire media set, making any other databases effectively inaccessible.
I'm finding almost zero information online about multiple databases in one backup file; most people want to span one database across multiple files. But in my case, I have a CMS with 4 small related databases that need to be managed as a set. It would be vastly easier to have one consolidated backup file (and one Maintenance Plan, and one agent job) rather than having to do everything 4x.
There's an old SO question question here for SQL Server 2005 describing the exact same problem that says it's a bug that was patched with SP3. But I'm running a fully-patched SQL Server 2014 (12.0.5207.0). I'm seeing this on two separate systems, one running Web Edition, one running Dev Edition.
Going by this old article by Paul Randal, it seems you can simply reference the position number (
WITH FILE = xx) if you're restoring in-place (
WITH REPLACE) but since I am restoring to a different server (with different file paths) I also need to use
WITH MOVE [Logical File Name] TO [new file path]. The same problem would exist if you're restoring a copy with a different name on the same server (like PROD => DEV).