SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Backing up the “log” of a database which is in Simple recovery model ! – Watch out for this!

In theory we cannot backup the log of a database which is in Simple recovery model, however there is a small bug in SQL 2012 which allows you to do this, and you need to be very careful about this.

Below is the detailed repro steps for this issue and the testing has been done using SQL 2012 RTM bits.

1. Change the recovery model of Model database to Simple.

2. Create a new database.

3. The newly created database will be having recovery model as simple as it inherits the properties of the model database.

4. Backup the newly created database.

TO DISK='I:\Test_LogBackup_Full.bak'

Processed 264 pages for database 'Test_LogBackup', file 'Test_LogBackup' on file 1.
100 percent processed.
Processed 2 pages for database 'Test_LogBackup', file 'Test_LogBackup_log' on file 1.
BACKUP DATABASE successfully processed 266 pages in 0.570 seconds (3.632 MB/sec).

5. Now try to backup the log of this database which is in simple recovery model

BACKUP LOG Test_LogBackup
TO DISK='I:\Test_LogBackup_Tran.trn'

Processed 3 pages for database 'Test_LogBackup', file 'Test_LogBackup_log' on file 1.
BACKUP LOG successfully processed 3 pages in 0.003 seconds (5.371 MB/sec).

This behavior is not expected and this was reported by one of the users(@nayanraval) on twitter. After multiple successful repro’s the user was suggested to open a case with Microsoft PSS group for a quick resolution.  A connect item is also open for you to sign-in and vote.


I will keep watching this connect item and will update this post once it’s resolved or we get a response from Microsoft on this behavior.

Thanks for reading.


Anup SivaDas handles the Database Engineering initiatives for Expedia, Inc. (http://www.expedia.com/), having IT experience of more than 9 years. Anup is an active blogger with SQLSailor.com, and can also be found on MSDN SQLServer forums and BeyondRelational.com. He has handled multiple SQLServer projects for various fortune 500 companies, and gained enrich proficiency within Database Administration, Database Architecture for Cloud, Consulting, Virtualization, Build, and Production Support activities. Blog | Twitter | LinkedIn


Leave a comment on the original post [sqlsailor.com, opens in a new window]

Loading comments...