Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2014
    Posts
    6

    Unanswered: TSQL help to setup a SSMS Job Trans Log backup

    Hello everyone,

    I am trying to set up a Full backup of a database MS SQL Server 2008 R2 using SSMS, that is a daily Full Backup. Next I was trying to set up a Backup of the Transaction Logs. I believe I was able to do these two tasks.

    Next my issue is to set up a Job that will backup the Trans Logs every 5 minutes. When trying to do this in SSMS it asks for the TSQL to set up the backup. The Tran Log file needs to be...

    <database_name>_date/time

    What is the TSQL I need to use to set that up.

    Here is what I have so far for the command

    BACKUP LOG ClipixDB
    to DISK ='G:\BACKUP\ClipixDB\ClipixDB_backup_' + CONVERT(VARCHAR, GETDATA(),112)+ '.trn';

    Go

    Is this above correct?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Go check out Ola Hallengren's Backup. You really want to use Ola's backup package instead of trying to "roll your own"!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    FYI: if you decide that you'd rather hurt yourself by trying to recreate what Ola has already done, then you should probably start with:
    Code:
    DECLARE @BackupPath NVARCHAR(255) = 'G:\BACKUP\ClipixDB\ClipixDB_backup_' 
    +  Replace(Convert(VARCHAR(19), GetDate(), 121), ':', '_') + '.trn';
    
    BACKUP LOG ClipixDB
       TO DISK = @BackupPath
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •