Shrink Database Size for SAT

  • Article

I routinely have to backup and restore databases from customer’s prod environment to the SAT environment. The SAT environment is often limited in terms of storage so I like it when I can do little things that make a big impact on storage size.

Below is a SQL script I use to change the recovery for databases. The Full recovery model used in production is useful in order to play back transaction logs but unnecessary in SAT.

 --Will Change recovery model of all databases (except tempdb) to simple.
  
 --Declaration of variables
 DECLARE @DBName SysName, @sql VarChar(100)
 -- Declare begin cursor to get the database names and get info from sys.databases catalog
 DECLARE Cursor_db Cursor FOR SELECT Name From sys.Databases WHERE Name != 'TempDB'
 -- Now using the cursor to loop through database names and change recovery model
 OPEN Cursor_db Fetch NEXT From Cursor_db INTO @DBName 
 --While Loop with Alter database command
 WHILE @@fetch_status = 0
 BEGIN--print 'database is ' + @DBName 
 SET @sql='ALTER Database ' + @DBName + ' SET Recovery Simple'
 PRINT 'sql is ' + @sql
 exec (@sql)
  
 Fetch Next FROM Cursor_db INTO @DBName 
 END--clean up objects
 CLOSE Cursor_db 
 Deallocate Cursor_db

 

The next script will shrink the log files for all the databases once you are in simple recovery mode.

 --The files will not shrink if you don't have the recovery model set to simple
  
 --suppress counts from being returned.
 set nocount on;
 --Declaration of variables
 DECLARE @LogFile SysName, @DBName SysName, @sql VarChar(1000)
 -- Declare begin cursor to get the file names. Join two tables to get the log file name
 --and the datbase name for use in the use databasename statement
 DECLARE Cursor_db Cursor FOR SELECT a.name as LogFileName, b.name as DbName
     FROM sys.master_files a, sys.databases b
     where a.type_desc = 'LOG' 
         and a.name not in ('templog')
         and a.database_id = b.database_id
 -- Now using the cursor to loop through log file names and shrink
 OPEN Cursor_db Fetch NEXT From Cursor_db INTO @LogFile, @DBName
 --While Loop with Alter database command
 WHILE @@fetch_status = 0
 BEGIN--print 'Shrinking ' + @LogFile + '...'
 SET @sql='use ' + @DBName + ';  DBCC SHRINKFILE(' + @LogFile + ', 1);'
 PRINT 'sql is ' + @sql
 exec (@sql)
  
 Fetch Next FROM Cursor_db INTO @LogFile, @DBName
 END--clean up objects
 CLOSE Cursor_db 
 Deallocate Cursor_db