SQL Server – Default to simple recovery model

SQL Server - Default to simple recovery model

SQL server’s recovery model is crucial for designing a backup and restore strategy. However, it can be a bit of a management chore when dealing with Development server.

If you don’t want to be bothered to look at databases where the log file is 100 times larger than the data files then the following might come in handy:

Simple recovery model – Script

The PowerShell script below will update the recovery model to Simple for all databases. Just make sure to alter in instance name and optionally alert someone in case of an exception. The statements below the comment ‘#additonal cleanup‘ can be removed if covered by other maintenance scripts.

Note: Altering all existing databases ( including the ‘model’ database).

This includes changing the recovery model on the ‘Model‘ database to simple. Therefore newly created user databases will use the simple recovery model unless otherwise specified.

More about recovery modes / models can be found here https://msdn.microsoft.com/en-us/library/ms189275.aspx

Post Navigation