Backup & Shrink Database in SQL2008

This sample t-sql script demonstrates how to back up a SQL 2008 database and truncate the database & log files.

-- declare variables to hold backup information 
declare @db varchar(100) 
declare @backupPath varchar(255) 
declare @backupPathLog varchar(255) 

-- set the variables 
set @db = 'EnterDatabaseNameHere' 
set @backupPath = 'SetBackupFilePathNameHere' 
set @backupPathLog = 'SetLogBackupFilePathNameHere' 

-- note: backup the database 
print '' 
print 'backup the database' 
BACKUP database @db to disk=@backupPath with init 

-- note: backup the log 
print '' 
print 'backup the log' 
BACKUP LOG @db to disk=@backupPathLog with init 

-- note: Rearranges data arranged in file. 

-- Moves data at end to unallocated pages in front of file. 
print '' 
print 'Rearrange data arranged in file' 
DBCC SHRINKDATABASE (@db, notruncate) 

-- note: backup the log a 2nd time 
-- The ShrinkDatabase command above added entries to the log. Back it up again. 
print '' 
print 'backup the log, take 2' 
BACKUP LOG @db to disk=@backupPathLog with init 

-- note: Release all free space at the end of the file 
print '' 
print 'Release all free space at the end of the file' 
DBCC SHRINKDATABASE (@db, truncateonly)