The backup set holds a backup of a database other than the existing database ошибка

Remove From My Forums

Question
Hi All,

Really need your assistance and help, I have problem and issue on Data Warehouse environment, where when doing a restoration received error as below

Error :-

The backup set holds a backup of a database other than the existing ‘XXX’ database

Restore database is terminating abnormal .

Was tried few solution such as WITH MORE, detach, create back and run restoration database still nowt work.

Need your suggestion and solution for this issue .

Appreciate your kind assistance.

Environmnt : SQL Server 2008 Enterprise .

Thank you

Amy Badri

Answers

If you wish to overwrite the existing database, you meed to specify the REPLACE option, for instance:

RESTORE DATABASE thatdb FROM DISK = ‘c:backupofsomeotherdb.bak’ WITH REPLACE

But think twice before you actually do it that way!
You may also have to specify the MOVE option if the files for the two database do not match.
- Marked as answer by
  
  Tuesday, March 29, 2016 3:02 AM
These error messages suggest you have many more files returned by the RESTORE FILELISTONLY command that you have specified in the MOVE clauses. You need to specify a MOVE clause for each file listed, or SQL Server will attempt to restore to the original
file location.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Proposed as answer by
  Ice Fan
  Tuesday, March 29, 2016 2:07 AM
- Marked as answer by
  Amy Badri
  Tuesday, March 29, 2016 3:01 AM
- Edited by
  Dan GuzmanMVP
  Tuesday, March 29, 2016 12:11 PM
  grammar
RESTORE DATABASE XXXXX
FROM DISK = ‘G:BakMXXX.bak’
WITH REPLACE,
MOVE ‘AFG_EXTRACT_GLOS_CB_DAILY_log’ TO ‘I:MSSQLDataXXXX.mdf’,
MOVE ‘AFG_EXTRACT_GLOS_CB_DAILY_log’ TO ‘E:MSSQLLogXXXXX.ldf’;

It seems you have specified the same logical file name twice. I suspect the first name should be the data file rather than log. To get the logical file names from the backup file, use RESTORE FILELISTONLY:
```
RESTORE FILELISTONLY
FROM DISK = 'G:BakMXXX.bak';
```
If the first RESTORE command was for the same database and it succeeded, you don’t need to bother with the second command anyway.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Marked as answer by
  Amy Badri
  Tuesday, March 29, 2016 3:01 AM

Hi all,

I am facing an interesting problem.

I have an application, that creates a database for it’s own. Before, it used to create the database just with

CREATE DATABASE database_name

statement, and it used to have only one filegroup PRIMARY (two files, mdf and ldf). (MS SQL SERVER 2008 R2)

Recently, we decided to add a new filegroup, to store photos, and wanted our application to be able to backup/restore the database with/without PHOTOS filegroup.

So now, when I create a database through the application, it creates a database, and adds a filegroup and assigns a file to it.

When it wants to restore a backup file from previous versions (with just one PRIMARY filegroup), it deletes the PHOTOS filegroup, and restores the old backup file, and once the restoration is done, adds this filegroup back. This works perfectly, well done.

The problem occurs, when I try to restore the backup, which is made by the latest version.

Lets say I create a database test1, and make a backup of it: test1.bak. And I create another database test2, and make a backup of it: test2.bak.
If I restore test2 from test2.bak, it restores, but if I try to restore from test1.bak, it gives this error.

RESTORE FILELISTONLY FROM DISK='C:test1.bak'

and

RESTORE FILELISTONLY FROM DISK='C:test2.bak'

give the same result except UniqueId fields.

The following is the code that is uses for restoration:

DECLARE 

@DestDataBaseName varchar(255),

@BackupFile varchar(255)



SET @DestDataBaseName = 'test2'

SET @BackupFile = 'C:test.bak'



RESTORE DATABASE @DestDataBaseName FROM DISK = @BackupFile

WITH REPLACE,PARTIAL, 

MOVE 'test' TO 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDatatest2.mdf', 

MOVE 'PHOTOS' TO 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDatatest2_PHOTOS.mdf', 

MOVE 'test_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDatatest2.ldf'

Tried to restore through GUI, with «REPLACE EXISTING» option checked, doesn’t work as well.
It gives the same error. That «the backup set holds a backup of a database other than the existing database».

Have been working on that for about a week, getting crazy.Any ideas what the hell the problem might be?

Thanks in advance.

Источник

When restoring a database from an SQL backup .bak, one usually creates a database and then selects the restore function. As soon as you try to restore the database you get the error saying “The backup set holds a backup of a database other than the existing“. This is because it fails to read the files from the restore and matching them to the newly created files.

The error says:

Restore failed for Server ‘SQLSRV01’. (Microsoft.SqlServer.SmoExtended)
Additional Information
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The backup set hold a backup of a database other an the existing ‘test_restore’ database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)

To solve this one should do the following:

– Don’t create an empty database and restore the .bak file on to it.
– Use ‘Restore Database’ option accessible by right clicking the “Databases” branch of the SQL Server Management Studio and provide the database name while providing the source to restore.

This should allow you to restore the database with no error and fix the error 3154.

(12623)

Technical help and scripts for everyday issues of a sysadmin day

Источник

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:DatabaseDatafgRestoreTest_src_primary.mdf’)

LOG ON (NAME = N‘LOG’, FILENAME = N‘F:DatabaseDatafgRestoreTest_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:DATABASEDatafgRestoreTest_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:DATABASEDatafgRestoreTest_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:DatabaseDatafgRestoreTest_dest_primary.mdf’)

LOG ON (NAME = N‘LOG’, FILENAME = N‘F:DatabaseDatafgRestoreTest_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:DATABASEDatafgRestoreTest_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:DATABASEDatafgRestoreTest_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;

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;

BACKUP DATABASE fgRestoreTest_src TO DISK = ‘F:DatabaseBackupsfgRestoreTest_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:DatabaseBackupsfgRestoreTest_src.bak’

WITH MOVE ‘fg1_file1’ TO ‘G:DatabaseDatafgRestoreTest_dest_fg1_file1.mdf’

, MOVE ‘PRIMARY’ TO ‘G:DatabaseDatafgRestoreTest_dest_primary.mdf’

, MOVE ‘LOG’ TO ‘F:DatabaseDatafgRestoreTest_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;

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:DatabaseBackupsfgRestoreTest_src.bak’

WITH MOVE ‘fg1_file1’ TO ‘G:DatabaseDatafgRestoreTest_dest_fg1_file1.mdf’

, MOVE ‘PRIMARY’ TO ‘G:DatabaseDatafgRestoreTest_dest_primary.mdf’

, MOVE ‘LOG’ TO ‘F:DatabaseDatafgRestoreTest_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

If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.

Источник

Home
> CRM, SQL > SqlError: The backup set holds a backup of a database other than the existing database

I got below error, when I was trying restore a database from Azure Blob Storage to my local SQL server using ‘SQL Server Management Studio (SSMS)’.

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘{DB_Name}’ database. (Microsoft.SqlServer.SmoExtended)

Reason:

I created a new Database (DB) first and tried to restore the DB from Azure blob to this new DB.

Fix:

Below approach solved the issue

Don’t create a new Database before hand.
Right click ‘Databases’ and select ‘Restore Database’
Pick your ‘Source’ Database
In the ‘Destination’ section, provide a new Database name as highlighted below

DB_Restore1