July 11, 2003 at 10:22 am
I have the enviable job of moving a database from one server to another (both SQL Server 2000). I've managed to copy the actual database (tables, views, triggers, sprocs), but I have no clue how to transfer the jobs, roles etc...
I've tried using the DTS Import/Export wizard and choosing "Select Objects", but none of the database objects appear in the list. Am I looking in the totally wrong place, or is this the way to copy that stuff over? If anyone else can also think of any important items to check during the big move, I'd appreciate the tip. I'm a web developer, not a DBA, and we don't have a *real* DBA onhand anymore to handle this kind of stuff.
July 11, 2003 at 2:08 pm
I hope I'm on the right track here for you. When you do the export, in the screen that says "Select Objects to Copy", you have to uncheck "Copy Objects" and then look in "Select Objects" to choose. Otherwise, it chooses to copy all the objects of the default database (it should show you that in the Summary before you click on Finish). You should go through each option thoroughly in the "Select Objects to Copy" screen (i.e., Default Options includes triggers).
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
July 11, 2003 at 9:05 pm
Check Andy Warren's articles about moving databases. To get the entire contents of a database, the easiest solution is probably detach/reattach, which he covers. This also gets your roles for you. It won't however, get your jobs. Once you get it moved, you may have to reconcile users and logins on the new server, and that's typically done with sp_change_users_login.
You can script the jobs using Enterprise Manager. You can either do this one job at a time or else script all of them at once.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 14, 2003 at 3:41 am
If you are setting up a new server as a copy of the old server there is another way of moving jobs, alerts, operators and DTS packages. Backup the MSDB database on the source server and then restore it onto the destination server, but be very careful.
WARNING: Doing this will overwrite any current DTS packages, etc that exist on the destination server, so it only works if you want to create a copy of the server, not to ammend an existing version.
I cannot stress that last warning enough as rebuilding lost work can take days or weeks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply