Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Backing up the log in simple mode

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.

db_simple_log

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

Comments

Posted by Jason Brimhall on 25 January 2011

I wonder if the OP was backing up the logs through an OS backup rather than through SQL...

Leave a Comment

Please register or log in to leave a comment.