Total Pageviews

Friday, March 1, 2013

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”

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.