Like many of you, I make use of Ola Hallengren's Maintenance Solution (http://www.ola.hallengren.com) in my enterprise to ensure that my databases are properly backed up. The solution framework installed by Ola's script includes a stored procedure called DatabaseBackup. The backup files that DatabaseBackup produces are stored in a well-designed folder hierarchy.  

If you give DatabaseBackup a mapped network share (let's say it's drive Z:) as a target folder, it will create folders as needed, which match the following scheme:

Z:\<server-instance>\<database-name>\<backup-type>

where:

<server-instance> is the name of your SQL Server instance, converted to be a valid Windows file name

<database-name> is the name of a database on that server instance, and

<backup-type> is either DIFF, FULL, or LOG, denoting the type of backup file the folder contains.

In my small-ish enterprise (less than 20 SQL Servers), with large maintenance windows available, I'm backing up all my SQL Servers to a central network share location.  In this case, I want to make sure that I verify all my backups after completion.    

When I had this backup strategy deployed and working, I found myself wishing for a report that would give me the following information, for each server, database, and backup type:

• Number of backup files present
• The total disk space consumed by those files
• The date and age (in days) of the oldest and newest backup files

To accomplish this, I wrote a program which I've attached to this article.  It's written in the Python language, a free download from http://www.python.org. The program was developed using Python version 3.4, which was the current version available at the time I wrote it.

I launch the program from a command file. In my case, the single line of code in that file looks like:

C:\Python34\python.exe bulist.py    Z:\SQLBackups   report.csv

I saved this single-line command script in the same folder as my program (bulist.py).  The syntax above runs the python interpreter, and provides the name of my program (bulist.py).

The last two arguments are passed to my program.  "Z:\SQLBackups" specifies the folder used by my maintenance jobs as a backup target. "report.csv" is provided as the name of the report file I want to generate. Since I didn't specify a folder name for the output file, it assumes that I want to save the results in the folder Z:\SQLBackups.  You can use a fully qualified path for the output file to store in in another folder. Replace the last 2 values on this command line with a backup folder that matches your needs, and an output file name that works for you as well.

When the program runs, it logs its activity to the command window or output file like so:

The output file generated is always created in a comma-separated list format, with column headers.  This makes it easy for me to double-click the file and launch Microsoft Excel, which allows me to format the results in a variety of ways.

I run this program every Monday after my weekend backups complete, to make sure my backup and rotation strategy is working and that my backups are current.  SQL Alerts and Database Mail are essential tools for knowing when your jobs fail, but having this tool to compile a backup inventory is helpful for understanding your disaster recovery assets as well.  I hope you find it useful!