Set Up a Scheduled Transaction Log Backup Job

Procedure

  1. Create a backup of the Plant Applications database.
  2. Run the transaction log backup script. You can backup the transaction logs on Fileshare or on the primary node.
    Note:
    If you want to use Fileshare, update the file path in the scripts. This will maintain the latest transaction log backup with the current state of the database. It is ideal to maintain at least 30 days of transaction data for highly changing databases. This gives us enough time to reconnect to the database in case of a complete hub failure or any similar issues.
    The following lines of code is a sample script for a full backup:
    DECLARE @backuppath varchar(250),@filename varchar(250);
                                --Change the path as per what's configured in the system for backpath
                                SET @backuppath = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\'; 
                                SET @filename = CONCAT(@backuppath,'SOADB_FULL.bak')
                                
                                BACKUP DATABASE SOADB TO DISK = @filename
                                WITH FORMAT,
                                NAME = 'Full DATABASE Backup of SOADB';
    The following lines of code is a sample script for a logs backup:
    DECLARE @backuppath varchar(250),@filename varchar(250);
                                    --Change the path as per what's configured in the system for backpath
                                    SET @backuppath = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\'; 
                                    
                                    -- Perform a Transaction Log Backup of the Database
                                    
                                    SET @filename = CONCAT(@backuppath ,'SOADB_backup_' +CONVERT(varchar, GETUTCDATE(), 23)+N'.bak' )
                                    BACKUP LOG SOADB TO  DISK = @filename WITH RETAINDAYS = 30, 
                                    NOFORMAT, NOINIT,  
                                    NAME = 'SOADB Database LOG Backup', 
                                    SKIP, 
                                    NOREWIND, 
                                    NOUNLOAD,  
                                    STATS = 10
                                    GO
  3. Start the Microsoft SQL server agent, and create a new job.
  4. Specify the steps for creating a transaction log backup.

  5. Specify the schedule for the backup.

Results

Back up of the transaction logs is performed based on the schedule that you have specified.