MSG 3154 The backup set holds a backup of a database other than the existing database.

  • I'm creating a module for our testing team to copy database in same server without using any wizard or providing any access to the server 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.

    I cannot use WITH REPLACE since I need backup live db and copy to the test db,

    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.

  • You need to use WITH REPLACE on the restore. Since you're overwriting the DB that's there, I don't understand why you say you can't use that option.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (7/14/2014)


    You need to use WITH REPLACE on the restore. Since you're overwriting the DB that's there, I don't understand why you say you can't use that option.

    This is the reason why I cannot use WITH REPLACE

    Msg 1834, Level 16, State 1, Line 13

    The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB.mdf' cannot be overwritten. It is being used by database 'SomeDB'.

    Msg 3156, Level 16, State 4, Line 13

    File 'SomeDB' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB.mdf'. Use WITH MOVE to identify a valid location for the file.

    Msg 1834, Level 16, State 1, Line 13

    The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB_log.ldf' cannot be overwritten. It is being used by database 'SomeDB'.

    Msg 3156, Level 16, State 4, Line 13

    File 'SomeDB_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB_log.ldf'. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Line 13

    Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Line 13

    RESTORE DATABASE is terminating abnormally.

  • Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.

  • Then you need to use WITH MOVE.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (7/14/2014)


    Then you need to use WITH MOVE.

    Already doing it, please go through my post carefully

    Regards

  • Hema kumar-337490 (7/14/2014)


    Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.

    That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?

  • sirvikasrana (7/14/2014)


    Grant Fritchey (7/14/2014)


    Then you need to use WITH MOVE.

    Already doing it, please go through my post carefully

    Regards

    You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log

    You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY

    Without the variables, this is, afaict, the statement you need to run

    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'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sirvikasrana (7/14/2014)


    Hema kumar-337490 (7/14/2014)


    Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.

    That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?

    The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • GilaMonster (7/14/2014)


    sirvikasrana (7/14/2014)


    Grant Fritchey (7/14/2014)


    Then you need to use WITH MOVE.

    Already doing it, please go through my post carefully

    Regards

    You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log

    You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY

    Without the variables, this is, afaict, the statement you need to run

    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'

    Please check my code above I've specified the same file names which you have added in your code.. my code and your code is not working..

    I failed but can you please create two databases having name SomeDB & SomeDBTest and try your and my code, let me know how well it worked...

  • Grant Fritchey (7/14/2014)


    sirvikasrana (7/14/2014)


    Hema kumar-337490 (7/14/2014)


    Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.

    That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?

    The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.

    WITH REPLACE is trying to replace 'SomeDB.mdf' (which I don't want) but I need to replace or create 'SomeDBTest.mdf'. How can I do this?

  • sirvikasrana (7/14/2014)


    GilaMonster (7/14/2014)


    sirvikasrana (7/14/2014)


    Grant Fritchey (7/14/2014)


    Then you need to use WITH MOVE.

    Already doing it, please go through my post carefully

    Regards

    You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log

    You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY

    Without the variables, this is, afaict, the statement you need to run

    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'

    Please check my code above I've specified the same file names which you have added in your code..

    No they're not. You specified Test in the logical names, names which according to the errors you posted don't exist. If you're obsfucating the code, please don't, it makes things way harder to figure out.

    sirvikasrana (7/14/2014)


    RESTORE DATABASE SomeDBTest FROM DISK = @PathFile

    WITH NORECOVERY,

    MOVE 'SomeDBTest' TO @PathDB,

    MOVE 'SomeDBTest_Log' TO @PathLog

    I have done this a number of time, the code I posted is what I would use. If it's not working, post the exact error messages (no obsfucation)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sirvikasrana (7/14/2014)


    Grant Fritchey (7/14/2014)


    sirvikasrana (7/14/2014)


    Hema kumar-337490 (7/14/2014)


    Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.

    That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?

    The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.

    WITH REPLACE is trying to replace 'SomeDB.mdf' (which I don't want) but I need to replace or create 'SomeDBTest.mdf'. How can I do this?

    Using a correct MOVE clause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (7/14/2014)


    sirvikasrana (7/14/2014)


    GilaMonster (7/14/2014)


    sirvikasrana (7/14/2014)


    Grant Fritchey (7/14/2014)


    Then you need to use WITH MOVE.

    Already doing it, please go through my post carefully

    Regards

    You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log

    You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY

    Without the variables, this is, afaict, the statement you need to run

    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'

    Please check my code above I've specified the same file names which you have added in your code..

    No they're not. You specified Test in the logical names, names which according to the errors you posted don't exist. If you're obsfucating the code, please don't, it makes things way harder to figure out.

    sirvikasrana (7/14/2014)


    RESTORE DATABASE SomeDBTest FROM DISK = @PathFile

    WITH NORECOVERY,

    MOVE 'SomeDBTest' TO @PathDB,

    MOVE 'SomeDBTest_Log' TO @PathLog

    I have done this a number of time, the code I posted is what I would use. If it's not working, post the exact error messages (no obsfucation)

    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.

    Your code worked on SomeDB files but I my requirement is to make it work on SomeDBTest not on SomeDB.

    Here is my Code:

    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.

  • GilaMonster (7/14/2014)


    sirvikasrana (7/14/2014)


    Grant Fritchey (7/14/2014)


    sirvikasrana (7/14/2014)


    Hema kumar-337490 (7/14/2014)


    Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.

    That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?

    The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.

    WITH REPLACE is trying to replace 'SomeDB.mdf' (which I don't want) but I need to replace or create 'SomeDBTest.mdf'. How can I do this?

    Using a correct MOVE clause.

    Is there any possible way to copy SomeDB to SomeDBTest ?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply