Post reply

Restore problem, Unable to attach database

  • January 26, 2007 at 11:16 am

    #68695

    Hi,

     

    We are refreshing our QA server from Prod, We are using Online backup.

    However, after restore was complete and we renamed the datafiles to QA, we are not able to attach the database.

    Please, If you have step by step process for restore/attach/detach procedure send it to me. Also if you know how to solve  this issue please help.

     

    Regards

  • January 26, 2007 at 11:25 am

    #685916

    Well, can you be more specific as to what you are doing?  It sounds like you are doing a restore on your QA box, detaching the DB, renaming the physical files, and then attempting to attach them to your QA instance.  Is this accurate?  If so, are you getting an error?

    You can rename the physical files during the restore using the WITH MOVE clause.  Look at the RESTORE command in BOL.

    John Rowan

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

  • January 26, 2007 at 11:26 am

    #685918

    Hello,

    Did you get an error message?.

    Moving one database to another:

    Possibility 1:

    Detach database from source server, copy files to destination server, (attach databases back at sourceserver),attach database at destination server.

    Possibility 2:

    Backup database at source server, restore backup to destination server.

    Have a look at

    http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/

    for detailed steps

  • January 26, 2007 at 11:35 am

    #685921

    We are restoring from on line backup.

    Error I am getting after renaming the data and transaction log, is it is not a valid sql file.

    However, If I rename it to the original file name I can always attach the database as prod on our QA box.

    I am very new in SQL environment and just have to make this work. I am using the GUI so I am not sure how much I can add here.

     

    Regards

  • January 26, 2007 at 12:32 pm

    #685937

    Hello Kourosh,

    You are confused with restore and detaching the files.

    When you take a backup and try to restore it on a different server, the logical file names will be the same but you can change the physical file names to the one you want from the GUI window.

    When you take a backup and restore it, the logical as well as physical file names will be the same as the source. However, you can change the physical files by detaching the restored database, change the physical file names and then attach them. At this point, SQL Server has the information of the database name and the logical and physical file names earlier to the change. So you need to just point respective logical files to the changed physical files.

    Hope this helps otherwise buzz once again with the logical and physical file names and somebody will route you properly on this forum.

    Thanks

     


    Lucky

  • January 26, 2007 at 3:47 pm

    #685986

    Lucky,

     

    Thank you very much. It worked.

Viewing 6 posts - 1 through 6 (of 6 total)

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