Restore database error

SQL Server Administration
1

Hi I want to restore my db into my newdb, but it shows an error telling ;

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

I try to find from another solution like run a script as below

restore database bisbu
from disk='D:\backupdb\bis_backup_201711100007.bak'
with replace

it tells

RESTORE DATABASE bisd
FROM DISK = 'D:\backupdb\bis_backup_201711100007.bak'
WITH RECOVERY;

or another script ;

RESTORE DATABASE bisd
FROM DISK='D:\backupdb\bis_backup_201711100007.bak'
WITH MOVE 'LogicalName of the SourceDatafile' TO 'D:\database\bisd.mdf',
MOVE 'Logicalname of sourcelogfile' TO'D:\database\bisd_log.ldf'

it still tells ;

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

please help

thanks a lot...

2

What does this give you?

RESTORE FILELISTONLY FROM DISK ='D:\backupdb\bis_backup_201711100007.bak’

you should get something like this

LogicalName        PhysicalName                     Type FileGroupName Size MaxSize
------------------ -------------------------------- ---- ------------- ---- -------
MyOldDatabase_data D:\MSSQL\DATA\MyDatabaseName.mdf D    PRIMARY       1234 567890
MyOldDatabase_log  D:\MSSQL\DATA\MyDatabaseName.ldf L    NULL          1234 567890

Based on YOUR results globally change these values (in all the scripts below) :

-- Location of BACKUP FILES which you want to restore
--	x:\Mypath\		                    Path to your backup files
--	x:\Mypath\MyFullBackupfile.BAK		Path/Filename of the FULL BACKUP file
--	x:\Mypath\MyDifferentialBackupfile.BAK	Path/Filename of the DIFFERENTIAL BACKUP file (optional)

-- TARGET Location for the Database (Data and Log - which may be separate drives etc)
--	x:\MySQLDataPath\MyNewDatabaseName.mdf	
--	x:\MySQLLogPath\MyNewDatabaseName.ldf
--	
-- ORIGINAL logical names
--	MyOldDatabase_data
--	MyOldDatabase_log

-- also change these to what would be SENSIBLE names for the NEW logical objects:

--	MyNewDatabase_data
--	MyNewDatabase_log
--
--	MyNewDatabaseName

-- Restore script

USE master -- Must not be "sitting" in the database being restored!
GO

-- If database is being overwritten force all users off:
ALTER DATABASE MyNewDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
	WITH
		REPLACE,	-- Overwrite DB - if one exists
		NORECOVERY,
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'MyOldDatabase_data' TO 'x:\MySQLDataPath\MyNewDatabaseName.mdf', 
	MOVE 'MyOldDatabase_log' TO 'x:\MySQLLogPath\MyNewDatabaseName.ldf'
GO

-- If you have a Differential backup to restore do this:

RESTORE DATABASE MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
	WITH
		NORECOVERY

-- If you have any Transaction Backup files to restore repeat this for each one in turn:

-- Copy & Paste this multiple times if you have many TLog files to restore
RESTORE LOG MyNewDatabaseName FROM DISK = 'x:\Mypath\MyTransactionBackupfile0001.BAK' WITH NORECOVERY, STATS = 10
RESTORE LOG MyNewDatabaseName FROM DISK = 'x:\Mypath\MyTransactionBackupfile0002.BAK' WITH NORECOVERY, STATS = 10
...

-- Set the database ready for use (after all backups have been restored)

RESTORE DATABASE MyNewDatabaseName WITH RECOVERY
GO

-- Rename logical names:
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_data', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_log', NEWNAME = 'MyNewDatabase_log')
GO
3

there're so many script..which on that i can use?

thx

4

I've tried the script one by one but the result still sending message as below

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

thx

5

All of them, as marked the Differential and Log restores are optional

As previously asked:

6

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'D:\backupdb\bis_backup_201711100007.bak’

'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D:\backupdb\bis_backup_201711100007.bak’

'.

7

I copied that from your original posting but didn't notice, until now, that you have the wrong sort of single-quote on the end.

i.e. it should be this:

RESTORE FILELISTONLY FROM DISK ='D:\backupdb\bis_backup_201711100007.bak'
8

It shows the logical name physicalname and what next should i do?

Thx

9

Post the results here.

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

suggests there is something in the backup file other than / in addition to the database that you want to restore.

10
  1. Add REPLACE to the restore command
  2. Make sure those new file names are not already in use by any db or other process:

RESTORE DATABASE bisd
FROM DISK='D:\backupdb\bis_backup_201711100007.bak'
WITH REPLACE, MOVE ‘LogicalName of the SourceDatafile’ TO ‘D:\database\bisd.mdf’,
MOVE ‘Logicalname of sourcelogfile’ TO’D:\database\bisd_log.ldf’

11

That's in the script that I proposed ... so I was assuming that could not be the problem.

Could you get that error message if you have multiple database backups in a single file? (i.e. also need a NAME parameter to RESTORE command)?

if not then it can only be REPLACE, as you describe.

12

