1

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?

3
  • You have the files from an instance that crashed, and you're trying to attach the msdb from the crashed instance to a new instance?
    – Hannah Vernon
    Nov 21, 2013 at 20:25
  • Exactly. I have both MDF and LDF of the MSDB
    – Lord Relix
    Nov 21, 2013 at 20:52
  • you might be able to use CREATE DATABASE ... FOR ATTACH syntax to attach the .mdf. Be sure to use some other name, such as msdb_old for the database name.
    – Hannah Vernon
    Nov 21, 2013 at 22:07

1 Answer 1

3

You should be able to connect the msdb.mdf using:

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 msdbtest database:

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.

I ran 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.

5
  • I tried this but it gave me a similar error from the 4th line onwards. I corrupted the MDF with a Hex file but no go.
    – Lord Relix
    Nov 22, 2013 at 18:16
  • 2
    I wasn't suggesting to corrupt the file on purpose. I did that to try to mimic your environment.
    – Hannah Vernon
    Nov 22, 2013 at 18:25
  • 2
    @MaxVernon would you go as far as calling that a bad idea?
    – Zane
    Nov 22, 2013 at 18:38
  • @Zane - yah - bad idea!
    – Hannah Vernon
    Nov 22, 2013 at 18:41
  • @LordRelix did you try it without corrupting the mdf first?
    – Hannah Vernon
    Nov 22, 2013 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.