Every DBA uses backup file as the basis to recover a corrupted SQL Server database. SQL Server’s maintenance plan helps you to create Backup plans. You can then schedule those maintenance plans as jobs though SQL Server agent. However you will not receive any alert if these backup files get corrupted or moved or deleted from the backup path. This article will help you to identify if all databases in your SQL environment has a backup file that can be used during a disaster recovery.
Start with a function
Execute the script from a new query window. We are creating a function to check if the backup file exists at the backup path. The function will return 1 if the backup file is available and returns 0 if the backup file is not available. We can then create a temp table to hold the backup date, age of backup file, full path of backup file and a column to hold 1 or 0.
On this temp table, you have to focus on the “cexists” column. If the backup file is missing for a database, fix it immediately by taking a FULL backup. Following might be reasons for the column “cexists” to show ‘0’ for a database
- Backup file was deleted from the backup path
- Backup file was renamed and the script was not able to identify it
- The maintenance plan was set to backup only selected user databases instead of all user databases.
The next column to be focussed is the age of backup file. A full backup file that is one year old will never help us. The best method is to compare this column with your company’s backup policy. In most of companies, FULL back up happens for every week. So any FULL backup file that is older than 7 days is a non-compliant.
Based on these 2 columns you can send an alert email to the DBA team. You can directly attach the result as a file or you can create a HTML alert email.
You can use restore verifyonly to check if a backup file can be used for a database restoration activity. However, if you try the restore verifyonly on your corrupted backup files you might find that the result is not 100% certain. As a best practice, you can set a bimonthly audit and do an actual restore with backup files picked at random. During this audit, please ensure that you are doing the restoration on a test server and certainly not on a production server.
CREATE FUNCTION dbo.chkfilefn (@bckpath VARCHAR(2000)) RETURNS BIT AS BEGIN DECLARE @bit INT EXEC master.dbo.xp_fileexist @bckpath ,@bit OUTPUT RETURN cast(@bit AS BIT) END; GO CREATE TABLE #temp1 ( cdbname VARCHAR(200) ,cdatetime DATETIME ,cold INT ,cloc VARCHAR(1000) ,cexists INT ) GO WITH CTETBL1 AS ( SELECT BCKTBL3.database_name ,BCKTBL3.backup_size ,BCKTBL3.backup_start_date ,BCKTBL1.physical_device_name ,Position = ROW_NUMBER() OVER ( PARTITION BY BCKTBL3.database_name ORDER BY BCKTBL3.backup_start_date DESC ) FROM msdb.dbo.backupmediafamily BCKTBL1 JOIN msdb.dbo.backupmediaset BCKTBL2 ON BCKTBL1.media_set_id = BCKTBL2.media_set_id JOIN msdb.dbo.backupset BCKTBL3 ON BCKTBL2.media_set_id = BCKTBL3.media_set_id WHERE BCKTBL3.[type] = 'D' AND BCKTBL3.is_copy_only = 0 ) INSERT INTO #temp1 ( cdbname ,cdatetime ,cloc ) SELECT BCKTBL4.NAME AS [Database] ,backup_start_date AS [Last Full DB Backup Date] ,physical_device_name AS [Backup File Location] FROM sys.databases AS BCKTBL4 LEFT JOIN CTETBL1 AS BCKTBL5 ON BCKTBL4.NAME = BCKTBL5.database_name AND Position = 1 ORDER BY [Database]; UPDATE #temp1 SET cold = datediff(d, cdatetime, getdate()) UPDATE #temp1 SET cexists = master.dbo.chkfilefn(cloc) SELECT * FROM #temp1 DROP TABLE #temp1 GO
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst data problem and excel recovery software products. For more information visit www.datanumen.com