Databases moving accross servers


Currently we are trying to move SQL SERVER databases across different servers. When planning this activity, I am wondering how would the daily jobs related to the database on the current server behave.. i am sure they will be broken.. but how would we handle this situation? Would we have to script the complete jobs again (Create from scratch)?.. or is there a way that these jobs can be moved to the future server?

Another following question is how will SSIS and Analysis services cubes be affected? is there a way, that the transition from the current server to the future on.. goes smoothly, with out much disturbance in the development?

Is there any checklist that needs to be followed accross while implementing this activity?

more ▼

asked Feb 15, 2010 at 07:54 PM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If the jobs solely depend on the databases that have moved, then the simplest and probably safest way is to script the jobs, change any reference to the old server to the new server, and execute on the new server.

If the steps within the jobs still reference objects on the other server, you can look at setting up linked servers to ensure your T-SQL will run across the servers.

Will the jobs need to reference across servers, or are they dependant only on single databases?

more ▼

answered Feb 16, 2010 at 05:50 AM

avatar image

Kev Riley ♦♦
65.9k 48 63 81

(comments are locked)
10|1200 characters needed characters left

There used to be a GUI wizard to transfer jobs from server to server but for the moment my memory is failing produce where that is located, maybe someone else can come up with that..?

However, if there arent many you can right click on a job in SSMS and choose script job as and then select a new query window. This will let you alter the necessary values for the new server, change the connection to the new server and then run the sql to create the job. If you want to script more than one job at a time then open the object explorer details tab (press F7) and multi select them in there and then right click as above.

(In a few weeks I'll (hopefully) be able to tell you how to do it via PowerShell too. Always learning ..!)

more ▼

answered Feb 16, 2010 at 05:48 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left

For Analysis Services Database (Cube) you can take the backup of the database and restore on target server

more ▼

answered Feb 16, 2010 at 01:22 PM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 15, 2010 at 07:54 PM

Seen: 1376 times

Last Updated: Feb 16, 2010 at 06:00 AM

Copyright 2017 Redgate Software. Privacy Policy