Where are SQL 2000 Jobs stored on Hard DIsk?

Question

Where are SQL 2000 Jobs stored on Hard DIsk?

pete-675551

SSChasing Mays

Points: 652
More actions
July 21, 2009 at 7:14 am

#385020

Hi
I had to re-image my drive due to hackers. I have done that, reinstalled SQL 2000, and set up my databases. I have old drive set up as slave.
The problem is, I forgot to save all my SQL Jobs. Can I grab them off the old drive?
Does anyone know where SQL server stores the SQL job information?
Thanks
Pete

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

All of the jobs are stored in the msdb system database. If you have a backup of that database on your old drive, you can restore it over your new msdb database and the jobs will reappear.

pete-675551 SSChasing Mays Points: 652 More actions · Answer 2

Thanks but I am still having issues. I have a back up of MSDB from the other sql installation which contains the job info. So I try to restore the back up into the new sql installation and I get error - Cannot be restored because it was created by a different version of the server.

So is there anyway to extract the job info from the back up.

Thanks

Pete

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

Your new SQL Server is at a lower version than the old SQL Server and database restores cannot be performed to a lower version.

You can get the version of the old server from the backup file by running the below SQL and examining three columns: SoftwareVersionMajor, SoftwareVersionMinor, and SoftwareVersionBuild. Example values might be 9 ,0,3042. RESTORE HEADERONLY FROM DISK = ' backupfile name'

You can get the version of the new SQL Server by running select serverproperty('ProductVersion') An example value might be 9.00.2047

Then use this site's SQL Server build list to determine what Service Packs and hot fixes need to the applied to the new SQL Server to get to the same version as the old SQL Server.

http://www.sqlservercentral.com/articles/Administration/2960/

SQL = Scarcely Qualifies as a Language

ps. One Orange Chip Points: 29422 More actions · Answer 4

pete (7/21/2009)
Thanks but I am still having issues. I have a back up of MSDB from the other sql installation which contains the job info. So I try to restore the back up into the new sql installation and I get error - Cannot be restored because it was created by a different version of the server.
So is there anyway to extract the job info from the back up.
Thanks
Pete

I can think of two ways.

1. Install a sql server with the same version as ur msdb backup file. Restore the msdb database, script out jobs and schedules, apply the scripts on the target msdb database.

2. restore the backup to a different database(not msdb) and copy the contents of jobs and related tables to the current msdb database. Table structure can be different. so be more careful in doing this.

Pradeep Singh