In the first part of this article we’ve discussed the backup strategy and the process of taking full and differential backup including log and tail backup. But for large database the backup strategy require more thinking to ensure:

• Backup space availability – if the database backup size is 1 TB or more, it’s usual that a single backup drive may not be able to provide this space. In this case we can split the backup files into several pieces.
• Recovery time minimization – to restore and recover a database of 1 TB it’ll take longer time and the operations needs to keep down during the entire recovery process. We need a way to restore the most essential stuffs and bring the operation back.

In this article, we will discuss both the ways. Firstly, let’s have a look at the Split Backup, where we can split the backup files in pieces for any full backup or differential backup. Let’s use the same database that we’ve created in the first part https://sultanealam.com/2024/06/08/sql-server-backup-strategy-part-1/

We’ll demonstrate split backup for a full backup, the same thing can be used for differential or log backup.

-- splitting the backup in separate files
USE MASTER
GO
BACKUP DATABASE [DemoBkup] TO
	DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Split1.BAK',
	DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Split2.BAK'
GO

-- restore from the splits following the same sequence
USE MASTER
GO
RESTORE DATABASE [DemoBkup] FROM
	DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Split1.BAK',
	DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Split2.BAK'
GO

Here, we are splitting the backup into two pieces. We can put this two pieces into two different OS path based on the availability of space. We can use the SSMS to do the same. Just keep on adding more files while taking the backup.

To restore, we need to add all the parts:

-- restore from the splits following the same sequence
USE MASTER
GO
RESTORE DATABASE [DemoBkup] FROM
	DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Split1.BAK',
	DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Split2.BAK'
GO

Now, let’s move to the point where we need to restore the database partially to start the business. Well, this require upfront planning while designing the database architecture. This can only be used when you have file groups with partitioned tables and filegroup backup is taken in full recovery model. Let’s assume for the same database we’ve a transaction table having customer transaction. If the table is partitioned yearly which means data for 2023 in a separate partitioned file group and 2024 in another, then if we can restore Primary and the 2024 file group we can start the operations. Later, we can restore the other file groups. This way, we can achieve the RTO (Recovery Time Objective) as discussed on the first part of this article.

Firstly, let’s add two filegroup for 2023 and 2024.

-- Creating File Group with Single Data File
USE DemoBkup
GO

ALTER DATABASE [DemoBkup]
ADD FILEGROUP [DemoHist_2023]
GO

ALTER DATABASE [DemoBkup]
ADD FILEGROUP [DemoHist_2024]
GO

ALTER DATABASE [DemoBkup]
ADD FILE 
( NAME = DemoHist_2023,
  FILENAME = 'C:\DemoBkup\DemoHist_2023.ndf',
  SIZE = 10240KB,
  MAXSIZE = UNLIMITED, 
  FILEGROWTH = 10%)
TO FILEGROUP [DemoHist_2023]
GO

ALTER DATABASE [DemoBkup]
ADD FILE 
( NAME = DemoHist_2024,
  FILENAME = 'C:\DemoBkup\DemoHist_2024.ndf',
  SIZE = 10240KB,
  MAXSIZE = UNLIMITED, 
  FILEGROWTH = 10%)
TO FILEGROUP [DemoHist_2024]
GO

You can find the new file group at your explorer.

Let’s define the partition function and scheme. Then create a table on this scheme.

-- partition function
USE DemoBkup
GO
CREATE PARTITION FUNCTION [DemoBkup_PartitionRangeFunc] (DATETIME)
AS RANGE LEFT FOR VALUES
	(	
	'2023-01-01',
	'2024-01-01'
	)
GO

-- partition scheme
USE DemoBkup
GO
CREATE PARTITION SCHEME [DemoBkup_PartitionScheme]
AS PARTITION [DemoBkup_PartitionRangeFunc]
TO ([Primary],[DemoHist_2023],[DemoHist_2024])
GO

-- Create table on Partition Scheme
CREATE TABLE CustomerTrans
(
	TransDt			DateTIME		NOT NULL,
	CustomerId		INT				NOT NULL,
	TransNo			INT				NOT NULL,
	TransAmount		NUMERIC(10,2)	NOT NULL,	
	CONSTRAINT PK_CustomerTrans PRIMARY KEY CLUSTERED ([TransDt],[CustomerId],[TransNo] ASC) ON [DemoBkup_PartitionScheme]([TransDt]),
	CONSTRAINT FK_CustomerId FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerId])
)ON [DemoBkup_PartitionScheme]([TransDt])
GO

