none
Database Restore, Error 3154 and

    Question

  • Hi all,

    I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

    I have tried giving the new database the same name as the original and a different name to the original database

    (Below is the scripted T-SQL that I get from the DB Admin tool

    RESTORE DATABASE [Fers_Production]
    FILE = N'FERS_Production_dat',
    FILE = N'FERS_Production_log'
    FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
    WITH FILE = 1,
    NOUNLOAD
    ,
    REPLACE,
    STATS
    = 10
    GO

    When I run this I get the following error.

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

    Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

    Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

    Any help is much appriciated, many thanks

    Derek 

    Monday, July 31, 2006 1:36 AM

Answers

  • The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file. 

     

     

    Monday, July 31, 2006 5:24 AM

All replies

  • Hi all,

    Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Attach database failed for Server 'DATABASESERVER'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

    A system assertion check has failed. Check the SQL Server error log for details
    Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
    Location:  IndexDataSet.cpp:12001
    Expression:  retCode == INSERT_SUCCESSFUL
    SPID:   53
    Process ID:  1092 (Microsoft SQL Server, Error: 3624)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

    ------------------------------
    Unfortunately the link that MS provide says there is no aditional info.

    Thanks

    Derek

     

    Monday, July 31, 2006 2:18 AM
  • The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file. 

     

     

    Monday, July 31, 2006 5:24 AM
  • Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

    I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

    Thanks for your help.

    Derek

    Tuesday, August 1, 2006 12:14 AM
  • I have merged these threads, as the error seems to be the same in both cases.
    Wednesday, August 2, 2006 6:50 PM
    Moderator
  • Derek,

    Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

    use fers_production
    go
    Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
    go
    Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
    go

    Then restart SQL Server after you have done this.

     

     

    Thursday, August 3, 2006 6:53 AM
  • Thanks again Andy,

    I have been caught up with other things hence the delay in my saying thanks.

    I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

    Cheers

    Derek

    Wednesday, August 9, 2006 11:51 PM
  •  

    Backup File = mydatabase.bak

    1. Run Microsoft SQL Server Management Studio application.

    2. If mydatabase is in Databases : delete mydatabase.

    3. Right Click to Databases and select Restore Database ....

    4.    Destination for restore -> To database: mydatabase

           Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

           Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

    (for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension 

    ex.:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

    5. Press OK button.

    Have fun!

       

     

    Monday, August 14, 2006 11:04 PM
  • Hi,

    I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
    Here is the description of my problem,

    I am trying to restore a SQL 2000 database to an existing  SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

    System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

    I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened?? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
    Any comments?

    Regards,

     

    Jacek

    Thursday, November 2, 2006 6:50 AM
  • Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

     

    I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

    RESTORE DATABASE [ELF2] FROM  DISK = N'Z:\ELF2' WITH  FILE = 1,  REPLACE,
     STATS = 10
    GO

    Friday, January 5, 2007 7:09 PM
  • Barry many thanks for this!

    I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

    I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

    I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

    --live copy
    use tempdb

    create database Platinum
    go

    alter database Platinum set single_user with rollback immediate
    go

    alter database Platinum set multi_user with rollback immediate
    go

    --if it has a name it will restore over the system decided path
    RESTORE DATABASE [Platinum] FROM  DISK = N'C:\install\Platinum.bak' WITH  FILE = 1,  REPLACE,
    STATS = 10
    GO

    ALTER database Platinum set recovery SIMPLE
    GO

    --Training Copy exactly the same copy
    use tempdb

    create database PlatinumTraining
    go

    alter database PlatinumTraining set single_user with rollback immediate
    go

    alter database PlatinumTraining set multi_user with rollback immediate
    go

    RESTORE DATABASE [PlatinumTraining] FROM  DISK = N'C:\install\Platinum.bak' WITH  FILE = 1,  REPLACE,
    STATS = 10
    GO

    ALTER database PlatinumTraining set recovery SIMPLE
    GO

    Tuesday, March 6, 2007 2:13 PM
  • "The backup set holds a backup of a database other than the existing 'Fers_Production' database."

     

    Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

    Wednesday, May 9, 2007 2:27 AM
  • Barry thanks so much.  I Modified your code a bit because I had a backup I desparately needed on tape. 

    RESTORE DATABASE [My_DB_Here] FROM  TAPE = N'\\.\Tape0' WITH  FILE = 1,  REPLACE,
     STATS = 10
    GO

     

    The good thing here was that the first run displayed errors which pointed out the path of the original database.  Once I corrected that it started the restore just fine.  I had struggled with the SQL GUI and receied no helpful info in the error messages.  Searching on the 3154 message brought me here.

     

    Thursday, January 24, 2008 9:15 PM
  • i wasted a lot of time on this,

    I solved by using below 2 things:

    1. created a new db with a different name, instead of silverstop, silverstop_store

    2. when restoring instead of default files it gives force and change from silverstop.mdf to silverstop_store.mdf and xxx_log.ldf
    Tuesday, February 5, 2008 4:24 AM
  • There is a solution posted here. 

    Hope this help you


    Shailesh Patel Application Programmer/Analyst
    • Edited by Shail Patel Monday, February 15, 2010 1:09 AM changed blog url
    Saturday, June 6, 2009 11:27 PM
  • Thx Andy_T .. i too tried its working for me also..!!!

    Cheers
    Thursday, July 2, 2009 12:28 PM
  • I had the same issue couple of mints before and tried all the recommanded solutions. I observed my agent was stopped. I restarted and it DB backup restored successfully.
    Manu ------- Please click the Mark as Answer if my post solves your issue.
    Tuesday, July 20, 2010 12:52 PM