Where are your backups?

By | In SQL Server | June 01st, 2016

As part of the SQL Server migration project planning, you need to find backup history to locate backup files, backup start date, backup finish date, duration required, and the backup size in MB. Let’s say that the Project Manager has requested you to present the backup duration in the format of HH:MM:SS so that she can make more close estimates on the time required for the migration. Unfortunately, SQL Server does not have built-in data columns to show you the backup duration in HH:MM:SS. In this blog, we will explore the required system tables, functions, and the techniques to create the backup history report requested.

We can find the backup details for each of the databases by querying the msdb.dbo.backupset and the msdb.dbo.backupmediafamily system tables. Here are the explanations of the system tables, functions, conversions, and case expressions that you need to know to get started:

  • DBO.BACKUPSET: This system table stores the information about the backup operations. It contains critical information such as the backup start date, backup end date, backup size, backup type, and other important backup details.
  • DBO.BACKUPMEDIAFAMILY: Contains one row for each media family. One of the most important data columns in this system table is the physical_device_name data column. It identifies the physical path of the backup.
  • CONVERT(): Converts an expression of one data type to another. Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
  • GETDATE(): Returns the current date and time on which the instance of SQL Server is running.
  • DATEADD(): Returns a specified date with the specified number interval added to a specified datepart of that date. Syntax: DATEADD (datepart , number , date )
  • DATEDIFF(): Returns the specified datepart boundaries between the start time and end time. Syntax: DATEDIFF ( datepart , startdate , enddate )
  • CASE EXPRESSION:Evaluates a list of conditions and returns one of the possible result expressions. In this blog, we will use the SIMPLE CASE expression.
  • REPLACE(): Replaces all occurrences of a specified string value with the replacement. Syntax: REPLACE ( string_expression , string_pattern , string_replacement )

Have you ever wondered how you can append the datetime stamp to the backup files without the “-“, “,“, “:”, “.”, and space? With the help of the built-in GETDATE(), REPLACE(), and CONVERT() system functions, you can. Here’s an example of how you can format the GETDATE() output to remove the space and exclude the special characters:

Script: DateTimeMinusTheSpaceAndSymbols.sql


Where are your Backups? Ft. 1

SQL Server does not have the built-in data column to show you the duration in the format of HH:MM:SS, but it does provide you the CONVERT() and DATEADD() system functions to get what you need. Here’s the script with examples to retrieve the duration in the desired format:

Script: HHMMSS.sql


Where are your Backups? Ft. 2

After reviewing and exploring the above code snippets that you can use to append the datetime stamp to the backup files and formatting the duration in HH:MM: SS, let’s now look into the scripts to run the full backups and check the backup history details.

Execute the following script to run the full backups. Modify it to select the databases you wish to backup and also to specify the backup folder within which you wish to store them:

Script: FullBackups.sql

Screen-shot of the backup files:

Where are your Backups? Ft. 3

After the backups are completed, you can run the following script to query the backup details. You can modify it to specify the past days to query for the backup history and also the databases returned. All the required data columns such as the recovery model of the databases, backup type, backup path, backup start date, backup finish date, backup duration in HH:MM:SS, and backup size in MB will also be included:

Script: WhereAreYourBackups.sql


Where are your Backups? Ft. 4

As part of the database migration steps, the next step will usually require you to generate the database restore scripts and verify the database restores. We will explore these topics in a future blog.

Datavail Script: Terms & Conditions

By using this script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

  1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as set forth herein.
  2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
  3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

  1. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.
Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

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