SQl Server DBA : Transaction Log Backup

Here is the stored procedure to backup the transaction log files:
backupThis procedure takes the database name and backupPath as input parameters. So this procedure can be used for any databases on the server. Place this procedure in a job and schedule it to run every ‘N’ minutes.

Make sure you copy the transaction log backup files to a different server so that can be used for disaster recovery. Also write a batch file to delete the old files otherwise it will fill your drive.

Here is the code if you want to copy and paste:

if object_id('dbo.BackupDatabaseTransactionLogs','P') is not null
drop procedure dbo.BackupDatabaseTransactionLogs
go
create procedure dbo.BackupDatabaseTransactionLogs
(
@databaseName nvarchar(100),
@backupPath nvarchar(500)
)
as
begin
set nocount on;
declare    @date nvarchar(20),
@backupFileName nvarchar(100),
@script    nvarchar(500),
@logFileName nvarchar(100)
set @date = convert(varchar(10),getdate(),112) +'_' + replace(convert(varchar(10),getdate(),108),':','');
set @backupFileName = @databaseName + @date +'.trn';
set @script = 'BACKUP LOG ['+@databaseName+'] TO  DISK = N'''+ @backupPath + '' + @backupFileName + ''' WITH COMPRESSION';
exec(@script);
end
go

Advertisements

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