Technical Article

All Encompassing Disaster Recovery Script

,

A script that will help you deal with and get through any disaster recovery situation.

-- Disaster Recovery Script
--
-- Script to handle any type of disaster that might befall 
-- a SQL Server database
--

declare @disaster varchar(50)
, @reporter varchar(50)
, @timetorecover int
, @action = varchar(2000)
, @dbaffected varchar(50)
, @tableaffected varchar(50)
, @return int

select @reporter = caller
, @disaster = problemreported
 from DBAPhoneQueue

if @disaster = 'slow performance'
 begin
set @action = 'The database server is fine, we suggest you contact the Network Administrator.'
   return
 end

if @disaster = 'data deleted'
 begin
-- Check for a single table
if ( select count( numberoftables) 
from DBAPhoneQueueDetails
) = 1
 begin
-- VIP reporting issue
if (select role from HRDB.dbo.Employee
where employeename = @reporter
) >= 'Director'
 begin
exec @return = RestoreLastBackupOnSpareServer @dbaffected, @tableaffected
If  @return = 0
exec NotifyCallerOfRestore @reporter, 'Successful Restore'
else
 begin
exec UpdateResume
exec DisperseResumeToHeadHunters
exec NotifyCallerOfRestore @reporter, 'We are having issues with the restore'
 end
       end
      else
    begin
        exec NotifyCaller @reporter, 'We will get to this as soon as possible.'
     waitfor delay '1:00:00'
  exec PageDBAToStartRestore @dbaffected, @tableaffected, ''
    end
     end
 else
  begin
if datepart(hh, getdate()) > 15
exec AutoOrderDinner @vendor='Pizza Vendor', @order='Long workday special'
else 
exec AutoOrderLunch @vendor='Sub Vendor', @order='Expensive Lunch Special'

exec NotifyBoss @reporter, 'This user has affected production servers by deleting multiple tables of data'

exec @return = RestoreLastBackupOnSpareServer @dbaffected, @tableaffected
      exec PageDBAToStartRestore @dbaffected, @tableaffected, 'Check Q, multiple tables affected.'
     end
 end

if @disaster = 'Server Crash'
 begin
select @hardware = equipmentlist
 from ServerInventory
 where databasename = @dbaffected
select @hardware = @hardware + top 2 items
 from DBAWishList

exec OrderNewHardware @hardware

If (select DBALocation from PersonalTracker) = 'Game Room'
 begin
exec NotifyUsersofLongDelay @dbaffected
waitfor delay '0:30:00'
      exec PageDBAToStartRestore @dbaffected, @tableaffected, 'After this game, you need to check on a server.'
 end
else if (select DBALocation from PersonalTracker) = 'Lunch'
 begin
exec NotifyUsersofReallyLongDelay @dbaffected
waitfor delay '1:30:00'
      exec PageDBAToStartRestore @dbaffected, @tableaffected, 'Sorry to interrupt you lunch.'
 end
else 
 begin
exec NotifyUsersServerDownForDay @dbaffected
waitfor delay '2:30:00'
      exec PageDBAToStartRestore @dbaffected, @tableaffected, 'DB not needed until tomorrow'
 end

 end


if @disaster = 'Data Center Meltdown'
 begin
exec UpdateResume
exec DisperseResumeToHeadHunters
exec SubmitAllExpenseReports @DBAOnCall
exec NotifyManagement 'We are currently working on the restore and expect to have everything restored within a week.' 
waitfor delay '1:00:00'
exec PlaceBlameElsewhere @user='Random', @category='Annoying callers', @message='We have discovered that the person named below is responsible for the data center issues.'
exec InfiniteLoopQuery 'The database is still being restored'
exec LeaveWithAllPersonalBelongings
 end

Rate

Share

Share

Rate