Restore Database With Tail Log Backup usage Demo in SQL Server

Restore Database With Tail Log Backup usage Demo in SQL Server

Let Insert Some Sample Data in SQL Server

Insert into [dbo].[SampleDataCopy]
SELECT * FROM [dbo].[SampleData]
where col1 in (24,48)

SELECT * FROM [dbo].[SampleDataCopy]

 After Insert Sample Data  We take Backup of DATABASE see below

Insert into [dbo].[SampleDataCopy]
SELECT * FROM [dbo].[SampleData]
where col1 in (29,30)

SELECT * FROM [dbo].[SampleDataCopy]

BACKUP LOG sampledb
TO DISK='F:\Sampledb_20160629_log1bak.bak' WITH INIT,STATS

one more thing i need to inform that following statement's are same statement


BACKUP LOG sampledb
TO DISK='F:\Sampledb_20160629_log1trn.trn' WITH INIT,STATS 


BACKUP LOG sampledb
TO DISK='F:\Sampledb_20160629_log1bak.bak' WITH INIT,STATS

 Insert into [dbo].[SampleDataCopy]
SELECT * FROM [dbo].[SampleData]
where col1 in (26,66)

SELECT * FROM [dbo].[SampleDataCopy]

BACKUP LOG sampledb
TO DISK='F:\Sampledb_20160629_log2trn.trn' WITH INIT,STATS 

If you are not decide/Forgot to take Tail Log Backup then Issue Restore Database Command

It gives error It needs Tail Log Backup as see below

Tail Log Backup:

Tail Log Backup is Last Transaction Log Backup or Before Restore Operation We take Transaction Log Backup WITH NORECOVERY command is called Tail Log Backup

Syntax for Tail Log backup:

BACKUP LOG databasename
TO DISK ='F:/backupname_datetime_bk.trn' WITH NORECOVERY

So we need to restore database without any issues there is two choices

1) if we take tail log backup we can able to Restore Database without any issues

After we took Tail log backup then we can able to Restore Database without any error

 
 2) If you forget/don't want to take Tail Log Backup(Recommends Tail log backup Compulsory)
then kindly use Restore DATABASE WITH REPLACE command to solve this

USE master

RESTORE DATABASE sampledb
FROM DISK='F:\Sampledb_20160629_bk.Bak' WITH NORECOVERY,REPLACE

We used WITH NORECOVERY so Database will be NoRecovery Mode

So now we need ready to restore tlog1 see below but we should issue WITH RECOVERY to view the records

RESTORE LOG sampledb
FROM DISK='F:\Sampledb_20160629_log1bak.bak' WITH RECOVERY

USE sampledb

SELECT * FROM [dbo].[SampleDataCopy]

 So we can able to RECOVER DB by issue command  WITH RECOVERY 

But If you want to add more files then you need issue Restore with NoRecovery 

Like this we can add more files as see below 

RESTORE LOG sampledb
FROM DISK='F:\Sampledb_20160629_log2.trn' WITH NORECOVERY

also if you need to decide restore with no more files to add then you can recover immediately by
WITH RECOVERY

We can Restore log with recovery as see below
 RESTORE LOG sampledb
FROM DISK='F:\Sampledb_20160629_log2.trn' WITH RECOVERY

 Or
 
you can finally you need to restored database by run below command recover fully if you issued
RESTORE WITH NORECOVERY






RESTORE DATABASE DBNAME WITH RECOVERY

 

Thats it