Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We're moving our non-trivial SQL Server database [65Gib] to a new server.

Added in the mix, we're moving to SQL Server 2008 R2 from SQL Server 2005, and ideally need to take some time doing ALTERs to move various tables into different filegroups.

The traditional knife over would be :

  • Down OLD Server
  • Copy off DB files (Just realized I'll have to find some other way of copying over user logins from masterdb)
  • Copy them over GigE to new DB
  • Import DBs into NEW Server and allow any upgrades to process.
  • Complete ALTERs
  • Enable NEW Server

I'd love to pre-load as much of the transfer as possible; is there a good [simple] way to copy over a live version of the database and continue updating the target server until all the prep work is done? It occured to me that I could use replication, but I wasn't aware if I could then promote the Replication Slave to Master...

Or am I making a mistake trying to switch servers and software versions at the same time?

share|improve this question
Switching software versions, at least as far as the DB is concerned, is neither here-nor-there. Just attach and go. You can then further tune it (set the compatibilty level to 110, for example), but to get it off the ground it's not an unreasonable thing to do. – Mark Henderson Jul 21 '10 at 1:31
up vote 8 down vote accepted

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.

share|improve this answer
Can you DB mirror across different versions of SQL Server? – gbn Jul 21 '10 at 4:47
I would not call 65gb trivial. It is "small", agreed, but it is not "trivial" in that it requires NO care at all. Most people here would be challeneged to even think of it (says more about them, agreed). Otherwise +1. – TomTom Jul 21 '10 at 5:03
not while the db's are at different versions. but it's still a way to minimize down time – Nick Kavadias Jul 21 '10 at 5:05
I found this to be a useful guide to plan the creation of the mirror and the steps after the mirror needs to be upgraded.… – Rizwan Kassim Sep 14 '10 at 0:33

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.

share|improve this answer
this is probably the another good approach for Rizwan, which is restoring logs without having to go through the trouble of setting up logshipping. – Nick Kavadias Jul 21 '10 at 5:07

This article includes a couple of stored procedures that make login transfer very easy.

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.

share|improve this answer

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.

share|improve this answer

Database Mirroring will work across different versions of SQL. I did exactly what you are attempting by using database mirroring and having the logins migrated ahead of time. The catch is you have to setup DB mirroring with t-sql, the gui won't accept it, also you can't go back so take a full backup of your DB just before you want to cut over, fail the mirror over, do sp_change_users_logins 'Update_One' 'user' 'user' to sync your new logins with the users existing in the db and you're done. DB migration of any size in less than 10 seconds. Even less if you have it all scripted.

share|improve this answer

Why would you not copy the MDF's and LDF's when you are performing the restore? Our database is approx. 350GB in

share|improve this answer
You could copy the MDFs and LDFs, but that would lead to a longer outage. Something like Log shipping would be a better option as the outage window would be reduced from hours to minutes. – mrdenny Jan 21 '11 at 19:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.