October 28, 2011 by Mike Hillwig
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:
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
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.