Oct 2010 TOM: Are your backups ready for a disaster?

By SQLRx Admin | Helpful Scripts

Oct 07

Database backups are a critical part of SQL Server disaster planning. It’s also critical to know that your backups are viable. Read the following tip and be assured you’re ready should a disaster hit. And don’t forget to regularly monitor the 11 VitalSigns  by SQLRx to debug performance bottlenecks.

SQL Server Administration: The best way to verify backups is to actually restore backup files, but many times this task falls by the wayside due to lack of space or time. In lieu of restoring backup files, you can use RESTORE VERIFYONLY to confirm that a backup set is complete and readable. Here is a routine that can be used to verify the most recent backup file for each database in a SQL instance. This can be used in a job to routinely verify that backup files are intact and should be able to be restored in the event of a disaster.
___________________________________

SET NOCOUNT ON
DECLARE @dbname VARCHAR(128)
DECLARE @finishdate DATETIME
DECLARE @diskbackup NVARCHAR(260)
DECLARE @verifystatement NVARCHAR(360)
DECLARE @timebegin DATETIME
DECLARE @timeend DATETIME
DECLARE @timeelapsed INT
DECLARE @FileFound INT

DECLARE csrDatabases CURSOR FOR
SELECT database_name, MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE type = ‘D’
GROUP BY database_name

OPEN csrDatabases

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @diskbackup = bmf.physical_device_name
FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupset bs
ON (bmf.media_set_id = bs.media_set_id)
WHERE bs.type = ‘D’
AND bs.database_name = @dbname
AND bs.backup_finish_date = @finishdate

EXEC master.dbo.xp_fileexist @diskbackup, @FileFound OUTPUT
IF @FileFound=1
BEGIN
SET @verifystatement = ‘RESTORE VERIFYONLY FROM DISK = ”’+@diskbackup+”’ WITH LOADHISTORY’
SELECT @timebegin = GETDATE()
EXEC sp_executesql @verifystatement
SELECT @timeend = GETDATE()
SELECT @timeelapsed = DATEDIFF(s,@timebegin,@timeend);

PRINT ‘Backup file ‘+@diskbackup+’ verified in ‘+CAST(@timeelapsed AS VARCHAR(20))+’ seconds.’

END

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate
END

CLOSE csrDatabases
DEALLOCATE csrDatabases
SET NOCOUNT OFF

About the Author

>