Sql-server – How to rescue SQL Agent jobs from a set of detached master, model, mdb files

restoresql-server-2008-r2sql-server-agent

I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself).

I've SQL backups, and even better, I've got the detached MDF and LDF files as they were on the SAN before everything went wrong. I've now got the task of restoring these back to a newly built server.

The databases themselves, logins and SSIS packages aren't a problem, as I've got all these. The thing that's bothering me is the SQL Agent jobs that were defined on the previous server. I'm dimly aware that these are stored in MSDB somewhere, but what steps should I go through to retreive these from the MDF/LDF files I've already got and load them on the new server?

I'm not going to be restoring the modal, msdb, master and temp databases on the new server, so should I be looking to attach the old files under different names and pull job definitions out of them, or is there some other (better) way?

Best Answer

You will have to attach the MSDB DB under a different name like 'jobsdb' and pull the jobs out of jobsdb. The jobs will be stored under jobsdb.dbo.sysjobs and the job steps will be under jobsdb.dbo.sysjobsteps. They can be joined by the job_id column.

The schedules can be found under jobsdb.dbo.sysjobschedules joined with jobsdb.dbo.sysschedules on schedule_id.

The rows can be added into your current msdb using a insert-select, or by using the stored procedures: sp_add_job, sp_add_jobstep, sp_add_jobschedule, sp_add_jobserver.

Related Solutions

Sql-server – Good way to call multiple SQL Server Agent jobs sequentially from one main job

SQL Sentry has a facility built exactly for this: to chain jobs and arrange them in various workflow orders.

I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup on production had finished.

What I had originally implemented was just a substantial buffer between the backup job start time and the restore start time. This wasn't exactly foolproof; since backup times varied the buffer often left us with wasted time where a restore hadn't started even though it could have. And occasionally the buffer wasn't enough.

What I implemented next was similar to what you have - I wrote a job on the test server that started shortly after the scheduled backup, and kept polling to see when the job was finished. That was later amended to just have a second step in the backup job that updated a table on the test server. Not really much different, except the restore job only had to watch a table locally instead of monitoring the job history remotely. Thinking back this could have been a trigger on that table that called sp_start_job so the job didn't have to run every n minutes (or be scheduled at all).

The final solution was to chain jobs together ... when the backup on server A finishes, Event Manager starts the restore job on server B. And if there was a third job, and a fourth job, or conditional logic based on what to do when a job fails vs. succeeds, etc., this can all be accounted for. The workflow designer will remind you quite a bit of SSIS:

The underlying mechanics of what I'm describing is not rocket surgery, of course. You could write this type of chaining wrapper yourself if you sat down and did it. Just providing you one alternative where you don't have to.

Sql-server – How to attach an .mdf file that was not properly detached

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

on your 2008 instance, create a database with a data file of the exact same size (I wouldn't bother trying to put a 2000 instance anywhere)
shutdown SQL Server
rename the new database's mdf file, and paste the old one in its place
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?

Best Answer

Related Solutions

Sql-server – Good way to call multiple SQL Server Agent jobs sequentially from one main job

Sql-server – How to attach an .mdf file that was not properly detached

Related Question