Fastest way to move a SQL Server database between systems and SQL Server versions?
Solution 1:
65 GB really is trivial, but may not be over in copying over a slow LAN, or if you can't afford a few minutes of down-time.
The fastest way to cut-over (and keep it simple) is to do logshipping (no recovery mode) between your your existing db and your new db.
You should be able to copy logins & agent jobs before the cut-over & leave them disabled till you move your live traffic over.
Not really a mistake to switch servers and version of sql server at the same time, depends on your requirements. Just remember that bringing the db online on sql server 2008 R2 is a one way process that will upgrade the data files the first time the db does a recovery & there is no going back!
Your other not so simple option is to do the following:
- on your new server, install the same version of sql server as your existing server
- setup database mirroring btwn existing & new server
- do the same prep in copying logins/agent jobs/ other dependencies
- failover the mirror to new server making it the primary (assuming you have support from your app to do this)
- update existing server to 2008 r2, then fail back the mirror upgrade new server to 2008 r2 then failover again remove the mirror, shutdown old server
There is probably many variations of the above approaches. The moral of the story is that you have to consider the cost of the cutover vs. the cost of down-time. It's a trade-off.
Solution 2:
You can detach your database from the old server, copy the physical files and attach it to the new one; it will happily attach to a different SQL Server version, and if you've already transferred logins (using one of the suggested methods), everything will work instantly.
Taking a full database backup and a log backup (and optionally shrinking the database) before the transfer will help reduce the size of the files to be copied.
Solution 3:
This article includes a couple of stored procedures that make login transfer very easy.
http://support.microsoft.com/kb/918992
It maintains the original sid of the login as well, so your database users won't require sp_change_users_login to match up the new login's sid to what is stored in the user db.
Solution 4:
I'd backup/restore with a FULL backup the night before or so, then do a differential backup/restore on the day. I wouldn't copy the MDFs and LDFs. I've migrated databases that around 400Gb on disk this way before.
Instant File initialisation for MDFs makes this quite quick
Logins I'd have already scripted and applied, jobs setup and disabled etc already.