SQL Server Performance

Backup Restore

Discussion in 'SQL Server 2005 General DBA Questions' started by gopal, Sep 28, 2004.

  1. gopal Member

    I am trying to restore backup from SQL Server 2000 to SQL Server 2005. I am successful in restoring the backup with one data file and one log file.

    I had trouble restoring the backups with multiple data files. I have done exactly the same but it gives me the following error message:

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


    I tried restoring the database with "Move" option.

    I would appreciate, if anyone can help me on this.

    Thanks,
    Gopal
  2. Luis Martin Moderator

    According error, I suppose you are restoring one database over TEST database allready in SQL.
    That's what you want to do?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. gopal Member

    That's correct. I am doing the exactly the same.

    I have the used the following command for restore

    restore database TEST
    from disk = '\server_nameX$TEST.BAK'
    with norecovery,
    move ....

    I just added 'replace' command after norecovery and it started working.

    Thanks for your time,
    Gopal

  4. dgao New Member

    Gopal,
    I'm getting the same problem and same error message. Could you please send me the complete restore statement with REPLACE command?

    Thanks in advance.

    Dennis
  5. satya Moderator

    Have you looked at the contents of that backup file using RESTORE FILELISTONLY, to resolve this run

    restore database MyDatabase from disk = \pathdbbackup-file' with replace

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. dgao New Member

    It worked. Thanks, Sayta.

    Dennis
  7. dgao New Member

    After I run restore command, it says restore successfully. However, it still says "Restoring" in the Management Studio and I cannot access the database. What should I do now? Stop and Start the server?

    Dennis
  8. satya.sqldba New Member

    Did u try refreshing it?

    Do not restart server for such small things, see if the database is accessible i.e. if you are able to run any queries or open a different Management Studio

    Satya
  9. satya.sqldba New Member

    Am I seeing the truth or is it a mistake? I see that there are 3500 views for this thread?
  10. MohammedU New Member

    Run...
    RESTORE DATABASE <DB NAME> WITH RECOVERY

    use DATABASEPROPERTYEX function get the status of the database...
    See BOL for syntax...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  11. satya.sqldba New Member

    There you go Gopal

    I didnt look at your syntax. You have restored it with norecovery option, use recovery to have the database accessible after your restore.

    Use norecovey only when you plan somehting like pending log restores and stuff.

    Satya
  12. dgao New Member

    Yes, everything is fine after I run RESTORE DATABASE <DB NAME> WITH RECOVERY.

    Thanks, folks.

    Dennis
  13. MohammedU New Member

    Read SQL Server 2005 Books Online topic "RESTORE (Transact-SQL)" to understand more about this...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page