Multiple databases, one backup file

Question

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

What gives?

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.

Additional information

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

Answer 1

It is well explained in Paul Randal's article as you mentioned in the question.

Let me walk you through an example.

Server 1 I am taking backup of 3 databases into one file.

BACKUP DATABASE [itdba] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT,NAME = N'itdba_backup'
GO
BACKUP DATABASE [itdbatest] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'itdbatest_backup'
GO
BACKUP DATABASE [itdbatest2] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'itdbatest2_backup'
GO

Now if a copy the backup to Server 2 as you mentioned restoring itdba database is like any other restore but you are having trouble restoring other 2 databases.

If you run below tsql

RESTORE HEADERONLY FROM DISK = N'D:\multipleDatabaseBackup.bak'

you will see the position of backups corresponding to databases. There are many other columns but I am showing few only.

Take a note of itdbatest database with position 2. I can restore this to another server which does not have the same drive/folder structure as Server 1 using MOVE.

itdbatest database file name and location in server 1.

I copied the backup to Server 2 before restoring.

RESTORE DATABASE itdbatest FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\multipleDatabaseBackup.bak'  
WITH REPLACE, FILE = 2,
MOVE 'ITDBATest' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ITDBATest.mdf',
MOVE 'ITDBATest_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ITDBATest_log.ldf'
GO