Someone posted a note saying that their backups were really large and slow. So they were trying to manage their backup scheme and ensure some level of recovery.  I was going back and forth, along with others, talking about the way your recovery would go and the merits of that particular backup scheme. While posting, the person mentioned at one point that his databases were in simple mode.

Huh?

AFAIK, or rather, As-Far-As-I-Remember, you can’t do a log backup in simple mode. I think that’s been a part of the product, but before posting, I decided to test.

Short answer, if you don’t want to read, is that you can’t backup a transaction log if the database is in simple mode.

First I grabbed a sample database on my local 2008 instance and set it to simple mode. Actually, first I had to set a database to simple mode, and I didn’t want to use the GUI. This isn’t something I do often, so I had to look up the syntax:

ALTER DATABASE SET OPTIONS – The first example shows setting to full recovery mode.

ALTER DATABASE db2 SET RECOVERY SIMPLE

Once this was set, I created a few transactions and ran a backup.

CREATE TABLE MyTable (id INT)
INSERT MyTable DEFAULT VALUES

BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

This gives me the following error:

Msg 4208, Level 16, State 1, Line 2

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Line 2

BACKUP LOG is terminating abnormally.

This is what I expect, as there is no backup chain that can be used to build a backup. What about running a full and then a log right away?

BACKUP DATABASE db2 TO DISK = 'db2_db_test.bak'
BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

Once again, the error occurs, and there should be no checkpoint after the full backup starts.

So I think that my posted is mis-informed of his environment, which is a dangerous place for a DBA to be, especially with regard to backup and recovery.

Filed under: Blog Tagged: Backup/Recovery, disaster recovery, sql server, syndicated