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
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [your_db_name] SET RECOVERY your_recovery_model WITH NO_WAIT GO |
Change Database Recovery Mode to Simple
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [your_db_name] SET RECOVERY Simple WITH NO_WAIT GO |
Change Database Recovery Mode to Full
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [your_db_name] SET RECOVERY Full WITH NO_WAIT GO |
Change Database Recovery Mode to Bulk-Logged
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [your_db_name] SET RECOVERY Bulk_Logged WITH NO_WAIT GO |
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.
|
1 |
SELECT name, recovery_model_desc FROM sys.databases |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE [master] GO DECLARE @dbname NVARCHAR(500) DECLARE mycursor CURSOR FOR --Exclude system databases with where clause. SELECT [name] FROM sys.databases where database_id>4 OPEN mycursor FETCH NEXT FROM mycursor INTO @dbname WHILE @@FETCH_STATUS=0 BEGIN EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY Simple') FETCH NEXT FROM mycursor INTO @dbname END CLOSE mycursor DEALLOCATE mycursor GO |
