Configure SQL Server database recovery mode (2 methods)

Time:2020-10-31

The following two methods of configuring SQL Server database recovery mode are mainly introduced.

Setting recovery mode with T-SQL
You can use it“ALTER DATABASE”Command plus“SET RECOVERY”Statement to modify the recovery mode of the database. For example, the following query statement sets the“AdventureWorks”The recovery mode of the database is set to full recovery mode.


ALTER DATABASE AdventureWorks SET  RECOVERY FULL  ; 

You can check“sys.databases”To verify the recovery mode state of the database. The statement is as follows:


SELECT  name, recovery_model,  recovery_model_desc FROM sys.databases WHERE  name = 'AdventureWorks'  ; 

You can use the following statement to set the database to mass operation mode or simple recovery mode, just replace it with the name of your own database in the position of.


--Changing recovery model to Bulk-loggedALTER DATABASE AdventureWorks SET  RECOVERY BULK_LOGGED  ;--Changing recovery model to SimpleALTER DATABASE AdventureWorks SET  RECOVERY SIMPLE  ;

When a new database is created, it inherits the recovery model from the model database, and the default is full recovery mode. To change the default recovery mode, you can use the“ALTER DATABASE”Statement to modify the recovery mode of the model database.
Note that if you intend to maintain a consistent transaction log backup, you cannot switch to the simple recovery model or change from the simple recovery model to something else.

Modify the recovery mode using SQL Server management tools (SSMS)
You can modify the database recovery mode in SQL Server management tools. In the object browser, right-click your database (under the database node), and then click properties. In the database properties dialog box, click the Options tab, and then modify the recovery mode as follows:

Have you mastered the above two methods of configuring SQL Server database recovery mode? If you encounter similar situation, you can try to configure it. I hope this article will help you in your study.