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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(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

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

Thank you for the prompt reply all of you !! the jobs that i spoke about are referenced across the servers.. talking about the next part of the question about the SQL Server integration and Analysis services? how would they be affected any idea ?
Feb 16, 2010 at 11:58 AM Katie 1
(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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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

Vasudev gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1141 times

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