View Contents of SQL Server Backup Files with RESTORE LABELONLY, HEADERONLY and FILELISTONLY


By:   |   Updated: 2019-02-13   |   Comments   |   Related: More > Restore


Problem

Typically, when we take a backup on a SQL Server instance, we can examine details of the backup set from two key sources: the SQL Server Error Log and certain system tables in the msdb database. However, when we do not have access to the SQL Server instance where the backup was taken, we have to resort to key tools that Microsoft provides in order to investigate the backup and be able to use it correctly.

Solution

The solution to this problem involved the use of key arguments in the SQL Server RESTORE command as detailed in the article. The arguments are RESTORE LABELONLY, RESTORE HEADERONLY and RESTORE FILELISTONLY. We shall examine them in more detail after creating the scenario.

Definitions

To better understand this article, the following definition of terms will help:

  • Backup Set – The contents of a successful backup operation
  • Media Set – A set of backup media that contains one or more backup devices
  • Backup Device – A disk or tape device to which the database engine performs a backup operation
  • Media Family – A backup created on a single non-mirrored device or a set of non-mirrored devices on a media set
  • Log Sequence Number (LSN) – An internal numbering sequence used to identify each operation within the transaction log

Scenario

We will create the desired scenario by performing a series of backup operations in this order:

  1. Take a full backup
  2. Introduce some data
  3. Take a differential backup
  4. Introduce some more data
  5. Take a log backup
  6. Take another full backup
  7. Introduce some more data
  8. Take another log backup

Below is the script that will do the above items.

--Listing 1: Perform Multiple Backup Operations to Same Backup Devices
 
-- Confirm the Current Instance
select @@servername as [SQL Server Instance];
select name as [Database Name] from sys.databases;
 
-- Initiate Full backup
backup database [Practice2017] to 
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with 
name='GolanHeights Full Backup',
medianame='GolanHeights Media',
retaindays=90,
stats=10;
  
-- Create a Table and Insert Data
use Practice2017
go

create table staff (ID int identity (1,1)
,name varchar(30)
,hire_date datetime)
go

insert into staff values ('Kenneth Igiri',getdate()-1)
go 10
 
-- Intiate a Differential Backup
backup database [Practice2017] to 
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with 
name='GolanHeights Differential Backup',
medianame='GolanHeights Media',
retaindays=30,
differential,
stats=10;
 
-- Insert More Records in the table
use Practice2017
go

insert into staff values ('Charles Green',getdate()-3)
go 100
 
-- Initiate a Log Backup
backup log [Practice2017] to 
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with 
name='GolanHeights Log Backup',
medianame='GolanHeights Media',
retaindays=7,
stats=10;
 
-- Introduce More Data in the Table
use Practice2017
go

insert into staff values ('Gen Lee',getdate()-3)
go 50
 
-- Initiate another Full backup
backup database [Practice2017] to 
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with 
name='GolanHeights Full Backup',
medianame='GolanHeights Media',
retaindays=90,
stats=10;
 
-- Insert More Data
use Practice2017
go

insert into staff values ('Hillary Onyema',getdate()-3)
go 80
 
-- Initiate Another Log Backup
backup log [Practice2017] to 
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with 
name='GolanHeights Backup',
medianame='GolanHeights Media',
retaindays=7,
stats=10;			

 From the SQL statements in Listing 1, we can deduce two key facts about the scenario:

  1. The backup sets are written to the same media set and to the same set of backup devices
  2. The backup sets are named, but some names are incorrect

SQL Server MSDB System Tables

If we want to examine the backups from database Practice2017 while on the instance where the backups were created, we can easily do that by querying the backupset and backupmediafamily system tables (see Listing 2 below). From the system tables we can tell the database that each backup set belongs to, the start and end times of the backup operations as well as the start and end Log Sequence Numbers (LSNs). We can also get the full path to the backup devices used.

--Listing 2: Examine Backup Sets from MSDB System Tables 
use msdb
go

select bus.database_name, bus.backup_start_date, bus.backup_finish_date, [bus].[type],
(((DATEPART(HH,bus.backup_finish_date))- (DATEPART(HH,bus.backup_start_date)))*3600) +
(((DATEPART(MI,bus.backup_finish_date)) - (DATEPART(MI,bus.backup_start_date)))*60) +
(((DATEPART(SS,bus.backup_finish_date)) - DATEPART(SS,bus.backup_start_date)))
[backup_time (secs)], bus.backup_size,
bmf.physical_device_name 
, bus.first_lsn
, bus.last_lsn
, bus.first_media_number
, bus.last_media_number
, bus.first_family_number
, bus.last_family_number
from backupset bus
join backupmediafamily bmf on bus.media_set_id=bmf.media_set_id
where database_name ='Practice2017' 
order by bus.backup_start_date desc			
Fig. 2 MSDB System Tables
Data Set Containing Backup Information for Practice2017 Database on the First Instance (EPG-KIGIRI\2017)

However, it is impossible to get this information on another instance where the Practice2017 database backups did not occur using the query above.

Fig. 3 Backup Information for Practice2017 Database Not Present on the Second Instance (EPG-KIGIRI\2019)

SQL Server Restore LabelOnly

SQL Server documentation tells us that Restore LabelOnly returns "…a result set containing information about the backup media identified by the given backup device."

Fig. 4 Using Restore LabelOnly

