How to create SQL script to backup transaction log with append

• itmasterw 60042

  SSCrazy

  Points: 2858

  More actions

  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

   

   

• Jeffrey Williams

  SSC Guru

  Points: 90345

  More actions

  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

• itmasterw 60042

  SSCrazy

  Points: 2858

  More actions

  June 22, 2021 at 9:48 pm

  #3899453

  Okay, that will work.

  Thnaks