Shawn Melton

Analyzing SQL Server backups

March 8, 2016 by

Introduction

Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.

The backup of a database can tell you everything from the compatibility level of the database to the date the database was created. You can find out the physical file names, size, and even the disk block size where the database was stored on the source server. The size is the information I am most interested in with consolidation projects. I can utilize this to analyze how much storage space I will need for the new server, and work out how I am going to need that storage carved up.

After I get all that information together I pull it into Excel and then utilize pivot tables to calculate out the storage totals for all the databases, and can also create tables on drive letter or file type. I found PowerShell to be the best method, for me, to pull all the information out of the backup files into a format I could bring into Excel. I did actually consider writing PowerShell that would put it directly into Excel for me but that is not a strong area I use often so decided against it for right now. In this article I want to share the tool I created for this in PowerShell. I will then go through how I build the Pivot tables in Excel.

The Script

Let me introduce you to, “GetBackupInformation.ps1”. This script will look like it does a good bit, but is pretty basic. I have included help information along with comments in the script itself to help with two things: (1) You learn how to use the script and (2) you possibly learn some new things with PowerShell. A few points about this script:

  • Minimum required is PowerShell 3.0 or higher.
  • You will need access to at least one SQL Server instance, but it can be Express Edition.
  • The data is output to the console or a parameter is provided to spit it to a CSV file. **
  • The Connection String and Delimiter parameters I set to default vales, so you can change those to your environment or pass them each time.

** I added the ability for this script to output to the console in the event you may want to send this data to another source altogether (Power BI, database, etc.). It is up to you once you have the output to take it and do what you want.

The background of this script came from having to use this process a few times, I decided to finally sit down and make it more robust. It could have been made complex by using SMO to read the backup files but I like shortcuts. I settled on just using T-SQL RESTORE command to read the backup file information, this is the reason a SQL Server instance is required. The script will simply execute “RESTORE FILELISTONLY” and “RESTORE HEADERONLY” against each backup file path that is passed to the script, which even on large backups these commands should only take a few seconds to execute, (should). The script will handle reading a single backup, multiple backup files, or a single backup file with multiple backups (backup set).

Example Data

The below screen shot illustrates how the script can be used and provides a sample of what backup information I am pulling:

The last command I am utilizing the CSV parameter, and will use that file to import into Excel.

One note, if you notice the “LocalDrive” column is empty. I went ahead and just added this column, but do not currently populate it until I bring the data into Excel. If you have a standard drive letter mapping for data and log drives, you could add some logic to the script to have it populate this column if you wish.

Building the Pivot

I start out just bringing the CSV file into Excel and doing a bit of formatting. I also filled in the “LocalDrive” columns with some drive letters for reference on drive size.

  1. Now you go into the Insert ribbon and click on “PivotTable”:

  2. Click OK

  3. A new worksheet is going to be created and you will be presented with something similar to the below screenshot.

  4. You can now just click on the check box for the data you want to include, and Excel will take a guess where you want it to go (Rows, Columns, etc.). I however tend to just drag and drop where I want it to go.

  5. So just drag the following rows to the noted areas:

    1. Rows: DatabaseName
    2. Columns: LocalDrive
    3. Values: sizeMB (or sizeGB if you wish)

  6. In the end it should look something like this:

Now I want to add another PivotTable to this same spreadsheet, this new table will show me the size of each database based on “Type” column. You can repeat the above steps and the only change is when you get to the step 2, before you click on OK perform the following step:

  1. Select “Existing Worksheet”

  2. Now click on the location selector

  3. This is just going to point Excel where you want the new table created.

    1. Click on “Sheet3”
    2. Click on cell “A20”
    3. Click on location selector to back to the previous screen

  4. Click OK.
  5. To create the next table, you follow same as we did above with the exception that “Columns” you would want to use “Type” instead of “LocalDrive” as we did previously.
  6. This should leave you with a table similar to below:

Summary

I hope this script will provide you with some insightful information with any consolidation project you may be working on, or even just in your day-to-day work as a DBA. I have found PivotTables in Excel can help make some tasks as a DBA very quick and easy. If you have not noticed this can also be a good tool for visually showing the numbers in a manner upper management can understand why that purchase request is being submitted for more storage.

References


Shawn Melton
Backup and restore

About Shawn Melton

Shawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada. After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server. He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS). View all posts by Shawn Melton

168 Views