November 4, 2015 at 10:07 am
I am trying to automate testing backups. I've seen a lot of very complicated scripts on the web but they either go overboard or don't meet our business requirements. My question is; Is there a return code or something that I can use to create a log?
This works well for me, except that it will write a 1 whether the backup file succeeds or fails.
Here is the code I have so far:
/*
Created to automate testing backups so that they can run during the night.
Dependencies:
Backup files should reside in @BackupLocation. If they are relocated, update row 12.
Table = [DBATools].[dbo].[BackupTestFileList]; Keeps a list of the files to check for the cursor. Truncated after each run.
Table = [DBATools].[dbo].[BackupTestResults]; An auditable history of the test results. Used to generate emails
alerts when required.
Stored Procedure = Undocumented system stored procedure [master]..[xp_dirtree]
Created by TClover. 11/04/2015
*/
DECLARE @BackupLocation VARCHAR(250)
SET @BackupLocation = 'N:\DB_Backup\'
--Step 1: Fill the file list with the backup names.
INSERT INTO [DBATools].[dbo].[BackupTestFileList]
EXEC xp_dirtree 'N:\DB_Backup\',1,1--1st 1 limits the tree depth to the current directory. 2nd 1 limits the list to files only.
--Step 2: Update the file list with the full path so that the restore can find them.
UPDATE [DBATools].[dbo].[BackupTestFileList]
SET [FileName] = 'N:\DB_Backup\' + [FileName]
--For testing only! Don't forget to delete this part. Limits the result set to a few small backup files.
DELETE FROM [DBATools].[dbo].[BackupTestFileList]
WHERE [FileName] NOT LIKE 'N:\DB_Backup\F%'
--Step 3: Cycle through the files listed in [BackupTestFileList] and put the results in [BackupTestResults].
DECLARE @QRY1 VARCHAR(8000)
DECLARE @CURSOR_DB1 VARCHAR(8000)
DECLARE CSR1 CURSOR STATIC FOR SELECT [FileName] FROM [DBATools].[dbo].[BackupTestFileList]
OPEN CSR1
FETCH NEXT FROM CSR1 INTO @CURSOR_DB1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QRY1 = 'RESTORE VERIFYONLY FROM DISK = '''+@CURSOR_DB1+''''
EXEC (@QRY1)
--I need a return code or something to control whether a 0 or a 1 is written below.
SET @QRY1 = 'INSERT INTO [DBATools].[dbo].[BackupTestResults] VALUES ('''+@CURSOR_DB1+''',''1'',GETDATE())'
EXEC (@QRY1)
FETCH NEXT FROM CSR1 INTO @CURSOR_DB1
END
CLOSE CSR1
DEALLOCATE CSR1
--Step 4: Clean up after ourselves
TRUNCATE TABLE [DBATools].[dbo].[BackupTestFileList] --No reason to waste backup space on something that changes every run.
November 5, 2015 at 11:05 am
I was hoping to discover something more elegant but I ran out of time so I went with the reliable BEGIN TRY/BEGIN CATCH method that I've always used. Thank you for your time.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply