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?
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?
answered Feb 16 '10 at 05:50 AM
Kev Riley ♦♦
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 ..!)
answered Feb 16 '10 at 05:48 AM
For Analysis Services Database (Cube) you can take the backup of the database and restore on target server
answered Feb 16 '10 at 01:22 PM