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 had single production server for website and SQL Server database. We have plan to have a new production server for website and SQL Server. We need to move the SQL Server database from the old server to the new server.

So, how can I migrate/move the DB to new production server with less down time?

Thanks, Velu.

share|improve this question

migrated from stackoverflow.com Aug 16 '11 at 0:40

This question came from our site for professional and enthusiast programmers.

    
BACKUP on the old server, RESTORE on the new server, and once the new stuff is up and running, turn the switch to route your requests to to new server(s). –  marc_s Aug 15 '11 at 8:31

4 Answers 4

up vote 1 down vote accepted

If you use SQL Server 2005/2008 you can use mirroring:

  • install new SQL Server instance
  • create mirror database on this server from production server
  • wait until databases are synchronized
  • turn old server off
  • remove mirror from new server
  • recover mirrored database

Downtime = time to remove the mirror and recover the database

If you don't want to use mirroring, you can do the same work without mirroring:

  • install new SQL Server instance
  • create backup from production DB
  • copy and restore the production DB WITH NORECOVERY option on new server
  • turn the production database into single-user mode
  • make the transaction log backup
  • turn production server off
  • copy and restore the log WITH RECOVERY option on new server

Downtime = time to make log backup, copy and restore this backup on new server

share|improve this answer

Depends - how much do you know?

One way would be setting up mirroring, then switching the mirror and removing the older server. Mirroring, otoh, is a little deeper into sq server than using the UI to set up a database.

share|improve this answer

I know the below isn't the most efficient or advanced way to do thing - but I do think it is a way which almost anyone can do with minimal help and little downtime...

Assuming you're talking about a database for a CMS, you could you get the temporary URL for the new location of the website on the new server (we use external hosting, and they tend to set this up - the temporary URL is normally a suffixed ip address). Then go through the database replacing values referring to the old URL to the new, temporary one.

You can then restore the site on the new server (or whatever it is) also, replacing values for the new URL. This gives you the opportunity to test the database is functioning. When you are happy, you can just change the DNS (or A records or whatever), so the original URL points to the new server, change everything back (which should only take a minute or two with good find and replace procedures) and bobs your uncle. Its not the fastest way to make the transfer, but it should provide the least downtime to your site and peace of mind that it will all work.

What CMS are you using?

share|improve this answer

If the database files are on a LUN by themselves (or with other files that you don't mind moving as well), you could detach the LUN from the old server and attach them to the new. Then you'd do a "create database... for attach" on the new side.

Also, I've grown to like the idea of one node clusters for future needs. That way if you need new hardware, you can add a node into the cluster, run the node setup and you're in business. This assumes SQL 2008+, though.

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.