By: Kenneth Igiri | Comments | Related: > 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:
- Take a full backup
- Introduce some data
- Take a differential backup
- Introduce some more data
- Take a log backup
- Take another full backup
- Introduce some more data
- 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:
- The backup sets are written to the same media set and to the same set of backup devices
- 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
However, it is impossible to get this information on another instance where the Practice2017 database backups did not occur using the query above.
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."
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.
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.
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).
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.
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.
- Make it your practice to examine your backups before attempting an actual restore. This could save time especially if you are in unfamiliar terrain.
- You can read more about the concepts covered in this article as follows:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
