Take Log backup after Changing Recovery model from FULL to SIMPLE

There are Some common misconceptions that if we change the database recovery model from “FULL” to “SIMPLE”
Then we require full backup of the database to re-initiated the LSN (log sequence number) for later transaction log backups.
Well, it is not necessary to undergo a full database backup, differential backup performs the trick.

Now, I am going to show you one scenario where we will take Log backup after Changing Recovery model from FULL to SIMPLE

Let’s get start:-

Create a Database and set it’s Recovery Model to Full and check database status

CREATE DATABASE Prod_Demo
GO

ALTER DATABASE Prod_Demo SET RECOVERY FULL

SELECT [name] ,
recovery_model_desc ,
state_desc
FROM sys.databases
WHERE [name] = 'Prod_Demo'

Creating one table and insert some records

CREATE TABLE tbl_Prod_Demo
(
ID INT ,
Name VARCHAR(25)
)
GO
INSERT INTO tbl_Prod_Demo VALUES ( 1, 'A' )
INSERT INTO tbl_Prod_Demo VALUES ( 2, 'B' )

Take full backup of the database

BACKUP DATABASE Prod_Demo TO DISK ='D:\Secondry_DB_Backup\Prod_Demo_FULL.BAK'

Insert some record and take a Transaction Log backup

INSERT INTO tbl_Prod_Demo VALUES ( 3, 'C' )
INSERT INTO tbl_Prod_Demo VALUES ( 4, 'D' )

BACKUP LOG Prod_Demo TO DISK='D:\Secondry_DB_Backup\Prod_Demo_Log_01.TRN'

Now change the database recovery model from FULL to SIMPLE

USE master
ALTER DATABASE Prod_Demo SET RECOVERY SIMPLE

SELECT [name] ,
recovery_model_desc ,
state_desc
FROM sys.databases
WHERE [name] = 'Prod_Demo'

Shrink the database log

USE Prod_Demo
GO
DBCC SHRINKFILE ('Prod_Demo_log', 0)

Again Insert Some record

INSERT INTO tbl_Prod_Demo VALUES ( 5, 'fourth' )

Check the table records

SELECT * FROM tbl_Prod_Demo

Change the database recovery mode to FULL

USE master
ALTER DATABASE Prod_Demo SET RECOVERY FULL

Perform a DIFFERENTIAL database backup

BACKUP DATABASE Prod_Demo TO DISK ='D:\Secondry_DB_Backup\Prod_Demo_DIFF_01.BAK' WITH DIFFERENTIAL

Insert some record againgain

INSERT INTO tbl_Prod_Demo VALUES ( 4, 'D' )

INSERT INTO tbl_Prod_Demo VALUES ( 5, 'E' )

Again Check the records

SELECT * FROM tbl_Prod_Demo

Now perform a Transaction log backup

BACKUP LOG Prod_Demo TO DISK='D:\Secondry_DB_Backup\Prod_Demo_Log_02.TRN'

If Something goes wrong in future! suppose We dropped the database or database corrupted then we lost the database, so we need to restore it from our backup and checked all data are available or not ?

RESTORE DATABASE Prod_Demo FROM DISK='D:\Secondry_DB_Backup\Prod_Demo_FULL.BAK' WITH NORECOVERY

RESTORE DATABASE Prod_Demo FROM DISK='D:\Secondry_DB_Backup\Prod_Demo_DIFF_01.BAK' WITH NORECOVERY

RESTORE LOG Prod_Demo FROM DISK='D:\Secondry_DB_Backup\Prod_Demo_Log_02.TRN' WITH NORECOVERY

RESTORE DATABASE Prod_Demo WITH RECOVERY

check database recovery model and status

SELECT [name] ,
recovery_model_desc ,
state_desc
FROM sys.databases
WHERE [name] = 'Prod_Demo'

13. Now check all records are available or not in a table

USE Prod_Demo
SELECT * FROM tbl_Prod_Demo

SQL Server – Take Log backup after Changing Recovery model from FULL to SIMPLE

Leave a Reply Cancel reply