Change Database Recovery Model in SQL Server

In this article, we will change database recovery model in SQL Server via SSMS or script. Also I will share scripts to to find recovery model of all databases and change recovery model to simple for all databases in SQL Server at the end of the article.

If you want to learn below topics you should read the article named “Database Recovery Mode in SQL Server

  • What is recovery model?
  • Database Recovery Models
  • What is difference between simple and full recovery model?
  • SQL Server Recovery Model Simple vs Full Performance
  • Difference Between Full and Bulk Logged Recovery Model in SQL Server

We can change database recovery model through SSMS (SQL Server Managemet Studio) or TSQL script.

Change Database Recovery Model

Right click on the database via SSMS and click Properties.

Go to the Options tab and select Full, Bulk-logged or Simple from Recovery Model and click OK.

In addition to SSMS, you can change database recovery mode by using the following script.

You can set one of the following recovery models.

  • FULL
  • SIMPLE
  • BULK_LOGGED

Change Database Recovery Mode to Simple

Change Database Recovery Mode to Full

Change Database Recovery Mode to Bulk-Logged

Query to find recovery model of all databases in SQL Server

You may need to list all databases with recovery model information. Below script will help you.

Change Recovery Model to Simple for all Databases

You may need to change all databases recovery mode at once. But be careful when changing recovery model from full to simple. Because you can not backup your transaction log in simple recovery model.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *