How to restore backup of msdb database

Question

I want to take backup of jobs.I know all of these are stored in MSDB database,so i had taken a backup of the msdb database but i'm not able to restore that database as i'm getting a error while restoring on another system that the process terminated abnormally.So,what should i do to restore that.

Thanks Nick Kavadias But the problem is that when i script my job,there r four steps.I dont get these four steps in detail. There is a condition which is used in those four steps which automatically generates an exception.But i'm not able to find that condition in the script

Answer 1

probably belongs on serverfault. But i think i know what Tarun is talking about.

IMHO the best way to take a copy of all your SQL Server agent jobs is to script them out using management studio. You can do this by clicking on jobs in object explorer, then object explorer details window. All jobs in this instance will be listed here, Select all, and pick script jobs from the right click context window. This will generate a t-sql script with all the jobs on the server.

If you do wish to restore the msdb database ( to keep job history ect.) then you will need to stop the SQL Server Agent service first.

Answer 2

Hi Tarun,

Recently I got a chance to transferred the jobs from SQL Server 2000 to SQL Server 2005 using SSIS (BIDS) In SSIS there Jobs Transfer Task, you needs to select and give the name and select the source and destination servers, you can transfer all the jobs or specific jobs.

Please try this option, I think it will solve your problem

Cheers

Venkat