Post reply

Migrating databases

  • March 30, 2006 at 3:20 am

    #113357

    Hello,

          I am going to do a SQL Server 2000 database migration. I have plenty of down time to do so. Just want to know what is the most common method to do this? I guess it's attach/detach. Then how about the master/msdb/model databases? I believe we usually restore the master database from a backup instead of attach/detach. And should I migrate user databases first or system databases? Please comment. Thanks.

    Cheers,

    Del Piero

  • March 30, 2006 at 4:56 am

    #629774

    I just go for backup and restore instead of attach/detach options.

    There is lot of about migration in the BOL and MSDN site...check that up.. all d best

    Get busy living ....or get busy dying....

  • March 31, 2006 at 3:29 am

    #630030

    Thanks. Is there a particular advantage of backup/restore over detach/attach option?

    Also, should I first restore the master database from backup, then attach the other databases, or vice versa?

    Thanks,

    Del Piero

  • March 31, 2006 at 5:05 am

    #630043

    Help to read these article ...has worth of info.

    http://support.microsoft.com/kb/224071/en-us

    http://support.microsoft.com/?id=314546

    http://vyaskn.tripod.com/moving_sql_server.htm

    Boy, U shud be able to do it now 😉

    Get busy living ....or get busy dying....

  • March 31, 2006 at 8:15 am

    #630138

    I usually never restore master onto another machine.  Restoring Master is something I would only ever do when something happened to a master database and then I might try to restore master on that same machine but if you rebuilding one environment onto another machine there is no reason to restore master.  If what you need are the logins the are several methods of copying logins/passwords from one machine to another without restoring master.  Check out scripts on this site.

    Francis

  • March 31, 2006 at 10:25 am

    #630189

    Thanks.

    How about Analysis Services cubes and dimensions? Should I just migrate the Access mdb file to the new server, then import it in Analysis Manager?

    Thanks,

    Del Piero

     

  • April 26, 2006 at 10:15 am

    #634373

    fhanlon, Isn't there any critical data in Master that needs to be brought to the new machine ?

    Also, We have our system databases on the server's local drives, but our production databases on a SAN (external storage). Will there be a problem bringing up the new server and having it recognize the data out on the SAN ?   Should I detach the production databases first, then attach them afterwards ?

    I also want to change the default collation on the new server ..... Is that feasable ?

    And I the server is a publisher & subscriber ....  

  • April 26, 2006 at 2:33 pm

    #634437

    The logins and passwords are on master but they can be rebuilt using DTS or one of several scripts found on this site.  Also all databases are registered on master, but as these get attached or restored they are registered again.  If you restore master their may be collation problems if you change the default.  You will need to reset up the publication

    Francis

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

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