Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We've got a couple of SQL Server 2005 databases we will be moving to a new production server soon. These databases aren't massive but big enough to make it a tricky to do so with as little downtime as possible.

The three databases that will be moved first, since they are the most critical, are 5, 9, and 25 GB in size (data only without logs).

Now there are a few possibilities but since I am not a full fledged DBA I thought maybe some people here might have better ideas/suggestions. Here is what we came up with;

* Create a full backup, move the file and restore the backup.

This is possible but since the databases are rather big this would mean some pretty serious (couple of hours) of downtime of the system since the databases need to be moved.

Is it possible to create and restore a backup today, and then do a differential restore when we make the actual move? The problem I can find so far with differential restores is that those always get added to a FULL backup which would leave the files the same size and not reduce downtime due to moving the files from server to server.

To make this "more" tricky the new database will be configured to mirror, where the old environment is not mirrored. Meaning I'd have to restore the differential backup on the principle server (I don't think this should cause problems but I thought I'd ask.)

If there is another, easier or better way to do this with the least downtime I'd love to hear it as well, of course.

A user on StackOverflow replied simply with "You can use mirroring to do this". Without going into a lot of details, the way I see this is that I can create a mirror on the new principle, then force the mirror to take over from the old production server. I then disable the mirroring and re-enable the mirroring to the new mirror server.

Would it work like that?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

I did this type of migration several times and the best way (for me) is:

  • complete backup (with database in use)

  • transaction logs backup every n minutes (n depends by time to copy the complete backup)

  • copy the complete backup to new server and then restore the database without recovery (RESTORE....NORECOVERY)

  • copy and restore (always without recovery) the transaction logs

  • when the new database is almost online, stop the applications using the old database, make last transaction logs backup, copy it to new server and restore it with recovery.

  • now you have the database on new server with a very little downtime.

share|improve this answer
    
This is exactly what we will do with one added bonus, since we will impelement a mirror the full backup will be moved to two servers and restored on both so we can instantly have a mirror environment when the transfer is done. –  F.B. ten Kate Sep 8 '10 at 14:53

As far as backing up and moving the databases is concerned, I regularly back up a 30GB database in less than a half hour. If you back them up to an external USB drive connected to the current server and transfer them via the USB drive to the new server and restore them I don't it taking more than about an hour, give or take a few minutes.

share|improve this answer

Ill try to give a point,
Maybe I am a wrong let others say if this is not regular way Can You set up transactional replication to NEW server and publish and subscribe databases, so you get both databases running constantly in lag of smaller then few seconds.
After that you close connection to your old database from clients and wait few seconds.
Disable replication and start use new datbase

share|improve this answer

Your Answer

 
discard

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.