it tells errors

Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string 'D:\backupdb\bis_backup_201711100007.bak’
WITH REPLACE, MOVE ‘bis’ TO ‘D:\database\bisd.mdf’,
MOVE ‘bis_log’ TO’D:\database\bisd_log.ldf’

'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'D:\backupdb\bis_backup_201711100007.bak’
WITH REPLACE, MOVE ‘bis’ TO ‘D:\database\bisd.mdf’,
MOVE ‘bis_log’ TO’D:\database\bisd'.

13

You still haven't posted the results from that

14

I've told that before
" It shows the logical name physicalname and what next should i do?"

It Shows this
Logicalname Physicalname type Filegroupname Size
BIS D:\Database\BIS.mdf D PRIMARY 6925844480
BIS_log D:\Database\BIS_log.ldf L NULL 13959168

thx

15

Sorry, I copied and pasted too quickly:

RESTORE DATABASE bisd
FROM DISK='D:\backupdb\bis_backup_201711100007.bak’
WITH REPLACE,
MOVE [BIS] TO ‘D:\database\bisd.mdf’,
MOVE [BIS_log] TO’D:\database\bisd_log.ldf’

16

No you didn't, you gave me an explanation of the output, not the data.

I've substituted your values into the script I gave you before

USE master -- Must not be "sitting" in the database being restored!
GO

-- If database is being overwritten force all users off:
-- *** IGNORE ERROR IN THIS COMMAND IF THE DATABASE "bisd" DOES NOT ALREADY EXIST ***
ALTER DATABASE bisd SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE bisd
	FROM DISK = 'D:\backupdb\bis_backup_201711100007.bak'
	WITH
		REPLACE,	-- Overwrite DB - if one exists
		NORECOVERY,
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'BIS' TO 'D:\database\bisd.mdf', 
	MOVE 'BIS_log' TO 'D:\database\bisd_log.ldf'
GO

/** N/A
-- If you have a Differential backup to restore do this:
RESTORE DATABASE bisd
	FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
	WITH
		NORECOVERY
N/A **/

/**N/A
-- If you have any Transaction Backup files to restore repeat this for each one in turn:

-- Copy & Paste this multiple times if you have many TLog files to restore
RESTORE LOG bisd FROM DISK = 'x:\Mypath\MyTransactionBackupfile0001.BAK' WITH NORECOVERY, STATS = 10
RESTORE LOG bisd FROM DISK = 'x:\Mypath\MyTransactionBackupfile0002.BAK' WITH NORECOVERY, STATS = 10
...
-- Set the database ready for use (after all backups have been restored)
N/A**/

RESTORE DATABASE bisd WITH RECOVERY
GO

-- Rename logical names:
ALTER DATABASE bisd MODIFY FILE (NAME = 'BIS', NEWNAME = 'bisd_data')
GO
ALTER DATABASE bisd MODIFY FILE (NAME = 'BIS_log', NEWNAME = 'bisd_log')
GO

Its basically the same as Scots's script, so his script may have (already) worked for you.

Personally I would still want to change the Logical Name from what the original backup used to something that matches the [changed] name of the new database

There are a couple of other things in my script that may prevent some errors - e.g. if the database already exists, and if it has open connections.

But its basically the same as Scott's script.

17

Dear Scott,
It still show errors..
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string 'D:\backupdb\bis_backup_201711100007.bak’
WITH REPLACE,
MOVE [BIS] TO ‘D:\database\bisd.mdf’,
MOVE [BIS_log] TO’D:\database\bisd_log.ldf’
'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'D:\backupdb\bis_backup_201711100007.bak’
WITH REPLACE,
MOVE [BIS] TO ‘D:\database\bisd.mdf’,
MOVE [BIS_log] TO’D:\database\bis'.

Did I put wrong command?

thx

18

Dear Kristen,
It is show errors message

Msg 3154, Level 16, State 4, Line 2
The backup set holds a backup of a database other than the existing 'bisd' database.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 942, Level 14, State 1, Line 20
Database 'bisd' cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 20
RESTORE DATABASE is terminating abnormally.
Msg 942, Level 14, State 4, Line 3
Database 'bisd' cannot be opened because it is offline.
Msg 942, Level 14, State 4, Line 1
Database 'bisd' cannot be opened because it is offline.

the db become offline ..and returning offline again after the script execute..

thx

19

That means that the "bisd" database, which you are trying to restore, already existed.

I presume that because you are trying to restore OVER that datahase that you do not have a problem with it (now) being offline.

But if you need to put the original back ONLINE you can use this command:

ALTER DATABASE bisd SET ONLINE WITH ROLLBACK IMMEDIATE
GO

I have some further ideas about changes you should try, but I will test those before replying.

Are you 100% certain that the results you posted for

RESTORE FILELISTONLY FROM DISK =‘D:\backupdb\bis_backup_201711100007.bak’

was exactly what was displayed AND that you did not leave anything out? No additional lines displayed?

20

Please post the results from this:

RESTORE HEADERONLY FROM DISK ='D:\backupdb\bis_backup_201711100007.bak'

In particular what is the value for BackupType?