How to backup the Transaction log, the Database and then Shrink using SQL in Microsoft SQL Server

Very helpful stuff, backup your database without having to click away in Enterprise Manager.
It backs up two databases, some improvements could be made to it of course. Perhaps a loop and reading the names from a table, or even from the system table. But it’s a great simplistic example for people not wanting to trust a ‘do it all’ type script.
edit: Seems my syntax was off a touch, it’s all fixed up now with the addition of creating a stored proc for it.

create procedure BackupAndShrinkDB as
–FirstDBToBackup
declare @sql varchar(1000)
select @sql = ‘c:\backupLocation\FirstDBToBackup\FirstDBToBackup_’ + Left(DateName(Month,getdate()),3) + Convert(varchar(50),Day(getdate())) + ‘.TRN’
backup log FirstDBToBackup
to disk = @sql

select @sql = ‘c:\backupLocation\FirstDBToBackup\FirstDBToBackup_’ + Left(DateName(Month,getdate()),3) + Convert(varchar(50),Day(getdate())) + ‘.BAK’
backup database FirstDBToBackup
to disk = @sql

http://msdn.microsoft.com/en-us/library/aa258287(SQL.80).aspx
DBCC SHRINKDATABASE (FirstDBToBackup, 5) –Shrinks FirstDBToBackup leaving 5% free space

–SecondDBToBackup
select @sql = ‘c:\backupLocation\SecondDBToBackup\SecondDBToBackup_’ + Left(DateName(Month,getdate()),3) + Convert(varchar(50),Day(getdate())) + ‘.TRN’
backup log SecondDBToBackup
to disk = @sql

select @sql = ‘c:\backupLocation\SecondDBToBackup\SecondDBToBackup_’ + Left(DateName(Month,getdate()),3) + Convert(varchar(50),Day(getdate())) + ‘.BAK’
backup database SecondDBToBackup
to disk = @sql

http://msdn.microsoft.com/en-us/library/aa258287(SQL.80).aspx
DBCC SHRINKDATABASE (SecondDBToBackup, 5) –Shrinks SecondDBToBackup leaving 5% free space
GO

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: