automate the recovery test

[masds]

I want automate the recovery test for our databases, I have a test server which is for all the SQL servers in our company, I can write code to run the restore command with backup files in correct sequence for each database, the problem is I don't know how to write the verification code, I need to verify the restore is OK, if not I need log the error somewhere. Thanks

 

[Catadmin]

I keep track of Transaction Log backup restores in a table that has space for an error message. The programmers here programmed the part where the name of the backup gets put in the table and copied over to the correct server. But on my end, the tracking table looks like this:

TransLog_Status table
Logname Processed
C:\backups\TransactionLog_Tlog_200504050200.TRN 0

ErrNum Deleted Added
<null> <null> getdate()

The Added has the getdate function as a default and does not accept nulls. Deleted is for when I delete the backup with another scripted job. ErrNum is for the Errors I catch.

Here's my restore script, stealing it's connection killing cursor from MRDenny's scripted Log Shipping.

use Master

-- This code restores all logs that have been copied to C:\inout on the HealthOne server to the Transaction Central Database
-- and marks them as Processed (sets 0 to 1 - or True) in the TC Database, TransLog_Status table.


Declare @Logname char(50),
        @Errnum int,
        @Countloop int,
	@spid as varchar(10),
	@CMD as varchar(1000);


-- The following Cursor kills all connections on the Transaction Central database.

declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid = 
        (select dbid from sysdatabases where name = 'TransactionCentral')
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
    set @CMD = 'kill ' + @spid
    exec (@CMD)
    fetch next from cur into @spid
END
close cur
deallocate cur

-- The following one line command changes the database to Single User so the restore can complete properly.

Exec sp_dboption @dbname = 'TransactionCentral', @optname = 'Single user', @optvalue = 'True';

Set @Errnum = 0;
Set @countloop = (Select count(logname) from tc.dbo.translog_status where processed = 0);

While @countloop <> 0
Begin
  Set @logname = (Select min(logname) from tc.dbo.translog_status where processed = 0);

  

  Restore Log TransactionCentral
  from disk=@logname
  with Standby = 'd:\inout\undos\undo.dat';

  Set @errnum = @@Error;
 
  

  If @errnum = 0 
  Begin Update tc.dbo.Translog_status
         Set processed = 1
         where Logname = @logname
  End 
  Else
        Break;

Set @countloop = @countloop - 1;

END;

-- The following one line command sets the database back to Multi-User mode so people can reconnect and do their work.

Exec sp_dboption @dbname = 'TransactionCentral', @optname = 'single user', @optvalue = 'False';

-- This code records an error number in the TC Database, TransLog_Status table for the Transaction Log backup where the 
-- restore failed.  This error code can be used in trouble shooting.

If @errnum <> 0
Begin 
  Use MSDB;
  Exec sp_update_job @job_name = Restore_TC, @enabled = 0 ;
  Use TC;
  Update tc.dbo.Translog_status
  Set Errnum = @errnum
  Where Logname = @logname
END;

Now, if you can do the VB code that puts the backup names with path files in the table with a processed of 0 (not restored) and schedule that code as a Windows scheduled task, you'll be set.

The only problem I've run into so far is that I keep losing my Undo.Dat file for some unknown reason, which fails the restore without an error message.

This code can be modified to restore full backups with full recovery fairly easily too.

Does it help?



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"