Generate SQL Script to Set Simple Recovery Model and Shrink Databases

Tore Olav Kristiansen

4.60/5 (3 votes)

1 Jan 2016CPOL

12.4K

When you create a new SQL Server database, the default recovery model is Full. This model takes up a lot of disk space. It is ordinarily not necessary for developer images. This tip has a SQL script that helps you change this.

Generate SQL Script to Run to Save db Disk Space

I have written this SQL script:

SQL

SELECT 'Alter database [' + name + '] SET RECOVERY SIMPLE;'+ CHAR(13) + _
'Use [' + name + ']' + CHAR(13) + 'DBCC SHRINKFILE ([' + name + '_log], 5) WITH NO_INFOMSGS' + _
CHAR(13) + 'DBCC SHRINKDATABASE (N''' + name + ''')' + CHAR(13) FROM master..sysdatabases

It will generate the following SQL script for all databases on the connected server when run in SQL Server Management Studio (SSMS):

SQL

Alter database [SharePoint_Config] SET RECOVERY SIMPLE; 
Use [SharePoint_Config] 
DBCC SHRINKFILE ([SharePoint_Config_log], 5) WITH NO_INFOMSGS 
DBCC SHRINKDATABASE (N'SharePoint_Config')

Set "Results to Text" in SSMS and increase the maximum number of characters displayed in each column to avoid cropping:

Copy the result, remove the system databases from it and then run in SSMS to set simple recovery model and shrink database files.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)