Mapping databases to their backup files that are still on disk

Goal

To obtain a list of backups for the databases that are on our instance whcih are still on disk and to count them.

My way of achieving it

Using tsql and xp_cmdshell. From time to time we end up having free space issues even after the backup cleanup job that we have in place executed successfully. This situation might happen because of how our backup cleanup logic works. Most of the backup cleanup logic is using dates to delete files and not numbers of the backups or a combination of these two. Because of this I created the below script in order to know how many backup files are still on disk for each of my databases. If we know that we should keep only 2 full backups and we have 3 on disk then we can delete one. The same applies for differential backups. The script works well for the environment for which I created it but with some small modifications I believe it will suit other environments need.  The script is expecting the backup file name in this form

I:\sqldata\backup\diff\SQLServer01\03\mysite_regional_02_p_20160903_042243_ab_diff.bak

  • the path contains the database name
  • the path has a timestamp in the form above
  • the path contains the type of the backup

By default the script will output

  • the database name and how many backups on disk exists for that database
  • the database names and their path and their backup date for all the databases that have more than 2 backups on disk.
  • the delete commands for a backup file that was taken for a database that is not anymore on the instance
  • some information in case the backup path contains also a folder named ‘restore’

The script provides only information and decision to delete something is in the hands of the person that interprets the results and knows how many full backups we should have, how many differentials and so on.

Below you can see the output of the script fir diff.bak and a local path

mappingdbtobakfiles

 

The script can be downloaded from here

 

Leave a Reply

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