question

Katie 1 avatar image
Katie 1 asked

Databases moving accross servers

Hello,

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?

ssisssasadministrationjob
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Kev Riley avatar image
Kev Riley answered

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?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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 ..!)

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Vasudev avatar image
Vasudev answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.