SQL Server Performance

migrate db to another machine

Discussion in 'General DBA Questions' started by v1rt, Oct 29, 2009.

  1. v1rt New Member

    I would like to learn how to transfer a database from machine A to machine B on MSSQL 2000. I remember seeing files for the databases. I think the file extension is .mdf. Can I just copy these files to the other machine and mount it?
    Thanks.
    Neil
  2. techbabu303 New Member

    There are many ways to do this it depends if the task needs to be performed with downtime or should be done without it.
    With Scheduled downtime where no user's are digging gold on server
    METHOD -1
    Take full backup of existing database using the script below, remember to have disk space for doing the below.
    BACKUP DATABASE <DATABASE NAME>
    TO DISK = <FILEPATH with filename with .bak extension>
    WITH INIT
    Then move the backup to secondary server and restore using the script below
    RESTORE DATABASE <NEW DATABASE NAME>
    FROM DISK =<FILEPATH with filename of backed up file >
    WITH MOVE <logical file name of data file of database backup earlier> TO <New file path of .mdf file name for new db>,
    MOVE <logical file name of log file of database backup earlier > TO <new file path of x.ldf file name for new db>
    METHOD -2
    Stop SQL server and copy the mdf and ldf files to new location and attach the database. This more cumbersome process refer book online for further details
    Without Down Time
    Refer logshipping in BOL for setting up secondary server for standby option
    Regards
    Sat
  3. v1rt New Member

    That is awesome!!! [:D]
    Thank you so much!

Share This Page