This ensures that all data on or before 2023-01-01 will be inserted into file group Primary. Data after 2023-01-01 to 2024-01-01 will be inserted to DemoHist_2023. Data after 2024-01-01 will be inserted into file group DemoHist_2024. Let’s insert few rows so that each file group have some rows.

-- Primary
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (1,'2023-01-01',1,100)
GO

-- DemoHist_2023
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (2,'2023-01-02',1,200)
GO
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (3,'2023-12-31',1,300)
GO

-- DemoHist_2024
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (4,'2024-01-02',1,400)
GO
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (5,'2024-01-02',1,500)
GO
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (6,'2025-01-02',1,600)
GO

We can check how many rows are on each partition from sys.partitions.

-- all partionwise total row
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='CustomerTrans'
ORDER BY index_id,partition_number

The output will be 1 row in the first partition (Primary), 2 rows in the second (DemoHist_2023) and 3 in the third (DemoHist_2024)

Now, in case of recovery if we can restore the Primary & the recent year file group which is DemoHist_2024, we can bring back the database up. Because, DemoHist_2023 contains cold & immutable data for the last year and we do not need it immediately in case of recovery.

Before proceeding with backup let’s check what are the data we’ve so far:

Note that, the Customer table (demonstrated at part 1) reside on Primary File group. Now, let’s take a full backup by filegroup.

--Backup filegroups
USE MASTER
GO
BACKUP DATABASE [DemoBkup] 
	FILEGROUP = 'PRIMARY',
	FILEGROUP = 'DemoHist_2023',
	FILEGROUP = 'DemoHist_2024'	
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File.BAK' WITH INIT
GO

Let’s insert a new customer with transaction on current date.

-- Inserting Row
USE DemoBkup
GO
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (7,'Mahbub','mahbub@mahbub.com')
GO
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (7,'2024-06-19',1,700)
GO

Now, taking differential filegroup backup.

-- Differential Bkup
USE MASTER
GO
BACKUP DATABASE [DemoBkup] 
	FILEGROUP = 'PRIMARY',
	FILEGROUP = 'DemoHist_2023',
	FILEGROUP = 'DemoHist_2024'	
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_1.BAK' WITH INIT
GO

Let’s insert one more customer with transaction.

-- Inserting more row
USE DemoBkup
GO
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (8,'Rafi','rafi@rafi.com')
GO
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (8,'2024-06-19',1,800)
GO

Note that, for both the cases the Customer data reside at Primary File group but the transaction data are at the DemoHist_2024 filegroup. Let’s take the transaction log backup at this point.

-- Transaction Log Bkup
USE MASTER
GO
BACKUP LOG [DemoBkup] 
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Log.BAK'
	WITH NAME = 'DemoBkup-Log',
	DESCRIPTION = 'DemoBkup-Log'
GO

Inserting one more pair of rows and we’ll not take any backup after this insertion.

-- Inserting One more pair of rows
USE DemoBkup
GO
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (9,'Ashraf','ashraf@ashraf.com')
GO
INSERT INTO CustomerTrans (CustomerId,TransDt,TransNo,TransAmount) VALUES (9,'2024-06-19',1,900)
GO

Let’s take the database offline now and drop the three data files Primary, DemoHist_2023 & DemoHist_2024. Then try to bring the database online. It won’t be up but the log will be available for tail backup. Please refer to Part 1 for detail to regenerate this scenario. However, please do not practice this at production.

Since, the log file is available let’s attempt the tail log backup before starting the restore and recovery.

USE MASTER
GO
BACKUP LOG [DemoBkup] 
    TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Tail_Log.BAK'
    WITH NORECOVERY,CONTINUE_AFTER_ERROR,
    NAME = 'DemoBkup-Tail',
    DESCRIPTION = 'DemoBkup-Tail'
GO

Now, we’ll restore the Primary & DemoHist_2024 data file from full, differential, transaction log and tail log backup and open the database.

-- Piecemeal Restore
USE MASTER
GO
RESTORE DATABASE [DemoBkup] FILEGROUP = 'Primary' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File.BAK' WITH PARTIAL, NORECOVERY, REPLACE
GO

USE MASTER
GO
RESTORE DATABASE [DemoBkup] FILEGROUP = 'DemoHist_2024' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File.BAK' WITH NORECOVERY
GO

-- Restore Differential 
USE MASTER
GO
RESTORE DATABASE [DemoBkup] FILEGROUP = 'Primary' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_1.BAK' WITH PARTIAL, NORECOVERY, REPLACE
GO

USE MASTER
GO
RESTORE DATABASE [DemoBkup] FILEGROUP = 'DemoHist_2024' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_1.BAK' WITH NORECOVERY
GO

-- Restore Log
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 	
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Log.BAK' WITH NORECOVERY
GO

-- Restore Tail Log
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 
    FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Tail_Log.BAK' WITH RECOVERY
GO

At this point, the database is up with Primary file group and latest transactional data. To check let’s query the Customer Table first.

See, all customer are there even the customer with Id 9, after which we did not take any backup. But we retrieved it from the tail backup. Now, let’s try to query the CustomerTrans table.

It’ll give an error telling that DemoHist_2023 is not accessible since we’ve not restore that filegroup. We can check the file group status:

SELECT NAME, state_desc
FROM [DemoBkup].sys.database_files;
GO

See, for this specific file group the status is RECOVERY_PENDING. But we still can query the current year data.

Therefore, by restoring the latest file group we can bring the business back and minimize the downtime. Later on, we can proceed with restoring the other filegroups. Let’s do it.

-- Restore Older year filegroup data
USE MASTER
GO
RESTORE DATABASE [DemoBkup] FILEGROUP = 'DemoHist_2023' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File.BAK' WITH RECOVERY
GO

USE MASTER
GO
RESTORE DATABASE [DemoBkup] FILEGROUP = 'DemoHist_2023' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_1.BAK' WITH RECOVERY
GO

-- Restore Log again
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 	
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Log.BAK' WITH RECOVERY
GO

-- Restore Tail Log again
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 
    FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Tail_Log.BAK' WITH RECOVERY
GO

At first, let’s check file group status. All file groups are online now.

If we check Customer & CustomerTrans table we can found all data are now up to date

That’s it, we are done with the complete recovery including the tail of the log. This piecemeal restore will minimize the business recovery time and will help DBA to restore the older data files outside transaction hour.

Apart from them, achieving faster recovery time the filegroup backup also help to restore a single missing or corrupted data file. To simulate the matter let’s take another full backup.

USE MASTER
GO
BACKUP DATABASE [DemoBkup] 
	FILEGROUP = 'PRIMARY',
	FILEGROUP = 'DemoHist_2023',
	FILEGROUP = 'DemoHist_2024'	
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File.BAK' WITH INIT
GO

Now, let’s bring the database offline and delete a file.

-- Missing a Filegroup 
USE MASTER
GO
ALTER DATABASE [DemoBkup]  SET OFFLINE 
GO

-- Remove file from OS
EXEC xp_cmdshell 'del C:\DemoBkup\DemoHist_2023.ndf'
GO

By the way, xp_cmdshell to run OS level command is by default disabled. To enable this run the following.

-- Enabling Command Shell to issue OS command
USE [master]
GO
EXEC sp_configure
'show advanced option',
'1'
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

However, better not to enable this feature at production. Now try to bring it back to online.

-- Bring the Database Online
ALTER DATABASE [DemoBkup]  SET ONLINE
GO

You’ll receive the following error.

To recover this filegroup from the backup set firstly we need to take a tail log backup.

-- Tail Log Bkup (take before any recovery starts)
USE MASTER
GO
BACKUP LOG [DemoBkup] 
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File_Tail_Log.BAK'
	WITH NORECOVERY,CONTINUE_AFTER_ERROR,	-- NORECOVERY ensures the DB will be at recovery mode
	NAME = 'DemoBkup-TailLog',
	DESCRIPTION = 'DemoBkup-TailLog'
GO

Now restore the missing file group followed by restoring the tail log.

-- Restoring only the missing filegroup
USE MASTER
GO

RESTORE DATABASE [DemoBkup] FILEGROUP = 'DemoHist_2023' 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File.BAK' WITH NORECOVERY
GO

-- Restore Tail Log
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_File_Tail_Log.BAK' WITH RECOVERY
GO

This will bring back the entire database to online.

Happy Restoring !!!

Backing up database is an activity that is one of the most regular task to do with. But many times those backup are not verified by restoring. Usually database restore happens for the following:

• Drill restoration
• Data error
• Disaster scenario (which is restore and then recover)

Assume a scenario, where there is a fire on your office and you have fire fighting equipment but no one knows how to use that or even worse the equipment is not working during a fire! That’s why fire drills are there. Similarly there should be data restoration drill at a regular interval to avoid panic at disaster scenario.

Defining Backup Strategy:

To define a backup strategy we need to first set the goal with correct backup type:

• Recovery Point Objective (RPO): How much data can you afford to loose.
• Recovery Time Objective (RTO): How long the downtime can you afford. For very large enterprise database (VLDB) this would be a big challenge.

When we are talking about restore usually it means restore with recover. If we can’t afford much data loss we must need to recover until the last point or at least to the best possible recover point.

In this article we’ll discuss the two most common backup types with restoration scenario. In the later article (https://sultanealam.com/2024/06/19/sql-server-backup-strategy-part-2/) we’ll discuss backup strategy for large database.

The two most common backup types are:

• Full backup
• Differential backup

A full backup is a backup that have the complete database backup and that suffices to bring the database up with a single restore. Whereas, a differential backup is the delta changes since the last full backup.

There are two more backup types followed by the above to restore the database to the most recent transactions:

• Transaction Log Backup – A transaction log is database log file where all the transactions are being written until a full or differential backup is taken. This should be taken at a regular interval until the next full or differential backup.
• Tail Log backup – A tail log backup captures any record that have not yet been backed up. This must be taken before any restoration operation is done. It is usually needed on a database fail or crush scenario.

However, the database recovery model must be set to Full to avail this backup. Without full recovery model you can’t recover beyond the full/ differential backup.

A typical backup plan may be:

• Full backup on the last week day
• Differential backup on each other week day
• Transaction log backup during the day on each 15 minutes

If this can be done, then at worst case we can loose 15 minutes of data if we can’t take a tail log backup. The detail of tail log will be described later.

Simulation of Backup & Restore:

To simulate we will do the following:

• Create a database with some records
• Taking a full backup followed by two differential backup with new data at each backup
• Take a transaction log backup after inserting more data
• Take a tail log backup
• Restoring each sequentially

To start with, let’s create a test database with a single table. Ensure that C:\DemoBkup\ folder exists at your machine.

-- Create New Database
USE MASTER
GO
CREATE DATABASE [DemoBkup]
ON  PRIMARY 
	( NAME = N'DemoBkup', FILENAME = N'C:\DemoBkup\DemoBkup.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON 
	( NAME = N'DemoBkup_log', FILENAME = N'C:\DemoBkup\DemoBkup_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

-- Creating Table
USE DemoBkup
GO

CREATE TABLE Customer
(
	CustomerId		INT			NOT NULL,
	CustomerName	VARCHAR(50) NOT NULL,
	CustomerEmail	VARCHAR(50) NOT NULL,
	CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY],
	CONSTRAINT UQ_Email UNIQUE ([CustomerEmail]) ON [PRIMARY],
) ON [PRIMARY]
GO

-- Inserting Data
USE DemoBkup
GO
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (1,'Sultan Khan','sultan@sultan.com')
GO
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (2,'Ahsan Kabir','ahsan@ahsan.com')
GO

The newly created database can be viewed at SQL Server Management Studio (SSMS).

Now let’s take a full backup.

-- Full Bkup
USE MASTER
GO
BACKUP DATABASE [DemoBkup] 
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup.BAK'
	WITH NAME = 'DemoBkup-Full',
	DESCRIPTION = 'DemoBkup-Full'	
GO

On success, the following message will displayed at output pane

Alternatively, backup can be taken from the SSMS. Right click on the database select Task an then select “Back Up…”

Click on Add to enter the destination path

Type the path name or click on the dot to select from the file system location.

You will receive confirmation on successful backup.

So far, we’ve taken a full backup either using the query editor or using the SSMS. Now let’s insert two more rows and then take a differential backup after each insert.

-- Inserting One More Row
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (3,'Mehedi Hasan','mehedi@mehedi.com')
GO

-- Differential Bkup 1
USE MASTER
GO
BACKUP DATABASE [DemoBkup] 
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_1.BAK' 
	WITH DIFFERENTIAL,
	NAME = 'DemoBkup-Diff-1',
	DESCRIPTION = 'DemoBkup-Diff-1'
GO

-- Inserting Another Row
USE DemoBkup
GO
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (4,'Rifat Jahan','rifat@rifat.com')
GO

-- Differential Bkup 2
USE MASTER
GO
BACKUP DATABASE [DemoBkup]
TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_2.BAK'
WITH DIFFERENTIAL,
NAME = 'DemoBkup-Diff-2',
DESCRIPTION = 'DemoBkup-Diff-2'
GO

Up to this point we’ve one full backup and two differential backup. Note that both the differential backup are the changes from the full backup. So, to restore we need to restore the full backup and the last differential backup.

However, before performing restore let’s assume after the differential backup few other rows also inserted but before taking the next full/ differential backup the database failed. So, to restore and recover up to the last possible point we need the transaction log backup. Let’s insert another row and take a transaction log backup.

-- Inserting another row
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (5,'Saifullah Azad','azad@azad.com')
GO

-- Transaction Log Bkup (the recovery model must be FULL)
USE MASTER
GO
BACKUP LOG [DemoBkup] 
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Log.BAK'
	WITH NAME = 'DemoBkup-Tran',
	DESCRIPTION = 'DemoBkup-Tran'
GO

Both differential & transaction log backup can be taken using SSMS as well.

Now, we’ve one full backup, two differential backup and one transaction log backup. Therefore, we can recover up to this point on any DB crash. Note that, so far we’ve inserted 5 records with customer id 1 to 5.

Now, let’s insert another row for which we won’t take any backup at all.

-- Inserting Further
INSERT INTO Customer (CustomerId,CustomerName,CustomerEmail) VALUES (6,'Shahadat Hemel','hemel@hemel.com')
GO

Let’s try to restore this data after a forced database failure. So, let’s crash the database. At first take the database offline.

Then go to the database file location at windows file explorer C:\DemoBkup and delete the DemoBkup.mdf file which is the master data file. Please do not practice this at production. Now, if we try to bring the database online it’ll fail.

Let’s try to recover the data now.

-- Restore the last full backup
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup.BAK' WITH NORECOVERY, REPLACE
GO

-- Restore the last differential backup
RESTORE DATABASE [DemoBkup] 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Diff_2.BAK' WITH NORECOVERY
GO

-- Restore the log backup
RESTORE DATABASE [DemoBkup] 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Log.BAK' WITH RECOVERY

Look, for the full and differential restore we keep the database state to NORECOVERY. At the final restore we do the RECOVERY. The restoration can be done using the SSMS also. Select Restore Database option right clicking on the database name.

Select Device as source and click the three dot.

Then Click Add and select the three backup set: the full backup, last differential backup and transaction log backup.

From option Select Overwrite the existing database.

Press Ok to continue. Once done (at either way you follow), we will find first 5 customers data that we’ve inserted.

But where is the employee with ID=6? Well, we’ve no transaction log backup for that. So should that data lost? If by any means we can access the database log file then we can take a tail log backup before any restore operation is done.

USE MASTER
GO
BACKUP LOG [DemoBkup] 
	TO DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Tail_Log.BAK'
	WITH NORECOVERY,CONTINUE_AFTER_ERROR,
	NAME = 'DemoBkup-Tail',
	DESCRIPTION = 'DemoBkup-Tail'
GO

Note that at earlier stage, since we suggested to take the database offline and delete the .mdf file (not recommended to do at production), please try to bring the database online first before taking the tail backup. It’ll not bring the DB to online but it’ll make the log available for tail backup.

Now, with the successful tail backup the restore of transaction log should be with NORECOVERY because we’ll recover after tail restoration. These two backup script will be:

-- Restore Transaction Log
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 	
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Log.BAK' WITH NORECOVERY
GO

-- Restore Tail Log
USE MASTER
GO
RESTORE DATABASE [DemoBkup] 
	FROM DISK = 'C:\BkupPlan\DemoBkUp\DemoBkup_Tail_Log.BAK' WITH RECOVERY
GO

Now, we can find the employee with ID=6 also. So we are able to recover up to the last point.

The same restoration can be done from SSMS. Just select the tail backup while selecting the backup set.

So, we are now done with a complete recovery from a DB failure. The backup and recovery always need to be well planned and tested at a regular interval to avoid DB fail on a disaster scenario. The backup plan should be a well written document with a recovery guideline.

Happy Restoring !!!