I need to recover the jobs from a MSDB file. I had to fully reinstall a new SQL Server and Windows Server, but apparently I forgot to ever do a MSDB backup. Yup, big blunder. Now I need to restore it or at least get the jobs out of it, which are essential to day-to-day operations. Our tape backups that have the backup I need will arrive here by Saturday but I don't want to risk another day without those jobs running. I tried to the sp_dbattach procedure so I could get the scripts out but apparently the log file is broken because it had some transactions going on when the other server went down. Do I have any option?
You should be able to connect the
CREATE DATABASE msdbtest ON ( NAME = 'msdb_data' , FILENAME = 'c:\path_to_your_old_msdb\msdb_data.mdf' ) FOR ATTACH;
Note, depending on the operating system where you are running the above command, you may need to elevate permissions for SQL Server Management Studio (runas administrator) for the command to work. See my question here for details.
I purposely corrupted the .ldf on my machine before attaching the file, and received the following error message about the newly attached
File activation failure. The physical file name "C:\SQLServer\Logs\msdbtest_log.ldf" may be incorrect. New log file 'c:\sqlserver\data\msdbtest_log.ldf' was created. Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa3ba6e3e; actual: 0x208d1401). It occurred during a read of page (2:0) in database ID 11 at offset 0000000000000000 in file 'C:\SQLServer \Logs\msdbtest_log.ldf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
DBCC CHECKDB('msdbtest') which reported no errors. I was then able to run the following
SELECT statements to see details about the jobs stored in msdb.
SELECT * FROM dbo.sysjobs SELECT * FROM dbo.sysjobsteps SELECT * FROM dbo.sysjobstepslogs SELECT * FROM dbo.sysjobschedules SELECT * FROM dbo.sysjobservers SELECT * FROM dbo.sysjobhistory SELECT * FROM dbo.sysjobactivity
You could use the information stored in these tables to recreate the jobs.