Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
inceebee
Starting Member
2 Posts |
 Posted - 2007-04-19 : 17:51:43
|
| I had several jobs scheduled in the SQL Server Agent before I upgraded from MSDE to 2005 Express. I planned to recreate the jobs in the scheduler later, now it's been a couple of months and I realize that we hadn't kept those scripts outside the database anywhere. Is there any way to recover those job files? We have database backup files going back quite a while, but I'm not sure how I'd go about restoring them. I guess I'd need an instance of SQL Server 2000 (which I have). Would I just restore an old backup there? Is there an easier way?I'd be grateful for any help you can give.Nate Baxley |
|
|
mikewa
Microsoft SQL Server Product Team
84 Posts |
Posted - 2007-04-19 : 18:31:32
|
| Hi Nate,As you may have noticed, SQL Express doesn't support SQL Agent or Jobs, so you can't use them in this Edition. For this answer to be correct, I have to assume that you did an in place upgrade from MSDE to SQL Express, in other words, you copy of SQL Express is using the same system databases that you were using with MSDE.If that's true, then all your Jobs are still there, happily being usless in the MSDB database. The bad news is that you will not be able to move or restore that database to SQL 2000 because the database format is not backward compatible. There are a couple options:Easy: Move up to a higher edition of SQL Server 2005 like Workgroup. It might even work to install the Evaluation Edition, which has a 180 day lifespan and use that to export your Jobs.Hard: You could write T-SQL to parse through MSDB and extract your Jobs. They are in the tables with names like sysjobs*. You'd obviously have to reconstruct the information based on all those tables.Regards,Mike WachalSQL ExpressThis posting is provided "AS IS" with no warranties, and confers no rights.Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm |
 |
|
|
inceebee
Starting Member
2 Posts |
Posted - 2007-04-20 : 15:04:07
|
| Thanks for the quick reply Mike. You are a lifesaver! I was able to quickly find my job in the sysjobs table and since the jobs were entirely scripted it was easy to pull out the code. I haven't implemented them yet, but it looks like it's all there. If you're ever in central Illinois, look me up and I'll buy you a beer.Thanks again!Nate Baxley |
|
|
|
|
|
|
|
|
Lost SS 2K Job Files