SHRINK Database Files with Backup

----- SHRINK Database Files with Backup

----- DECLARE variables
DECLARE
@FileDate varchar(12)
, @DataBackupPath varchar(100)
, @LogBackupPath varchar(10)
, @DatabaseName sysname

----- SET values for variables
SELECT
@FileDate = '200303111800' --Format is yyyymmddhhnn (year, month, day, hour/24, minute)
, @DataBackupPath = 'X:\DataBackupFolder\'
, @LogBackupPath = 'H:\MSSQL\LogBackupFolder\'
, @DatabaseName = DB_NAME()

----- ISSUE CHECKPOINT
CHECKPOINT

----- Get Initial file size
SELECT
(Size * 8)/1024 AS InitialFileSizeMB
, Name AS FileName
FROM
sysFiles

----- Backup Files
EXEC ('BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ''' + @DataBackupPath + @DatabaseName + '_db_' + @FileDate + '.BAK''')
EXEC ('BACKUP LOG ' + @DatabaseName + ' TO DISK = ''' + @LogBackupPath + @DatabaseName + '_tlog_' + @FileDate + '.TRN''')

----- ISSUE CHECKPOINT
CHECKPOINT

----- Shrink Files
----- You may have to modify the SHRINKFILE commands if you use a different naming convention for your logical names.
----- See the names from the sysfiles query above.
EXEC ('DBCC SHRINKFILE (' + @DatabaseName + '_Log, 5)')
EXEC ('BACKUP LOG ' + @DatabaseName + ' TO DISK = ''' + @LogBackupPath + @DatabaseName + '_tlog_' + @FileDate + '.TRN''')
EXEC ('DBCC SHRINKFILE (' + @DatabaseName + '_Log, 5)')
EXEC ('DBCC SHRINKFILE (' + @DatabaseName + '_Data, 5)')

----- Get Final file sizes
SELECT
(Size * 8)/1024 AS NewFileSizeMB
, Name AS FileName
FROM
sysFiles