Observe from the image above that we can tell the Media Name that was used during the backup and if we examine only one media family, we will be able to tell that it is NOT the complete backup set (observe the FamilyCount and FamilySequenceNumber c columns in the result set). We also notice the SoftwareVendor column indicates if we were using a third-party vendor to perform the backup operation.

SQL Server Restore HeaderOnly

SQL Server documentation tells us that Restore HeaderOnly "…returns a result set containing all the backup header information for all backup sets on a particular backup device in SQL Server." In my estimation, Restore HeaderOnly and Restore FilelistOnly are the two most important statements a DBA should issue when preparing to restore a backup.

Fig. 5 using Restore HeaderOnly I

First, we observe the result sets works as advertised. We are able to see ALL Backup Sets written to this Media Set. We can also tell what kind of backup the backup set contains – Full Database Backup (1), Differential Backup (5), Transaction Log Backup (2) and so on (see above image).

The output also tells us who took the backup and the server details of the source instance – instance name, database name and version. Knowing the type of backup, we are dealing with will help us determine what order to perform recovery. Also notice that it shows the issue with the wrong names used in the backup operations we pointed out earlier. Knowing the version of the source instance helps us determine whether we can restore to the instance we want to restore to.

Another very important column to look at is the Position column. Because we have several backups written to one Media Set, when performing an actual restore, we must identify the backup set we are interested in using the FILE=# clause as shown in Listing 3 below.

Fig. 6 Using Restore HeaderOnly II
Figure 6

We are also able to see the LSN and timestamps of the backup operations. This will also helps us verify and align the restore sequence (see below images).

Fig. 7 Using Restore HeaderOnly III
Fig. 8 Using Restore HeaderOnly IV
Fig. 9 Using Restore HeaderOnly V

SQL Server Restore FilelistOnly

SQL Server documentation tells us that Restore FilelistOnly "… returns a result set containing a list of the database and log files contained in the backup set in SQL Server." This is particularly useful if we need to use the MOVE clause with the restore command or we need to replicate the drive structure of the source instance at the destination.

Fig. 10 Using Restore FilelistOnly

In addition, we can also plan for the size requirements of the respective physical files using the information in the Size column (size is in bytes). As an aside, observe that in the cases of Restore HeadOnly and Restore VerifyOnly, we do not need to query ALL backup devices, ANY of them will give us the information we need.

Performing the SQL Server Restore

The code in Listing 3 shows how we use this Media Set to recover the Practice2017 database on a different SQL Server instance. In the last section of the listing we point out that we cannot apply the last Log Backup in this scenario (File 7) since the full backup associated with this Transaction Log backup starts at LSN 35000000234400044 which belongs to a more recent full backup and is beyond the LSN of the log backup we just successfully applied (35000000228100001) (see Figure 6 above).

-- Listing 3: Performing Complete Recovery Using the Information Gathered
 
-- Performing a Restore from the Full Database Backup
-- Using file 2
restore database Practice2017 from
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with file=2,
move 'Practice2017' to 'D:\MSSQL\Data\Practice2017.mdf',
move 'Practice2017_log' to 'G:\MSSQL\Log\Practice2017_log.ldf',
norecovery,
stats=10
 
-- Performing a Restore from the Differential Database Backup
-- Using file 3 and NORECOVERY
restore database Practice2017 from
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with file=3,
move 'Practice2017' to 'D:\MSSQL\Data\Practice2017.mdf',
move 'Practice2017_log' to 'G:\MSSQL\Log\Practice2017_log.ldf',
norecovery,
stats=10
 
-- Performing a Restore from the Transaction Log Backup
-- Using file 5 and NORECOVERY
restore log Practice2017 from
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with file=5,
move 'Practice2017' to 'D:\MSSQL\Data\Practice2017.mdf',
move 'Practice2017_log' to 'G:\MSSQL\Log\Practice2017_log.ldf',
norecovery,
stats=10
 
-- Attempting a Restore from the Differential Database Backup
-- Using file 7 and NORECOVERY
-- This will with error "The log in this backup set begins at LSN 35000000228700001, which is too recent to apply to the database. An earlier log backup that includes LSN 35000000218100001 can be restored."
-- A full backup exists with FILE # 6
restore log Practice2017 from
disk='G:\Backup\Practice2017_Backup_01.bak',
disk='G:\Backup\Practice2017_Backup_02.bak',
disk='G:\Backup\Practice2017_Backup_03.bak',
disk='G:\Backup\Practice2017_Backup_04.bak'
with file=7,
move 'Practice2017' to 'D:\MSSQL\Data\Practice2017.mdf',
move 'Practice2017_log' to 'G:\MSSQL\Log\Practice2017_log.ldf',
norecovery,
stats=10
			
Next Steps

In this article, we touched on key arguments in the RESTORE command used to examine backups in a scenario where we need to use the backup in another instance of SQL Server. We have also highlighted ways in which such information will be useful to use in the field.



Last Updated: 2019-02-13


get scripts

next tip button



About the author
MSSQLTips author Kenneth Igiri Kenneth Igiri is a Database Administrator with eProcess International S.A. Ecobank’s technology services hub. He has over eight years of experience with SQL Server and Oracle.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Identify when a SQL Server database was restored, the source and backup date

How to migrate a SQL Server database to a lower version

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Make Network Path Visible For SQL Server Backup and Restore in SSMS

SQL Server Database Stuck in Restoring State








get free sql tips
agree to terms


Learn more about SQL Server tools