Header

Tuesday, February 18, 2014

Gothas on changing the Recovery model from Simple to Full

While teaching traditional Backup and Recovery of SQL Server databases this week I made a simple example to prove a point on changing the Recovery model.

When changing the database from Full to Simple mode, no problems, except that you cannot do log backup.

BUT when changing back from Simple to Full recovery mode - BE AWARE the database does not change the recovery model until you make a Full backup after the change.

You can reproduce using the code below:  
 
--Create the test database
Create database TestRecoveryModelChange
GO

--Check the recovery model of the test database
Select name,recovery_model_desc
from sys.databases
Where Name = 'TestRecoveryModelChange'
GO

--Create a Full backup of the test database
Backup database [TestRecoveryModelChange] to disk='E:\Backup\TestRecoveryModelChange.bak'
GO

--Change Recovery model to Simple
USE [master]
GO
ALTER DATABASE [TestRecoveryModelChange] SET RECOVERY SIMPLE WITH NO_WAIT
GO


--Create a change
USE [TestRecoveryModelChange]
Create table Test(Col1 int)
Insert into Test Values(1)
GO

--Change the recovery model back to FULL
USE [master]
GO
ALTER DATABASE [TestRecoveryModelChange] SET RECOVERY FULL WITH NO_WAIT
GO

--Try doing a log backup
Backup Log [TestRecoveryModelChange] to disk='E:\Backup\TestRecoveryModelChange_Log.trn'


--Uupps !
Msg 4214, Level 16, State 1, Line 3
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

No comments: