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:
Post a Comment