SQL Server Information – backups not compliant with the backup naming convention

Goal

To obtain a list of backups that have a name different than the backup naming convention and the space occupied by these backups

My way of achieving it

Using tsql, xp_cmdshell and xp_fileexists. From time to time, at least in my case, I am doing backups as part of a ticket or a request and I am naming them in 100% of the cases something that is different than what the scheduled backups uses for those backups. In time I realized the my footprint is quite big and if among the team there are other colleagues doing the same then the footprint is even bigger. Because of this I created this script that will tell us which backups looks like NOT being compliant or backups that are not taken by the scheduled jobs or using the right naming convention and provides us information about those files like:

  • path to the backup
  • if the file is still on disk
  • size of the file
  • delete commands

The assumptions that I made were that usually we are taking full backups, differential backups, tlog backups and on some servers we might have log shipping configured. Because of these we might have 4 naming conventions for our backups or more. The script starts by creating a table containing the naming conventions discovered based on the backup name length, number of numbers in the backup name and the number of occurrences. Based on this table and based on the fact that if occurrences is smaller than 10 is deciding if the backup is compliant or not.

By default the script is dealing with backup history for the last 14 days. If you want to go back in time more than 14 days then you must modify the line below

SET @days = 14 — enter here the number of days for which you want to search among backup history

in the script.

Please be aware that this setting together with how often you take the backups increases the execution time.

The output of the script is bellow

backupsnotcompliant1

The output of the script from a server where we have some backups jobs configured looks like below

backupsnotcompliant2

 

The only thing that I want to mention here is that this script is providing information only and the decision of doing something with the output is entirely something that you must decide and is very dependent of the each infrastructure environment. All the results set were left in the output because by seeing the output you can easily confirm that indeed those type of backups are for sure not compliant with the backup policy from your environment.

The script can be downloaded from here

Leave a Reply

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