Changing Recovery model from FULL to SIMPLE

There is a common misconception that if you change the database recovery model from “FULL” to “SIMPLE”, then you need the FULL database backup to reestablish the LSN (Log Sequence Number) for subsequent transaction log backup. Well, undergoing a full database backup is not necessary, Differential Backup does the trick.

Let’s create a production like scenario where some point in time we need to change the recovery model from FULL to SIMPLE due to Transaction Log issue. Following is our workflow to recover the database:

·         Create a database

·         Create a table

·         Backup the database

·         Take a transaction log backup

·         Insert a record

·         Take another transaction log backup

·         Change the recovery model to SIMPLE

·         Shrink the log file, shrink the database

·         Attempt to perform transaction log backup (will fail)

·         Insert second record

·         Change the recovery model to FULL

·         Perform a differential database backup

·         Insert third record

·         Take a transaction log backup

·         A disaster happened – in our example, we dropped the database.

·         Restore the database from the backup

·         Restore the differential backup

·         Restore the last transaction log backup

·         We are good to go.

Let’s get started:

1.       Database creation and verification

CREATE DATABASE TestDB

GO

USE TestDB

GO

EXEC sp_helpfile

ALTER DATABASE TestDB SET RECOVERY FULL

SELECT  [name] ,

        recovery_model_desc ,

        state_desc

FROM    sys.databases

WHERE   [name] = 'TestDB'

2.       Create a table and insert one record

CREATE TABLE tblTest

       (

         xID INT ,

         sName VARCHAR(20)

       )

GO

INSERT  INTO tblTest VALUES  ( 1, 'First' )

3.       Backup the database

BACKUP DATABASE TestDB TO DISK ='D:\TEMP\TestDB_FULL.BAK'

4.       Insert one record and perform a Transaction Log backup

INSERT  INTO tblTest VALUES  ( 2, 'Second' )

BACKUP LOG TestDB TO DISK='D:\TEMP\TestDB_Log_01.TRN'

5.       Now change the database recovery model to SIMPLE

USE master

ALTER DATABASE TestDB SET RECOVERY SIMPLE

SELECT  [name] ,

        recovery_model_desc ,

        state_desc

FROM    sys.databases

WHERE   [name] = 'TestDB'

6.       Do a log shrink

USE TestDB

GO

DBCC SHRINKFILE ('TestDB_log', 0)

7.       Insert third record

INSERT  INTO tblTest VALUES  ( 3, 'Third' )

SELECT  * FROM    tblTest

8.       Change the database recovery mode to FULL

USE master

ALTER DATABASE TestDB SET RECOVERY FULL

9.       Perform a DIFFERENTIAL database backup

BACKUP DATABASE TestDB TO DISK ='D:\TEMP\TestDB_DIFF_01.BAK' WITH DIFFERENTIAL

10.  Insert forth record

INSERT  INTO tblTest VALUES  ( 4, 'Forth' )

SELECT  * FROM    tblTest

11.   Let’s perform a Transaction log backup

BACKUP LOG TestDB TO DISK='D:\TEMP\TestDB_Log_02.TRN'

12.   Something went wrong! We dropped the database or it is corrupted -we lost the database, so we need to restore it from our backup. Let’s do it.

RESTORE DATABASE TestDB FROM DISK='D:\TEMP\TestDB_FULL.BAK' WITH NORECOVERY

RESTORE DATABASE TestDB FROM DISK='D:\TEMP\TestDB_DIFF_01.BAK' WITH NORECOVERY

RESTORE LOG TestDB FROM DISK='D:\TEMP\TestDB_Log_02.TRN' WITH NORECOVERY

RESTORE DATABASE TestDB WITH RECOVERY

SELECT  [name] ,

        recovery_model_desc ,

        state_desc

FROM    sys.databases

WHERE   [name] = 'TestDB'

13.   Do we have all records? Yes we do

USE TestDB

SELECT  * FROM    TblTest

Summary:

By changing the database recovery model from “FULL” to “SIMPLE”, we have not lost the ability to apply transaction log when there was a Differential backup afterward. Therefore, it proved that there is no need to have a full database backup in such case. Please note that when we have database mirroring, we can’t change the recovery model, unless we break the mirroring.

Learn More:

Watch Paul Randal’s in-depth tutorial - “SQL Server: Logging, Recovery, and the Transaction Log”

http://pluralsight.com/training/Courses/TableOfContents/sqlserver-logging