Best methods for moving sql server database to new server for least amount of down time

Hi.  It's been a long time since i've had to move a sql server database and the sql server connections were always already set up for us by the Network techs.  This time, sql server will be installed on the new server (same version 2008 R2) but I will probably have to set up the connection and initial security etc.  I've been looking at the methods now available to do this and having a hard time figuring out the best one to use..   We will be keeping the old server/sql server running (being used) while we set everything up on the new.  In a few days when we know everything is working, we will want to bring the new up to date with the old, disconnect the old server and start everyone using the new.  So which would be the best method to use knowing that I need to do it again in a few days and keep the down time very low?
1.  Using the Move/Copy wizard..  This option looks as if I'll be able to copy the database to the new server as long as I have the initial security ... owner and sysadmin rights set etc.   It appears that I can also bring over the log ins etc.  
2.  Full Backup/Restore method..  It looks as if this will NOT bring over all the log ins, security etc.  True?  
3.  Detach/Attach method?

If I was able to use #1 and copy to the new server/instance etc., what would be the best way to bring the data up to current when we go live in a few days?  I guess it all depends too if I have any problems initially with anything but I'm just looking for anyone with experience to point me in the right direction.  Thanks!
HometownCompAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OriNetworksCommented:
You can run  sp_help_revlogin on the old server to generate a script for creating all logins on the new server. I also have an upcoming migration and I plan on using this and performing backup/restore operations to get the databases over to the new server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OriNetworksCommented:
Overall the downtime required depends on how long it will take to move the databases over to the new server then to configure any applications to point to the new server name.
0
HometownCompAuthor Commented:
good tips... so do you think if I'm able to do the backup/restore to new server initially and get the logins scripted/set properly etc...  then I should be able to run backup/restore again in few days without too much trouble?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Start This Course
OriNetworksCommented:
You might want to test it twice to make sure you have the steps working correctly. For example, the first time you move the databases over, they wont exist on the new server. But the second time you try, they will exist on the new server so you might have to do something different to overwrite them. That is the only condition I can think of but other than that it's should go pretty easily.
0
HometownCompAuthor Commented:
Thanks, I'll let you know how it went..
0
Scott PletcherSenior DBACommented:
Make the new install exactly the same version and patch level as the current version.  [You can add additional SPs/patches later if you need them.]

Restore the master and msdb dbs.  If you have custom things in the model db, restore it also, or script out the items in model from the old db to add them to the new.

You can restore user dbs in steps: first restore the full backup, ahead of time; then, at point-in-time of cutover, just create a differential backup and restore it.  Typically diff backups are extremely fast to create and apply.  Be sure to use compression for all backups if available.

To test that:
1) Take a full backup of a sample user db(s).
2) Restore it(them) to the new server WITH NO_RECOVERY.
3) Allow 4 hours of so of new processing on that(those) db(s), take a differential backup.
4) Apply the differential backup to the new server WITH NO_RECOVERY.
5) If you want, also apply specific tran log backup(s).
6) When that's ready, do a final RESTORE [db_name] WITH RECOVERY; to make the db useable.  Check it to make sure it has all current data.

At cut-over time, be sure to stop all other backups that could run so you don't get another backup coming in, which would force you to use that recover from rather than the one you already have pre-restored and ready to recover.
0
HometownCompAuthor Commented:
Thank you all..    I actually accepted the solutions and awarded the points already (a few days ago) and was surprised to receive the message saying that this was not done...  I hope you get credit this time!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.