Yesterday, I participated in my first DR test at this company. It was a long day, but we learned a lot. This was also our first SQL Server client that we tested. For our Oracle clients, the Oracle DBAs have this process down to a science.

We’re using Log Shipping for our DR environment, and before I opened up the databases, I needed to stop the LS Restore processes for all 22 of the databases, and I wasn’t about to right-click on 22 SQL Agent jobs. That’s when I wrote this little nugget:
[SQL]
set nocount on
go
select ‘EXEC msdb.dbo.sp_update_job @job_id=N”’+ cast(job_id as varchar(60))
+”’, @enabled=0
GO’
from msdb.dbo.sysjobs
where name like ‘LSRestore_%’
and enabled = 1
[/SQL]

What this does is generate a SQL script that I can copy, paste, and run. Sure, I could have encapsulated this into a cursor and EXECute it, but I like to have a little bit of control over these things.  Lately, I’ve become a big fan of writing scripts that generate other scripts.

After the DR test, I write something similar that re-enabled those jobs once I had my backups restored.