Compress and Backup SQL Database

Down the line you may come across a point when you need to compress an SQL Database by truncating the log files, an annoying task that cannot be completely undertaken from the GUI (the shrink file option does not truncate the log files). After I had several companies complaining of excessively large Log files on some of our larger more heavily utilised databases I came up with the following script that can be run whenever you need to compress the database.

-- Shrink the Log Files as much as we can, this is the same as the GUI option
DBCC SHRINKFILE (N'DatabaseLogFileName',100)
-- We are now going to truncate the log files, there are rear occasions when you may want to keep the transaction logs but I've never had to work in such an environment as of yet. 
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE
CHECKPOINT
-- Once the checkpoint command is run the log files they go bye bye so we can now set ourselves back to our original state
ALTER DATABASE [DatabaseName] SET RECOVERY FULL
-- OK Shrink the main database file
DBCC SHRINKDATABASE (DatabaseName)
-- Back it up to a file
BACKUP DATABASE [DatabaseName] TO DISK = N'BackupFileLocation' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
-- And while we're at it we might as well reindex the database, you can find this code in one of my previous articles
EXEC TableReindexer

Just make sure you replace the DatabaseLogFileName this is usually the name of the Database with _log stuck on the end, but can be located under logical name of the database file properties. DatabaseName and BackupFileLocation are pretty self explanatory.

You can also run this from a batch file using the SQLCMD command should you wish to set up a scheduled task to do such things as I do.

SET SQLSERVER=SERVERINSTANCE
SET DATABASE=DATABASENAME
SET BACKUPLOCATION=BACKUPLOCATION
SET BACKUPFILENAME=BACKUPNAME

DEL %BACKUPLOCATION%%BACKUPFILENAME% /S /Q

REM Reindex all the keys within the database
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "EXEC tag_tablereindexer"

REM -- Truncate the log file attached to the database, this can sometimes get a little large and slow down the system overall
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY SIMPLE"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "CHECKPOINT"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY FULL"

REM -- Shrink the actual database itself
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKDATABASE (%DATABASE%)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
REM -- Backup the databse to our backup location
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%BACKUPLOCATION%%BACKUPFILENAME%' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Again SQLSERVER should be set to the name of the server instance e.g. SQLSVR03\DB12 DATABASE is the database name, BACKUPLOCATION is the path to the backup which also requires to be left with a leadin backslash e.g. c:\backups\ BACKUPFILENAME is the name you want for the backup e.g. SQLBackup.bak