dcsimg
Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: moving database to new server

  1. #1
    Join Date
    Aug 2006
    Posts
    27

    Wink moving database to new server

    hi guys

    i got few questions. i need to move a databases from old server to new server. old server has 25 user databases total datafile around 300GB. heres the questions.

    1. what is the fastest way to move all the data to new server? and what's the best way to do it? (DTS? MDF copy over/attach? bak copy over/restore? other theres other way?)

    2. i got alot of job and user need to transfer over. i know DTS can do the user.. how about the jobs?

    3. do i need to move the master and msdb over too?

    4. beside all 3 questions above... do i miss anything that need to be move too?


    Thanks guys!!!!!!!!

  2. #2
    Join Date
    Oct 2006
    Posts
    1
    try SQL Server Copy Database Wizard.
    Is a wizard that helps to dettach+copy to new server+attach databases to migrate.

    greetings,
    Nelson

  3. #3
    Join Date
    Aug 2006
    Posts
    27
    is that gonna take long compare with just copy the mdf file? because i only got 4 hours downtime..... you know.. some people in top management dont like too much downtime..@@!

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    You can't just copy the .mdf file. You need to do the detach and copy both the .mdf and .ldf files. Search MS support website to get directions on handling the system dbs.
    Last edited by nosepicker; 10-16-2006 at 11:02 PM.

  5. #5
    Join Date
    Aug 2006
    Posts
    27
    yes.. i know what you saying. what i mean is if i detach the database and just using map drive to copy mdf file over to new server and attach. will that be faster than database copy wizard? anyway. thanks for the help.

  6. #6
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92
    An idea I had that helped significantly was with your old server still live, do a full backup (on all your databases) and restore these on your new server. When you take the old server offline do differential backups and restore these on the new server. This way you can greatly reduce downtime especially with regards to the time taken copying files.

    I've done this with 40Gb total and used up every minute of the six hours downtime I was allowed what with testing and all so you may need to beg for more than four hours.

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    You can copy jobs in dts transfer jobs task.

  8. #8
    Join Date
    Sep 2005
    Posts
    43
    The best way is...

    1) Detach all the db's from your Old server.
    2) Request the networking people to copy all the data files from old box to New box.
    3) Attach all the db's

    This works much faster than all....
    After that transfer your logins by running scripts.
    Transfer ur DTS packages n jobs.
    Maintenance plans are to be created again.

    Thanks
    Bob

  9. #9
    Join Date
    Aug 2006
    Posts
    27
    Thanks guys.. now i have so many options
    big boss dont like too much downtime.. so i was thinking. get a full backup in live server and restore on new server few days b4 i switch the live server , and do the differential backup on live and restore on new server everyday. until the day i need to switch than do the final differential restore again after i change single user in live server(so no one can insert). --> DTS user / jobs ---> switch name..

    one more question is.. do i need to restore the master or msdb? please let me know .. thanks guys...~~

  10. #10
    Join Date
    Dec 2004
    Posts
    502
    Keep in mind that a differential backup is cumulative, not additive. In other words, if you do a full backup on Sunday, then a differential backup on Monday, and then another differential backup on Tuesday, the Tuesday differential will include transactions since the full backup, not since the last differential backup. Therefore, it would probably be best to do a full backup, restore it onto the new server, do a differential backup, restore that onto the new server, and then do periodic log backups up until and immediately after you take the original database offline. When I say "offline", I mean you stop the apps from making updates to the data. Obviously, you can't do a log backup if you take SQL Server down. Then you restore all the log backups (which are additive) in order, ending with the last log backup done after the database was taken offline.
    Last edited by nosepicker; 10-18-2006 at 11:08 AM.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    If you managed to move all sql logins, jobs and so to new server already, then don't have to restore system dbs from old server.

  12. #12
    Join Date
    Aug 2006
    Posts
    27
    ok.. thanks Rmiao.. nosepicker.. and all.. this's all i need.. thanks again.~~

  13. #13
    Join Date
    Aug 2006
    Posts
    27
    ok.. here's another question.. make me really confuse.

    when i restored database in new server from bak file.. i check the user under database..
    not under security..
    i see all the original userand auth... but not showing under security user.
    so i use DTS to transfer user from old server to new server. users did transfer over.
    but all the original setting auth are gone.. like my account was sa.. but after DTS to
    new server.. i am not SA no more. and funny thing is under database user.. the user still got
    same auth like the old one. but the user under security got nothing.. they not match..
    so is there a way to make them same? and or there's any other way to do it? or as long as
    they got permisstion under database user it dosent matter under security?
    or i need to restore the system database to get it right? please help. thanks

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    You can remap db user to sql login with sp_change_users_login.

  15. #15
    Join Date
    Sep 2006
    Location
    East Coast Of America
    Posts
    15
    If you want to transfer all logins and passwords from original server, there is a MS link telling you how to do it. It works!
    http://support.microsoft.com/default...b;en-us;246133

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •