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.

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

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

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:

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.

1_Database_Restore_with_Snapshot_Backup_Chain_Breaker

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:

 

Regards

Prince Rastogi

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

Follow me on TwitterFollow me on FaceBook