Switching multiple databases to FULL recovery model.

To switch a database from simple to full recovery model, it is simple to do when you have one or two databases that need to be switched. You can either do it through the dialogs in SSMS by right clicking on the database and changing the recovery model, or you can run an alter database script.

The difference between simple and full recovery model. In simple recovery model, the you can run full or differential backups. In full recovery model you have the option for running transaction log backups in addition to the full and differential that you get with simple. Transaction logs are important because they allow for a precise point in time recovery that you can’t achieve with full or differential backups. The drawback of full recovery model is that if you aren’t running transaction log backups, the transaction log may just continue to grow.

When you need to make this change for many databases you probably don’t want to type out each database name if you have dozens or hundreds of databases to change. That is why we came up with this script to make it much easier.

Here is the query to find all the databases in simple recovery model and switch them to full recovery model.  The includes the system databases, master, model, tempdb, and msdb. When you run the script it will list all database in simple recovery model, you will want to copy the contents of column 2 “switchToFull”, and past that into a new query window, and execute it.

SELECT [name],
'ALTER DATABASE [' + [name] + '] SET RECOVERY FULL;' as switchToFull
FROM sys.databases
-- only those that are in simple recovery model
WHERE recovery_model = 3 
-- don't do the master, model, tempdb, msdb
AND database_id > 4;  

The results will look something like this, you can copy the entire “switchToFull” column and paste that into a new query window and run it. This script has been tested on SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016, 2017 and 2019.

Be sure to run the full backups, after switching to full recovery model.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!