Select Page

Where are your backups?

JP Chen | | June 1, 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:

-- Removing the symbols and space from the default datetime

SELECT GETDATE() [Current DateTime], REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') [Current DateTime Removing the Symbols] --YYYYMMDDHHMMSS

Script: DateTimeMinusTheSpaceAndSymbols.sql

Output:

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:

-- Display 120 seconds, 125.25 seconds, and 3600 seconds in the format of HH:MM:SS

SELECT CONVERT(varchar, DATEADD(ms, 120 * 1000, 0), 108) [2 minutes]

, CONVERT(varchar, DATEADD(ms, 125.25 * 1000, 0), 114) [2 minutes 5 seconds and 25 milliseconds]

, CONVERT(varchar, DATEADD(ms, 3600 * 1000, 0), 108) [1 hour]

Script: HHMMSS.sql

Output:

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:

-- Run the full backups for the selected databases with date and timestamp appended to the backup files
-- ****************************************************************************************************
-- Copyright © 2016 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties. 
-- ****************************************************************************************************
DECLARE DBFullBackups_Cursor Cursor
FOR
-- Pick the databases you wish to run full backups
SELECT db.name
FROM sys.databases db
WHERE db.name in ('AdventureWorks2012', 'AdventureWorksDW2012', 'Venti', 'Grande', 'Tall', 'TEST DB') -- Added [TEST DB] with space in the db name for testing purposes

OPEN DBFullBackups_Cursor
DECLARE @db varchar(125);
DECLARE @BackupPath varchar(225);
DECLARE @BackupCmd varchar(525);

-- Check to make sure the path exists
SET @BackupPath = 'E:\SQLBackup\' -- specify your own backup folder

FETCH NEXT FROM DBFullBackups_Cursor INTO @db 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	-- Backup command
	SET @BackupCmd = 'BACKUP DATABASE [' +@db+ '] TO DISK = ''' +@BackupPath+@db+ '_' 
	+REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') --YYYYMMDDHHMMSS
	+'.bak'''

	-- Print the backup command
	PRINT @BackupCmd

	-- Run the backup command
	EXEC (@BackupCmd)

	-- Fetch the next database
	FETCH NEXT FROM DBFullBackups_Cursor INTO @db
END

-- Close and deallocate the cursor 
CLOSE DBFullBackups_Cursor
DEALLOCATE DBFullBackups_Cursor
GO

Script: FullBackups.sql

Screen-shot of the backup files:

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:

-- Where are your backups?

-- ****************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ****************************************************************************

SELECT SERVERPROPERTY('SERVERNAME') as InstanceName

, bs.database_name as DatabaseName

, bs.recovery_model

, CASE bs.type

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Log'

WHEN 'F' THEN 'File or Filegroup'

END as BackupType

, bmf.physical_device_name as BackupPath

, bs.backup_start_date as BackupStartDate

, bs.backup_finish_date as BackupFinishDate

, CONVERT(varchar, DATEADD(ms, DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) * 1000,0), 108) [Backup Duration HH:MM:SS] -- use 114 instead of 108 if you wish to see milliseconds

, CONVERT(NUMERIC (20,2), bs.backup_size/1024/1024) [Backup Size MB]

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE bs.backup_finish_date >= GETDATE() - 3 -- backups in the past 3 days

Script: WhereAreYourBackups.sql

Output:

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.
  4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS