This article explains how to change SQL database recovery model using SQL Server Management Studio (SSMS) and Transact-SQL.
According to Microsoft, a “recovery model” is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.
With SSRS configured for SCCM or reporting in general, by default, the ReportServer database recovery model is enabled as FULL. This means that all activity in the ReportServer database will get logged to the transaction file. Most importantly, the transaction logs can grow with time.
When you want to reduce the size of the SQL Server-based SCCM reporting services log database, you might want to change the recovery model. SQL administrators will know of more ways to change the database recovery model like these.
Recovery Models for SQL Databases
Three recovery models exist for SQL databases: simple, full, and bulk-logged. Typically, a database uses the full recovery model or the simple recovery model. A database can be switched to another recovery model at any time.
The model database sets the default recovery model of new databases. Before switching from the full recovery or bulk-logged recovery model, back up the transaction log.
- Simple: Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
- Full: No work is lost due to a lost or damaged data file.
- Bulk-logged: Reduces log space usage by using minimal logging for most bulk operations.
Also Read: How to Reindex WSUS Database
View Recovery Model of a MS SQL Database
To quickly view the recovery model of a MS SQL database, you can use these steps:
- Launch the SQL Server Management Studio.
- Connect to Microsoft SQL Server Database Engine.
- Right-click any Database and select Properties which opens the Database Properties dialog box.
- Under Select a page pane, click Options. You would see current recovery model displayed under Recovery model list box.
Recommended Reading: How to Move SCCM Database to another drive
1. Change SQL Database Recovery Model using SSMS
Using SQL Server Management Studio, you can change the recovery model for a SQL database with the following steps:
Step 1: Launch the SQL Server Management Studio and connect to Database Instance.
Step 2: Once you are connected to Microsoft SQL Server Database Engine, on the left-hand side in Object Explorer pane, click the SQL server name to expand the server tree. Expand Databases and right-click on the database whose recovery model you wish to change and select Properties.
Step 3: Now you see the Database Properties dialog box. Under Select a page, click Options, and now you can set the recovery model for the selected database. By default, the recovery model for the SQL database is set to Full. From the Recovery model drop-down you can select Simple or Bulk-logged. Click OK to save the changes.
2. Change Database Recovery Model using Transact-SQL
Another easy way to change the recovery model of a database is by using Transact SQL. T-SQL or Transact SQL is the query language specific to the Microsoft SQL Server product. It can help perform operations like retrieving the data from a single row, inserting new rows, and retrieving multiple rows.
Step 1: Launch the SQL Server Management Studio and connect to Database Engine.
Step 2: Once you are connected to Microsoft SQL Server Database Engine, on the left-hand side in Object Explorer pane, click the SQL server name to expand the server tree. Expand Databases and right-click on the database whose recovery model you wish to change and select New Query.
Step 3: In the Query window, enter the below query and click Execute to change the recovery model of the database. Before running the command make sure you replace “DBNAME” with the correct database name.
USE DBNAME;
ALTER DATABASE DBNAME SET RECOVERY SIMPLE;
The commands appear to have completed successfully after running the aforementioned T-SQL command. Using Transact SQL, we were able to change the database’s recovery model.
