SQL Server Database Restore with Snapshot – Backup Chain Breaker

Hi Friends,

Today I will show you practical for one of the reason of Transaction Log backup chain breaker i.e.  SQL Server Database Restore with database snapshot. Suppose I am having one database named as SQLTEST having one table named as xtSQLTest with some amount of data.

--create database
create database SQLTEST
go
USE SQLTEST
go
-- create a table
create table xtTest
(
    id int identity(1,1)
)
go
--insert some data in the table
insert into xtTest default values;
go 100

Now take full backup and insert some more data then again take first transaction log backup:

--take the full backup
backup database SQLTEST to disk='e:\Full.bak'
go
--insert some more data in the table
insert into xtTest default values;
go 100
--take first Transaction log backup
backup log SQLTEST to disk='e:\tlog1.trn'
go

Now create database snapshot here and insert some more data then again take second transaction log backup:

create database snap_SQLTEST on
(NAME=SQLTEST,
 FILENAME='E:\SQLTEST.snap')
 as snapshot of SQLTEST
 go
--insert some more data in the table
insert into xtTest default values;
go 100
--take second Transaction log backup
backup log SQLTEST to disk='e:\tlog2.trn'
go

So everything is running fine till here, now just SQL Server Database Restore with the snapshot which you created earlier, then again try to take third transaction log backup:

-- restore database snapshot
use master
go
restore database SQLTEST from database_snapshot='snap_SQLTEST'
go
--now taking my third Transaction log backup
backup log SQLTEST to disk='e:\tlog3.trn'
go

Here Snapshot restoration ran fine but we receive below mention error when trying to take third transaction log backup:

Msg 4214, Level 16, State 1, Line 2

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 2

BACKUP LOG is terminating abnormally.

So keep in mind that every time when you restore database snapshot, reinitialize the backup chain with full backup after that you can also take transaction log backups:

--Take New Full backup to start new backup chain
backup database SQLTEST to disk='e:\Full_AfterSnapshotRestore.bak'
go
--take first Transaction log backup
backup log SQLTEST to disk='e:\tlog1_AfterSnapshotRestore.trn'
go

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook