Msg 3154 The backup set holds a backup of a databa

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Msg 3154 The backup set holds a backup of a databa

Author

Topic

ivikasrana
Starting Member

3 Posts

Posted - 2014-07-14 : 09:22:15

I'm creating a module for our testing team to copy/database without having access to the complete server. I do not want them to access the sql server or any wizard but to give them a webpage where they can copy live database to a testing database on the same server without bugging up the development team.
We are using SQL 2012 Devloper
Any help would be appreciated...
-----------------------

USE MASTER
GO

DECLARE @Folder VARCHAR(MAX) = 'E:\MSSQLBackup\'
DECLARE @PathFile VARCHAR(MAX) = @Folder + 'SomeDB.bak'
DECLARE @PathDB VARCHAR(MAX) = @Folder + 'SomeDBTest.mdf'
DECLARE @PathLog VARCHAR(MAX) = @Folder + 'SomeDBTest_Log.ldf'

BACKUP DATABASE SomeDB TO DISK = @PathFile WITH FORMAT, COMPRESSION

Alter Database SomeDBTest SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE FILELISTONLY FROM DISK = @PathFile --found this online but its useless

RESTORE DATABASE SomeDBTest FROM DISK = @PathFile
WITH NORECOVERY,
MOVE 'SomeDBTest' TO @PathDB,
MOVE 'SomeDBTest_Log' TO @PathLog

ALTER DATABASE SomeDBTest SET MULTI_USER;

------------------------

Processed 257943 pages for database 'SomeDB', file 'SomeDB' on file 1.
Processed 2 pages for database 'SomeDB', file 'SomeDB_log' on file 1.
BACKUP DATABASE successfully processed 257945 pages in 1.255 seconds (1716.994 MB/sec).

(2 row(s) affected)
Msg 3154, Level 16, State 4, Line 13
The backup set holds a backup of a database other than the existing 'SomeDBTest' database.
Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-14 : 11:27:58

I made some minor changes and tested. I usually kill connections when I restore a database, so I am including a kill spid proc which you can create in master.

CREATE PROC [Dba].[KillConnectionsForDatabase]
@Dbname varchar(50)
AS
BEGIN

DECLARE @SPIDs TABLE(killSpid nvarchar(20))
DECLARE @Rows int

INSERT INTO @SPIDs
SELECT DISTINCT 'KILL ' + CAST(SPID as varchar(5)) FROM Master..sysprocesses where dbid = db_id(@Dbname)

DECLARE SpidKill CURSOR
READ_ONLY
FOR SELECT killSpid FROM @SPIDs

DECLARE @killSpid nvarchar(20)
OPEN SpidKill

FETCH NEXT FROM SpidKill INTO @killSpid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
EXECUTE Sp_executesql @killSpid
END
FETCH NEXT FROM SpidKill INTO @killSpid
END

CLOSE SpidKill
DEALLOCATE SpidKill

END

DECLARE @Folder VARCHAR(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVERLOCAL\MSSQL\'
DECLARE @BackupDb varchar(50) = 'DEV'
DECLARE @LogicalBackupDbDataName varchar(50) = 'DEV'
DECLARE @LogicalBackupDbLofName varchar(50) = 'DEV_Log'
DECLARE @RestoreDB varchar(50) = 'SomeDB'
DECLARE @PathFile VARCHAR(MAX) = @Folder + 'Backup\' + 'DEV.bak'
DECLARE @PathDB VARCHAR(MAX) = @Folder + 'Data\' + 'SomeDBTest.mdf'
DECLARE @PathLog VARCHAR(MAX) = @Folder + 'Data\' + 'SomeDBTest_Log.ldf'

BACKUP DATABASE Dev TO DISK = @PathFile WITH INIT,COMPRESSION

EXEC [Dba].[KillConnectionsForDatabase] 'SomeDB'

RESTORE DATABASE @RestoreDB FROM DISK = @PathFile
WITH
MOVE @LogicalBackupDbDataName TO @PathDB,
MOVE @LogicalBackupDbLofName TO @PathLog
,REPLACE,RECOVERY

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 12:11:19

You just need the REPLACE option added to your WITH.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ivikasrana
Starting Member

3 Posts

Posted - 2014-07-14 : 13:24:13

quote:
Originally posted by tkizer

You just need the REPLACE option added to your WITH.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

See, let me clear here.. I want to replace SomeDBTest files not SomeDB files, I am taking backup of SombDB and want to restore it in SomeDBTest, why would I take backup of SomeDB and restore it back since I wanted to restore it into a new test database.

Is there any possible way to copy SomeDB to SomeDBTest ?

Here is my Code after using WITH REPLACE:

RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDBTest' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDBTest_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
------------------------------

Msg 3234, Level 16, State 2, Line 2
Logical file 'SomeDBTest' is not part of database 'SomeDBTest'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 13:28:22

Your MOVE is incorrect. The logical files need to reflect SomeDB and not SomeDBTest. You can run RESTORE FILELISTONLY to verify which logical files to use or check the source database's properties.

RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ivikasrana
Starting Member

3 Posts

Posted - 2014-07-14 : 13:37:23

quote:
Originally posted by tkizer

Your MOVE is incorrect. The logical files need to reflect SomeDB and not SomeDBTest. You can run RESTORE FILELISTONLY to verify which logical files to use or check the source database's properties.

RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

thanks, its working

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 13:38:56

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources