November 26, 2002 at 7:58 am
How can I move a master and msdb system databases from one server to another server
considering the target server has different collation from the source server.
November 26, 2002 at 9:09 am
What information are you trying to move from these databases?
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
November 26, 2002 at 9:43 am
I want move logins, jobs, operators, DTS packages, maintanance plans, linked servers.
November 26, 2002 at 10:10 am
There is a script at: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
to move logins
You can script the jobs, and operators in EM.
Don't know how to script of move the linked servers, although you should be able to redefine easily if you don't have to many.
Packages can be saved onto the new server, or a file and then the new server.
And here are the steps to move the maintenance plans:
1) Run command on source server
execute sp_help_maintenance_plan
2) From the output determine which plan_id you wish to generate, then issue the following command on the source server replaceing <plan_id> with the plan you are thinking about generating:
execute sp_help_maintenance_plan '<plan_id>'
This command should identify the databases that the plan is associated with. Note the databases and SQL Server agent jobs associated with this plan.
3) Generate the script for the SQL Server agent jobs from the source server.
4) On the target server create the all the SQL Server agent jobs for the maintanenance job
5) Create the maintenance plan on the target server by issuing the following commands:
DECLARE @myplan_id UNIQUEIDENTIFIER
EXECUTE sp_add_maintenance_plan N'Myplan',@plan_id=@myplan_id OUTPUT
print @myplan_id
Note the plan_id that was created.
6) Is the following command on the target server, one for each database identified in step 2:
Execute sp_add_maintenance_plan_db <plan_id>',N'<database>'
7) Then execute the following on the target server, once for every job created.
EXECUTE sp_add_maintenance_plan_job N'<plan_id>', N'<job_id>'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply