Database Recovery Models in SQL Server


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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |