Piecemeal Restore: Backup Set Holds a Backup of a Database Other than the Existing Database (Error)

When attempting to do a piecemeal restore of a database, using the ‘REPLACE’ option to replace the existing database, you may see the following error message:

Msg 3154, Level 16, State 4, Line 63
The backup set holds a backup of a database other than the existing '<database name>' database.
Msg 3013, Level 16, State 1, Line 63
RESTORE DATABASE is terminating abnormally.

This error indicates that you cannot do a piecemeal restore of one database onto an existing copy of a different database. You must drop the existing database before you can do the piecemeal restore. As always, code illustrates the problem quite well.

The first piece of code creates two databases, fgRestoreTest_src and fgRestoreTest_dest. Each database consists of the primary filegroup, plus two custom filegroups, fg1 and fg2. This allows us to do a piecemeal restore of a single filegroup.

This next piece of code is not really necessary, but it creates two tables in the fgRestoreTest_src database, one on each filegroup. These tables allow us to prove which database is which because only the fgRestoreTest_src database contains these tables. The fgRestoreTest_dest database has no tables.

Next, we’ll backup the fgRestoreTest_src database, and attempt to restore it over top of the fgRestoreTest_dest database using piecemeal restore1.

The backup:

Processed 352 pages for database 'fgRestoreTest_src', file 'PRIMARY' on file 1.
Processed 24 pages for database 'fgRestoreTest_src', file 'fg1_file1' on file 1.
Processed 24 pages for database 'fgRestoreTest_src', file 'fg2_file1' on file 1.
Processed 5 pages for database 'fgRestoreTest_src', file 'LOG' on file 1.
BACKUP DATABASE successfully processed 405 pages in 0.038 seconds (83.239 MB/sec).

The restore:

The restore results in the following error message:

Msg 3154, Level 16, State 4, Line 63
The backup set holds a backup of a database other than the existing 'fgRestoreTest_dest' database.
Msg 3013, Level 16, State 1, Line 63
RESTORE DATABASE is terminating abnormally.

If we drop the target database, fgRestoreTest_dest, first, then attempt the restore, it succeeds. So, let’s drop the fgRestoreTest_dest database:

Now, we’ll do the restore, without even attempting to use the REPLACE option:

Processed 352 pages for database 'fgRestoreTest_dest', file 'PRIMARY' on file 1.
Processed 24 pages for database 'fgRestoreTest_dest', file 'fg1_file1' on file 1.
Processed 5 pages for database 'fgRestoreTest_dest', file 'LOG' on file 1.
RESTORE DATABASE ... FILE= successfully processed 381 pages in 0.054 seconds (55.103 MB/sec).

As you can see, the restore succeeded.

I hope this post helps us! If you have any questions, please don’t hesitate to leave a comment below, or send us a tweet.

This post is part of our series on Database Recovery.

Here’s a great painting by Vincent Van Gogh of a wheat field. Because, wheat field.

    Not a piecemeal restore, but lovely anyway!

Not a piecemeal restore, but lovely anyway!


1https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/piecemeal-restores-sql-server

Ads by Google, Paying the Rent: