Post reply

restoring the database or attachinf the datafile

  • January 6, 2006 at 12:50 pm

    #96260

    I have database DB1 on Box1  and I have the full backup of that database and also have data file and log file.If I want to restore the database on a diff box How can do that.

    1.How can I restore the Database?

    2.How ca I attach the data file to the database on new Box?

    Thanks.

  • January 9, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • January 9, 2006 at 8:16 am

    #613576

    I have done this several times by copying the BAK file to the new server and using the MOVE option. From BOL:

    MOVE 'logical_file_name' TO 'operating_system_file_name'

    Specifies that the given logical_file_name should be moved to operating_system_file_name. By default, the logical_file_name is restored to its original location. If the RESTORE statement is used to copy a database to the same or different server, the MOVE option may be needed to relocate the database files and to avoid collisions with existing files. Each logical file in the database can be specified in different MOVE statements

    Because my backups are all full my BAK's tend to be quite large so we use a portabe hard drive connected directly to the source server to grab the file versus trying to copy from one server to the next over the network.

    Hope that helps.

    Ross

     

  • January 9, 2006 at 8:32 am

    #613593

    You can:

    1. RESTORE the backup file to the new server.

    or

    2. Attach the .mdf and .ldf files.

    Refer to the BOL for the syntax for RESTORE DATABASE and sp_attach_db.

    -SQLBill

  • January 9, 2006 at 10:10 pm

    #613723

    Attaching is probably easier and also means you keep your DB diagrams

    After restoring, make sure you also look up sp_change_users_login to map database users to logins on your new server.

Viewing 5 posts - 1 through 4 (of 4 total)

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