January 5, 2009 at 2:38 pm
We set up a new server and want to move our databases and scheduled jobs to this new location. What is the best approach to accomplish this?
January 5, 2009 at 3:23 pm
I've used two methods to move databases:
1. Detach the databases, move files, and attach the databases
2. Backup and restore the databases
With either of these methods you'll have to recreate logins on the new server and sync them with users after the databases are relocated. There are other considerations if any of the databases are replicated or if there are linked servers defined.
You'll find a ton has been written about this subject. If you search on this site, Google, or Books OnLine, you'll find checklists and advice galore.
As for moving jobs, I prefer to script them out on the current server and run the script on the new server.
Greg
January 5, 2009 at 3:28 pm
For DB:
Option 1: If Downtime is permitted and db is of medium size --> put source DB in read only mode --> Full backup and restore on new server.
Option 2: If Downtime is limited and db is large --> take a full backup at time T1 --> Restore db on new server. Next --> Put source db in read only mode --> Transition log backup --> apply transaction log to new server.
Option 3: If downtime is permitted --> Take source db offline --> Move data and log file to new location --> Bring online/reattach on new server (sp_detach_db/sp_attach_db)
In all the above cases instead of using UI its best to have a set of scripts ready to speed up the process.
For DTS/Jobs:
Option 1: If # DTS packages are limited --> Open pkg --> Save to New Server (Scheduled will have to be created separately on new server)
Option 2: This is tricky but works --> for DTS packages --> MSDB database has details for DTS package --> Its possible to copy/insert the rows from one server to another --> Use import export etc.
As far as I know Jobs will have to be rescheduled again on new server
---- [font="Tahoma"]Live and Let Live![/font] ----
January 6, 2009 at 6:22 am
I didn't see anyone mention it, but if you're moving scheduled Agent jobs, just script them out on the first server and you can run them on the second. Edit as necessary. This should be the easiest part of the move.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2009 at 4:48 pm
also instead of re creating and syncing the logins use the following link
http://support.microsoft.com/kb/246133
to move logins between servers, preserving the database to user relationship
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply