Post reply

How to create SQL script to backup transaction log with append

  • June 22, 2021 at 9:00 pm

    #3899450

    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:

    BACKUP DATABASE [North2] TO  
    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', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO

     

     

  • June 22, 2021 at 9:45 pm

    #3899452

    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.  https://ola.hallengren.com/sql-server-backup.html

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

  • June 22, 2021 at 9:48 pm

    #3899453

    Okay, that will work.

    Thnaks

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

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