How to Switch Database Recovery Model
Database Administrator can switch the recovery model for a database by either executing TSQL or by using SQL Server Management Studio
Using TSQL:
— SET THE RECOVERY MODEL TO SIMPLE
ALTER DATABASE ADVENTUREWORKS SET RECOVERY SIMPLE
GO
— SET THE RECOVERY MODEL TO BULK_LOGGED
ALTER DATABASE ADVENTUREWORKS SET RECOVERY BULK_LOGGED
GO
— SET THE RECOVERY MODEL TO FULL
ALTER DATABASE ADVENTUREWORKS SET RECOVERY FULL
GO
Using SQL Server Management Studio (SSMS)
Right click the AdventureWorks database under the Databases node in SSMS and select Properties, select the Options tab under Select a Page, Select the Recovery Model from the drop down list of your choice and finally click OK to save the changes for the AdventureWorks database.
Conclusion
In this article we have discussed in detail all the three database recovery models and when to use each one of them. I would recommend using Full recovery model for all production databases; so that you can use the combination of full, differential and transaction log backups to achieve the point in time recovery for mission critical databases.
]]>