Restart Log Backups

My sales database had a problem with log backups failing and the log file grew large. I do the following:

ALTER DATABASE sales
SET RECOVERY SIMPLE
GO
CHECKPOINT
GO
ALTER DATABASE sales SET RECOVERY FULL
GO

When I then issue this:

BACKUP LOG sales TO DISK = 'sales_log_2.trn'
;
GO

I get this error:

Msg 4214, Level 16, State 1, Line 1

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

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

What do I need to do in order to take a log backup?

Choose your answer:

Take a full backup and then a log backup Take a differential backup and then a log backup Take either a full or differential backup and then a log backup You cannot restart log backups until the database is detached and re-attached.

Correct answer:

Take either a full or differential backup and then a log backup

Explanation:

You can take either a full or a differential backup and then start your log backups again. Ref: A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup -http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282030%29-restarting-a-log-backup-chain-requires-a-full-backup.aspx If you are looking for deep, intensive, Immersion training on this and other SQL Server topics, try a class from SQLskills: SQLskills Immersion Events Some test code for you. Run these one at a time to see what happens:

CREATE DATABASE sales
;
GO
USE sales
GO
CREATE TABLE MyTable(myid INT);
GO
BACKUP DATABASE sales TO DISK = 'Sales_Full.bak'
GO
INSERT MYTable values (1), (2), (3)
;
go
BACKUP LOG sales TO DISK = 'sales_log_1.trn'
;
GO
ALTER DATABASE sales SET RECOVERY SIMPLE
;
GO
CHECKPOINT
;
GO
ALTER DATABASE sales SET RECOVERY FULL
;
GO
INSERT MYTable values (4), (5), (6)
;
go
BACKUP LOG sales TO DISK = 'sales_log_2.trn'
;
GO
BACKUP DATABASE sales  TO DISK = 'sales_diff.dff' WITH Differential
;
GO
BACKUP LOG sales TO DISK = 'sales_log_2.trn'
;
GO