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

2019-03-13 · by Hannah Vernon · in recovery

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.

USE master;
IF DB_ID(N'fgRestoreTest_src') IS NOT NULL 
BEGIN
    DROP DATABASE fgRestoreTest_src;
END

CREATE DATABASE fgRestoreTest_src
ON (NAME = N'PRIMARY', FILENAME = N'G:\Database\Data\fgRestoreTest_src_primary.mdf')
LOG ON (NAME = N'LOG', FILENAME = N'F:\Database\Data\fgRestoreTest_src.ldf');
GO

ALTER DATABASE fgRestoreTest_src SET RECOVERY FULL;
BACKUP DATABASE fgRestoreTest_src TO DISK = N'NUL:'; 

ALTER DATABASE fgRestoreTest_src ADD FILEGROUP fg1;
ALTER DATABASE fgRestoreTest_src ADD FILE (
    NAME = N'fg1_file1'
    , FILENAME = N'G:\DATABASE\Data\fgRestoreTest_src_fg1_file1.mdf'
) TO FILEGROUP fg1;

ALTER DATABASE fgRestoreTest_src ADD FILEGROUP fg2;
ALTER DATABASE fgRestoreTest_src ADD FILE (
    NAME = N'fg2_file1'
    , FILENAME = N'G:\DATABASE\Data\fgRestoreTest_src_fg2_file1.mdf'
) TO FILEGROUP fg2;
GO

IF DB_ID(N'fgRestoreTest_dest') IS NOT NULL
BEGIN
    DROP DATABASE fgRestoreTest_dest;
END

CREATE DATABASE fgRestoreTest_dest
ON (NAME = N'PRIMARY', FILENAME = N'G:\Database\Data\fgRestoreTest_dest_primary.mdf')
LOG ON (NAME = N'LOG', FILENAME = N'F:\Database\Data\fgRestoreTest_dest.ldf');
GO

ALTER DATABASE fgRestoreTest_dest SET RECOVERY FULL;
BACKUP DATABASE fgRestoreTest_dest TO DISK = N'NUL:'; 

ALTER DATABASE fgRestoreTest_dest ADD FILEGROUP fg1;
ALTER DATABASE fgRestoreTest_dest ADD FILE (
    NAME = N'fg1_file1'
    , FILENAME = N'G:\DATABASE\Data\fgRestoreTest_dest_fg1_file1.mdf'
) TO FILEGROUP fg1;

ALTER DATABASE fgRestoreTest_dest ADD FILEGROUP fg2;
ALTER DATABASE fgRestoreTest_dest ADD FILE (
    NAME = N'fg2_file1'
    , FILENAME = N'G:\DATABASE\Data\fgRestoreTest_dest_fg2_file1.mdf'
) TO FILEGROUP fg2;
GO

USE master;

IF DB_ID(N'fgRestoreTest_src') IS NOT NULL

BEGIN

DROP DATABASE fgRestoreTest_src;

END

CREATE DATABASE fgRestoreTest_src

ON (NAME = N'PRIMARY', FILENAME = N'G:\Database\Data\fgRestoreTest_src_primary.mdf')

LOG ON (NAME = N'LOG', FILENAME = N'F:\Database\Data\fgRestoreTest_src.ldf');

ALTER DATABASE fgRestoreTest_src SET RECOVERY FULL;

BACKUP DATABASE fgRestoreTest_src TO DISK = N'NUL:';

ALTER DATABASE fgRestoreTest_src ADD FILEGROUP fg1;

ALTER DATABASE fgRestoreTest_src ADD FILE (

NAME = N'fg1_file1'

, FILENAME = N'G:\DATABASE\Data\fgRestoreTest_src_fg1_file1.mdf'

) TO FILEGROUP fg1;

ALTER DATABASE fgRestoreTest_src ADD FILEGROUP fg2;

ALTER DATABASE fgRestoreTest_src ADD FILE (

NAME = N'fg2_file1'

, FILENAME = N'G:\DATABASE\Data\fgRestoreTest_src_fg2_file1.mdf'

) TO FILEGROUP fg2;

IF DB_ID(N'fgRestoreTest_dest') IS NOT NULL

BEGIN

DROP DATABASE fgRestoreTest_dest;

END

CREATE DATABASE fgRestoreTest_dest

ON (NAME = N'PRIMARY', FILENAME = N'G:\Database\Data\fgRestoreTest_dest_primary.mdf')

LOG ON (NAME = N'LOG', FILENAME = N'F:\Database\Data\fgRestoreTest_dest.ldf');

ALTER DATABASE fgRestoreTest_dest SET RECOVERY FULL;

BACKUP DATABASE fgRestoreTest_dest TO DISK = N'NUL:';

ALTER DATABASE fgRestoreTest_dest ADD FILEGROUP fg1;

ALTER DATABASE fgRestoreTest_dest ADD FILE (

NAME = N'fg1_file1'

, FILENAME = N'G:\DATABASE\Data\fgRestoreTest_dest_fg1_file1.mdf'

) TO FILEGROUP fg1;

ALTER DATABASE fgRestoreTest_dest ADD FILEGROUP fg2;

ALTER DATABASE fgRestoreTest_dest ADD FILE (

NAME = N'fg2_file1'

, FILENAME = N'G:\DATABASE\Data\fgRestoreTest_dest_fg2_file1.mdf'

) TO FILEGROUP fg2;

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.

USE fgRestoreTest_src;
CREATE TABLE dbo.RestoreTestTable_fg1
(
    RestoreTestTable_ID int NOT NULL
        CONSTRAINT RestoreTestTable_fg1_pk
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON fg1;
INSERT INTO dbo.RestoreTestTable_fg1 DEFAULT VALUES;
CREATE TABLE dbo.RestoreTestTable_fg2
(
    RestoreTestTable_ID int NOT NULL
        CONSTRAINT RestoreTestTable_fg2_pk
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON fg2;
INSERT INTO dbo.RestoreTestTable_fg2 DEFAULT VALUES;
GO

USE fgRestoreTest_src;

CREATE TABLE dbo.RestoreTestTable_fg1

(

RestoreTestTable_ID int NOT NULL

CONSTRAINT RestoreTestTable_fg1_pk

PRIMARY KEY CLUSTERED

IDENTITY(1,1)

) ON fg1;

INSERT INTO dbo.RestoreTestTable_fg1 DEFAULT VALUES;

CREATE TABLE dbo.RestoreTestTable_fg2

(

RestoreTestTable_ID int NOT NULL

CONSTRAINT RestoreTestTable_fg2_pk

PRIMARY KEY CLUSTERED

IDENTITY(1,1)

) ON fg2;

INSERT INTO dbo.RestoreTestTable_fg2 DEFAULT VALUES;

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

The backup:

USE master;
GO
BACKUP DATABASE fgRestoreTest_src TO DISK = 'F:\Database\Backups\fgRestoreTest_src.bak' WITH INIT;
GO

USE master;

BACKUP DATABASE fgRestoreTest_src TO DISK = 'F:\Database\Backups\fgRestoreTest_src.bak' WITH INIT;

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:

RESTORE DATABASE fgRestoreTest_dest 
    FILEGROUP = 'fg1'
    , FILEGROUP = 'PRIMARY'
FROM DISK = 'F:\Database\Backups\fgRestoreTest_src.bak'
WITH MOVE 'fg1_file1' TO 'G:\Database\Data\fgRestoreTest_dest_fg1_file1.mdf'
    , MOVE 'PRIMARY' TO 'G:\Database\Data\fgRestoreTest_dest_primary.mdf'
    , MOVE 'LOG' TO 'F:\Database\Data\fgRestoreTest_dest.ldf'
    , RECOVERY
    , REPLACE;
GO

RESTORE DATABASE fgRestoreTest_dest

FILEGROUP = 'fg1'

, FILEGROUP = 'PRIMARY'

FROM DISK = 'F:\Database\Backups\fgRestoreTest_src.bak'

WITH MOVE 'fg1_file1' TO 'G:\Database\Data\fgRestoreTest_dest_fg1_file1.mdf'

, MOVE 'PRIMARY' TO 'G:\Database\Data\fgRestoreTest_dest_primary.mdf'

, MOVE 'LOG' TO 'F:\Database\Data\fgRestoreTest_dest.ldf'

, RECOVERY

, REPLACE;

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:

USE master;
DROP DATABASE fgRestoreTest_dest;
GO

USE master;

DROP DATABASE fgRestoreTest_dest;

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

RESTORE DATABASE fgRestoreTest_dest 
    FILEGROUP = 'fg1'
    , FILEGROUP = 'PRIMARY'
FROM DISK = 'F:\Database\Backups\fgRestoreTest_src.bak'
WITH MOVE 'fg1_file1' TO 'G:\Database\Data\fgRestoreTest_dest_fg1_file1.mdf'
    , MOVE 'PRIMARY' TO 'G:\Database\Data\fgRestoreTest_dest_primary.mdf'
    , MOVE 'LOG' TO 'F:\Database\Data\fgRestoreTest_dest.ldf'
    , RECOVERY;
GO

RESTORE DATABASE fgRestoreTest_dest

FILEGROUP = 'fg1'

, FILEGROUP = 'PRIMARY'

FROM DISK = 'F:\Database\Backups\fgRestoreTest_src.bak'

WITH MOVE 'fg1_file1' TO 'G:\Database\Data\fgRestoreTest_dest_fg1_file1.mdf'

, MOVE 'PRIMARY' TO 'G:\Database\Data\fgRestoreTest_dest_primary.mdf'

, MOVE 'LOG' TO 'F:\Database\Data\fgRestoreTest_dest.ldf'

, RECOVERY;

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!

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

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

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta