How to create SQL script to backup transaction log with append

  • Hi,


    I am trying to write a backup script, in a way that it will not write over the other files that are there.

    However, I have not found anything that would show me how to do this.

    Can someone tell me how I can write a script to do this?

    Thank you

    Here is what I have so far:

    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TranLogBackup\North2_Log.trn'
    WITH NOFORMAT, NOINIT, NAME = N'North2-Full Database Backup',



  • Maybe this will give you some ideas:

            Backup Database @database 
    To Disk = @filename
    With Name = @backupName
    , Buffercount = @bufferCount
    , Description = @backupDescription
    , Maxtransfersize = @maxTransferSize
    , stats = 5, Checksum;

    All you need to do is declare and define the variables for the backup.  Here is one for the log backups:

    Declare @backupType char(4) = 'tlog'
    , @timestamp char(12) = convert(char(8), getdate(), 112) + replace(convert(char(5), getdate(), 108), ':', '');

    Set @backupName = replace(replace(@backupName, '%D', @database), '%T', @backupType);
    Set @filename = replace(replace(replace(@filename, '%DATETIME%', @timestamp), '%D', @database), '%T', @backupType);
    Set @backupDescription = replace(replace(replace(@backupDescription, '%DATETIME%'
    , convert(varchar(20), getdate(), 120)), '%D', @database), '%T', @backupType);

    --==== Run the backup
    Backup Log @database
    To Disk = @filename
    With Name = @backupName
    , Description = @backupDescription
    , stats = 5, Checksum;

    Or - you can download Ola's utility which can be setup and configured for any/all maintenance type tasks.


    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Okay, that will work.


Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply