Script to check that backup files still exist for SQL Server

Problem

You have configured your database backup jobs without any problems. The backups themselves seem to be working, but are the files still where you think they are? How do you know they have not been moved, or worse, deleted to free up some space on your server? And when would you find out that the files were no longer there? If you are looking for the file to do a restore, and cannot find it, then it is too late.  In this tip, I will show you a simple way to check through the backup history to find the last full backup for each database and also to check that the file still exists where it is supposed to exist.

Solution

The database backup file information is readily available inside the msdb database, making the solution as easy as a few lines of T-SQL. The code below will work for SQL2005/8 by going into the msdb database, extracting the file name, and using the undocumented system stored procedure xp_fileexist. Yes, I said undocumented, which means you need to use at your own risk as Microsoft does not support the undocumented stored procedures and they are subject to change.  This system stored procedure is used in a lot of places, so don’t be frightened by its undocumented nature.

The code will return the backup file details for each active database on the instance at the moment the code is executed. It will read some tables in the msdb database and return the filename for the last full backup of each database. It will then use xp_fileexist to determine if the file still exists where it was originally created. If it does not, then it will print out a brief error message that includes the name of the missing file and the database.

Returning the details


<span style="COLOR: blue">SET </span><span style="COLOR: black">NOCOUNT </span><span style="COLOR: blue">ON
<br>DECLARE </span><span style="COLOR: #434343">@FileName </span><span style="COLOR: blue">VARCHAR</span><span style="COLOR: gray">(</span><span style="COLOR: black">255</span><span style="COLOR: gray">)
<br></span><span style="COLOR: blue">DECLARE </span><span style="COLOR: #434343">@File_Exists </span><span style="COLOR: blue">INT
<br>DECLARE </span><span style="COLOR: #434343">@DBname </span><span style="COLOR: black">sysname
<br><br></span><span style="COLOR: green">--get list of files to check
<br></span><span style="COLOR: blue">DECLARE </span><span style="COLOR: black">FileNameCsr </span><span style="COLOR: blue">CURSOR
<br></span><span style="COLOR: black">READ_ONLY <br></span>
<span style="COLOR: blue">FOR  <br>   SELECT </span><span style="COLOR: black">physical_device_name</span><span style="COLOR: gray">, </span><span style="COLOR: black">sd.name
<br>   </span><span style="COLOR: blue">FROM </span><span style="COLOR: black">msdb..backupmediafamily bmf
<br>   </span><span style="COLOR: blue">INNER JOIN </span><span style="COLOR: black">msdb..backupset bms </span><span style="COLOR: blue">ON </span><span style="COLOR: black">bmf.media_set_id </span><span style="COLOR: blue">= </span><span style="COLOR: black">bms.media_set_id
<br>   </span><span style="COLOR: blue">INNER JOIN </span><span style="COLOR: black">master..sysdatabases sd </span><span style="COLOR: blue">ON </span><span style="COLOR: black">bms.database_name </span><span style="COLOR: blue">= </span><span style="COLOR: black">sd.name
<br>   </span><span style="COLOR: gray">AND </span><span style="COLOR: black">bms.backup_start_date </span><span style="COLOR: blue">= </span><span style="COLOR: gray">(</span><span style="COLOR: blue">SELECT MAX</span><span style="COLOR: gray">(</span><span style="COLOR: black">backup_start_date</span><span style="COLOR: gray">) </span><span style="COLOR: blue">FROM </span><span style="COLOR: black">[msdb]..[backupset] b2
<br>                                   </span><span style="COLOR: blue">WHERE </span><span style="COLOR: black">bms.database_name </span><span style="COLOR: blue">= </span><span style="COLOR: black">b2.database_name </span><span style="COLOR: gray">AND </span><span style="COLOR: black">b2.type </span><span style="COLOR: blue">= </span><span style="COLOR: red">'D'</span><span style="COLOR: gray">)
<br>   </span><span style="COLOR: blue">WHERE </span><span style="COLOR: black">sd.name </span><span style="COLOR: gray">NOT </span><span style="COLOR: blue">IN </span><span style="COLOR: gray">(</span><span style="COLOR: red">'Pubs'</span><span style="COLOR: gray">,</span><span style="COLOR: red">'tempdb'</span><span style="COLOR: gray">,</span><span style="COLOR: red">'Northwind'</span><span style="COLOR: gray">, </span><span style="COLOR: red">'Adventureworks'</span><span style="COLOR: gray">)
<br><br></span><span style="COLOR: blue">BEGIN </span><span style="COLOR: black">TRY
<br>   </span><span style="COLOR: blue">OPEN </span><span style="COLOR: black">FileNameCsr
<br><br>   </span><span style="COLOR: blue">FETCH </span><span style="COLOR: black">NEXT </span><span style="COLOR: blue">FROM </span><span style="COLOR: black">FileNameCsr </span><span style="COLOR: blue">INTO </span><span style="COLOR: #434343">@FileName</span><span style="COLOR: gray">, </span><span style="COLOR: #434343">@DBname
<br>   </span><span style="COLOR: blue">WHILE </span><span style="COLOR: gray">(</span><span style="COLOR: #434343">@@fetch_status </span><span style="COLOR: gray"><> -</span><span style="COLOR: black">1</span><span style="COLOR: gray">)
<br>   </span><span style="COLOR: blue">BEGIN <br>       IF </span><span style="COLOR: gray">(</span><span style="COLOR: #434343">@@fetch_status </span><span style="COLOR: gray"><> -</span><span style="COLOR: black">2</span><span style="COLOR: gray">)
<br>       </span><span style="COLOR: blue">BEGIN
<br>           EXEC </span><span style="COLOR: black">Master.dbo.</span><span style="COLOR: darkred">xp_fileexist </span><span style="COLOR: #434343">@FileName</span><span style="COLOR: gray">, </span><span style="COLOR: #434343">@File_Exists </span><span style="COLOR: black">OUT
<br>        <br>           </span><span style="COLOR: green">--if the file is not found, print out a message
<br>           </span><span style="COLOR: blue">IF </span><span style="COLOR: #434343">@File_Exists </span><span style="COLOR: blue">= </span><span style="COLOR: black">0 </span><span style="COLOR: green">--0 means file is not found, 1 means it is found
<br>               </span><span style="COLOR: blue">PRINT </span><span style="COLOR: red">'File Not Found: ' </span><span style="COLOR: gray">+ </span><span style="COLOR: #434343">@FileName </span><span style="COLOR: gray">+ </span><span style="COLOR: red">' -- for database: ' </span><span style="COLOR: gray">+ </span><span style="COLOR: #434343">@DBName
<br>       </span><span style="COLOR: blue">END
<br>    <br>   FETCH </span><span style="COLOR: black">NEXT </span><span style="COLOR: blue">FROM </span><span style="COLOR: black">FileNameCsr </span><span style="COLOR: blue">INTO </span><span style="COLOR: #434343">@FileName</span><span style="COLOR: gray">, </span><span style="COLOR: #434343">@DBName
<br>   </span><span style="COLOR: blue">END <br>   
<br>END </span><span style="COLOR: black">TRY <br><br></span>
<span style="COLOR: blue">BEGIN </span><span style="COLOR: black">CATCH
<br>    </span><span style="COLOR: blue">SELECT
<br>        </span><span style="COLOR: black">ERROR_NUMBER</span><span style="COLOR: gray">() </span><span style="COLOR: blue">AS </span><span style="COLOR: black">ErrorNumber
<br>        </span><span style="COLOR: gray">,</span><span style="COLOR: black">ERROR_SEVERITY</span><span style="COLOR: gray">() </span><span style="COLOR: blue">AS </span><span style="COLOR: black">ErrorSeverity
<br>        </span><span style="COLOR: gray">,</span><span style="COLOR: black">ERROR_STATE</span><span style="COLOR: gray">() </span><span style="COLOR: blue">AS </span><span style="COLOR: black">ErrorState
<br>        </span><span style="COLOR: gray">,</span><span style="COLOR: black">ERROR_PROCEDURE</span><span style="COLOR: gray">() </span><span style="COLOR: blue">AS </span><span style="COLOR: black">ErrorProcedure
<br>        </span><span style="COLOR: gray">,</span><span style="COLOR: black">ERROR_LINE</span><span style="COLOR: gray">() </span><span style="COLOR: blue">AS </span><span style="COLOR: black">ErrorLine
<br>        </span><span style="COLOR: gray">,</span><span style="COLOR: black">ERROR_MESSAGE</span><span style="COLOR: gray">() </span><span style="COLOR: blue">AS </span><span style="COLOR: black">ErrorMessage</span><span style="COLOR: gray">;
<br></span><span style="COLOR: blue">END </span><span style="COLOR: black">CATCH
<br><br><br></span><span style="COLOR: blue">CLOSE </span><span style="COLOR: black">FileNameCsr
<br></span><span style="COLOR: blue">DEALLOCATE </span><span style="COLOR: black">FileNameCsr
<br>GO <br><br></span>

The script will not return a result set, it will only return a brief message regarding any files not found.

Here is a screenshot of a sample result set returned by the script.

xp_fileexist screen shot

 

Next Steps

  • Take the above code and execute against your instance. You could also put this into a SQL Agent job and automate the task to alert you of missing files.
  • The code as written only looks at missing full backups.  You can extend this script to look for all backup types to make sure that all files for the entire backup set still exist.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *