4

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

  • 2
    Dumb question: what happens when you run RESTORE HEADERONLY and point it at your backup file? – Erik Darling Dec 17 '17 at 19:04
  • Have you looked into this powershell code ? – Kin Shah Dec 17 '17 at 20:12
  • @sp_BlitzErik RESTORE HEADERONLY shows all four database backups. So they're there. But when I run RESTORE FILELISTONLY I only see the files for the first backup. – Neil Laslett Dec 20 '17 at 4:23
2

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.

enter image description here

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.

enter image description here

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
  • Yes, but this is dependent on one thing: being able to access server 1 to look up the Logical File Names (ITDBA & ITDBA_log). If you don't have access to the original server, I cannot find any way to get these values from the backup file itself. – Neil Laslett Dec 20 '17 at 4:21
  • This seems to be a bug in either the restore wizard or in RESTORE FILELISTONLY itself. If I start the restore in the wizard, script out the restore, and then manually change the Logical File Names in the MOVE statement (as you indicate), the restore runs fine. But of course I can only do that because I still have access to the source server and can go look up the LFNs there. Not a good solution for disaster recovery scenarios. The tooling seems unable to detect and use the correct files associated with the selected backup, or even to show me the list of files. – Neil Laslett Dec 20 '17 at 4:26
  • On your first comment (very good question) for logical file name use RESTORE FILELISTONLY command. Details [here]9https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-filelistonly-transact-sql). I do no use GUI so I am not aware of the issue. – SqlWorldWide Dec 20 '17 at 12:46
  • Sorry, just getting back to this now. Yes, RESTORE FILELISTONLY is broken in that it only shows the file names for the first backup set. I'm doing this with both GUI and script. If I know the Logical File Names I can hand-code the RESTORE command and it works, no problem. None of this is a GUI problem. If you have multiple servers in one media set and don't know the LFNs, you cannot get them from the backup file. – Neil Laslett Jan 19 '18 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.