Post reply

Tearing my hair out with SQL 2000 restore to new DB name

  • June 19, 2008 at 3:15 am

    #187310

    I have been trying to sort this for days !!

    Using SQL Server Enterprise Server 2000 restore function i am trying to create a new database using backup from remote test server for a database name that all ready exists on a production server. The database backup does not exist in history of back ups and has a slightly different schema.

    I have selected database root and in tasks selected restore DB changing name of database 'dbname' (production DB) to restore to new 'dbname1'. As the back up to restore is not available under back up history list i have to select a new device and browse for back up device from the directory it is stored in.

    When i then try to restore it conflicts with production DB name 'name' and abends with error due to production dbname conflict.

    Can someone provide me with the correct procedure please

    Thanks

  • June 19, 2008 at 4:48 am

    #830982

    If you have difficulty in restoring the database using UI, use RESTORE command.

    For example:

    restore database NewDatabaseName

    from disk = 'E:\Training_full.bak'

    with move 'Training_Data' to 'E:\Training2_Data.mdf',

    move 'Training_Log' to 'E:\Training2_Log.ldf'

  • June 19, 2008 at 8:55 am

    #831164

    I also have a whole set of tutorials on this on MidnightDBA.

    Under the admin section.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 19, 2008 at 9:39 am

    #831198

    Do with the same, as will do in Logshipping.

    Manoj

    MCP, MCTS (GDBA/EDA)

  • June 19, 2008 at 10:47 am

    #831242

    The conflict is probably comming from the data/log file locations. Did you change the paths for these files before attempting to restore?

    I would go with Suresh's suggestion and use the RESTORE DATABASE command via QA.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 19, 2008 at 11:17 am

    #831266

    the problem is with the physical file names, they are being restored to the same location as the orginal db files which is causing the conflict. change the physical file names of the new database and the restore will complete.

    Gethyn Elliswww.gethynellis.com

  • June 20, 2008 at 10:52 am

    #831943

    Restore filelistonly from disk = 'Backup Location and name'

    Get the Logical Names...

    Then execute the Following

    RESTORE DATABASE DATABASENAME from disk = 'Backup Location and Name'

    WITH REPLACE,

    MOVE 'Logical Data File Name' to '(New Location and name)',

    MOVE 'Logical Log File Name' to '(New Location and Name)'

    Maninder
    www.dbanation.com

  • June 20, 2008 at 5:04 pm

    #832125

    I think if you check the box 'force restore over existing database' (on the option tab; next to general tab on the EM restore utility), it will work.

  • June 21, 2008 at 1:20 am

    #832179

    Thanks Suresh 😀

Viewing 9 posts - 1 through 8 (of 8 total)

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