Sql-server – Multiple databases, one backup file

backupsql serversql server 2014

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

Best Answer

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

Related Solutions

Sql-server – How to restore multiple backups

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Sql-server – How to backup and restore a database to a different location and name via command line only

The WITH MOVE 'logical_file_name_in_backup' refers to the internal name of that file, not the database name.

Run this to see what I mean. You're looking at the name column

SELECT * FROM sys.database_files

Edit:

Need WITH REPLACE now... see first link above and look for

The database name is different from the database name recorded in the backup set.

Best Answer

Related Solutions

Sql-server – How to restore multiple backups

Sql-server – How to backup and restore a database to a different location and name via command line only

Related Question