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)
Last modified by Mohit @ 4/4/2025 7:52:33 AM