Printed 2017/01/21 11:21PM

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

By AnupWarrier, 2012/10/01

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.