Create Backups of Plant Applications SQL Server Transaction Log

About this task

Important:
In this task, angle brackets (< >) indicate placeholder text. If such placeholder text is part of a command, you must replace it with an appropriate value before running the command.

Procedure

  1. Access SQL Server Management Studio (SSMS).
  2. Create a backup of the Plant Applications database.
    Note:
    For more information, refer to the SSMS documentation.
  3. Create a SQL Server Agent job for the Plant Applications SQL server instance. The job must perform the following steps:
    • Step 1: Mark all the replicated transactions in the log as distributed transactions using the following command:
      exec sp_replflush
exec sp_repldone @xactid = NULL,@xact_segno=NULL,@numtrans=0, @time=0, @reset=1

      Using these commands, you can truncate the log when there are replicated transactions in the transaction log that are no longer valid.

    • Step 2: Run the following transaction log backup script:
      SELECT @filename = 'C:\MSSQL_Backup\<plantAppsdatabase>_backup_' +CONVERT(varchar, GETUTCDATE(), 23)+N'.bak' 
BACKUP LOG [<plantAppsdatabase>] TO  
DISK = @filename
WITH RETAINDAYS = 14, NOFORMAT, NOINIT,  NAME = <plantAppsdatabase>-Full Database Backup', SKIP, 
NOREWIND, NOUNLOAD,  STATS = 10
GO
  4. Schedule the job to run at required intervals.
  5. Run the new job, and then verify the following:
    • Whether the job performed all the configured steps.
    • Whether a backup of the Plant Applications SQL server transaction log is successfully created.