Configure the SQL Server database recovery mode (two methods), SQL Server

Configure the SQL Server database recovery mode (two methods), SQL Server

The following describes how to configure the SQL Server database recovery mode.

Set recovery mode with T-SQL
You can useAlter database"Add"Set recovery"Statement to modify the database recovery mode. For example, the following query statementAdventureWorks"Database recovery mode is set to full recovery mode.

ALTER DATABASE AdventureWorks SET  RECOVERY FULL  ; 

You can query"Sys. databasesTo verify the recovery mode 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 the batch operation mode or the simple recovery mode. You only need to replace the "" location with your own database name.

--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 mode from the model database. The default mode is full recovery. To modify the default recovery mode, you can useALTER DATABASEStatement to modify the recovery mode of the model database.
Note that if you plan to maintain consistent transaction log backup, you cannot switch to the simple recovery mode or change from the simple recovery mode to other.

Use SQL Server management tool (SSMS) to modify the recovery mode
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 click Properties. In the database Properties dialog box, click the options tab and modify the recovery mode as follows:

Have you mastered the above two methods for configuring the SQL Server database recovery mode? You can try to configure it in the future in similar situations. I hope this article will be helpful for your learning.