Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2010-11-30 : 13:31:30
|
| what is the best way to move a database from one server to another with the least downtime? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-30 : 14:01:05
|
| Full backupRestore to new location WITH NORECOVERYif that has taken a while then repeate using a DIFFERENTIAL backupMake TLog backupRestore all TLog backups since Full (or Diff) backup to new location WITH NORECOVERY*** START OF DOWNTIME ***Throw users offSet Source database to SINGLE USER + READ ONLYMake final TLog backupRestore Final TLog backup (watch out for and any other Tlog backup that may have been generated by Maintenance Task) to new location WITH NORECOVERYRESTORE NewDatabase WITH RECOVERY - to set it to become activeChange the NewDatbase to be MULTI_USER and READ-WRITENOTE: You may have to change the Connection String for the application, or swap over the IP address of the new server so it replaces the old one, otherwise users won't connect to the new server!*** END OF DOWNTIME ***The various steps need scripting so that they can be run easily without having to click on the GUI tools etc.! and a dress rehearsal needs to have been performed to make sure the processes will run smoothly "on the day"When we do this we aim to have less than 2 minutes down time, more typically on our 24/7 systems the downtime is so short that the users barely notice - we give them a Holding Page for the minute or so with instructions to wait for their session to resume. The holding page does a Meta Refresh every 10 seconds, and will get back in when the site is available againIf you are also upgrading to a new version of SQL then there are other steps to consider. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-11-30 : 14:16:08
|
| what's the best way to do a clean complete full backup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
what is the best way to move a database