1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Force a log backup when Full Finishes.

Discussion in 'Microsoft SQL Server' started by ThePravus, Sep 12, 2012.

  1. ThePravus

    ThePravus New Member

    Joined:
    Sep 12, 2012
    Messages:
    1
    Likes Received:
    0
    We are looking for a way to automatically start a log backup as soon as the our Full backup finishes in order to truncate the logs. What is the proper way to do this? Do we just edit the sqlfull.cmd and add a command for log backup after the full?

    -Andrew
     
  2.  
  3. moon-buddy

    moon-buddy Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,206
    Likes Received:
    279
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    I don 't understand completely what you want.

    FULL backups normally (as default) deletes transcation logs after completion unless specifically told not to.

    If you are asking if it is possible to do LOG backups after FULLS, yes you can by running the SQLLOG.cmd script. You can run this as many times as you want either through TSM scheduler or through other schedulers.
     
  4. Stalef

    Stalef New Member

    Joined:
    Mar 12, 2009
    Messages:
    6
    Likes Received:
    0
    Occupation:
    IT-Consultant
    Location:
    Norway
    Make your schedule for that server run a .cmd-file
    I've included an example for a MS SQL Cluster, with the instance on S:, where we put the dsm.opt, tdpsql.full, logs for that instance and our batch-files like S:\TSM\SQLFull.cmd and S:\TSM\SQLLog.cmd

    In TSM:
    DEFine SCHedule <DOMAIN> <SCHED-NAME SQL FULL BACKUP> Type=Client DESCription="Backup of SQL instance on S:" ACTion=Command PRIority=5 STARTTime=21:00 DURation=2 SCHEDStyle=Classic OBJect="S:\TSM\SQLFull.cmd"

    DEFine SCHedule <DOMAIN> <SCHED-NAME SQL LOG BACKUP> Type=Client DESCription="Logbackup of SQL instance on S:" ACTion=Command PRIority=5 STARTTime=22:00 PERIOD=1 Perunits=hour DURUnits=minutes DURation=15 SCHEDStyle=Classic OBJects="S:\TSM\SQLLog.cmd"

    On SQL Server, create to .cmd-files:
    SQLFull.cmd:
    set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
    set sql_opt=S:\tsm
    C:
    cd %sql_dir%
    date < NUL >> %sql_opt%\sqlsched.log
    time < NUL >> %sql_opt%\sqlsched.log
    REM Start Full Backup
    %sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_opt%\dsm.opt /config=%sql_opt%\tdpsql.cfg /logfile=%sql_opt%\sqlfull.log >> %sql_opt%\sqlsched.log
    echo %date% %time% >> %sql_opt%\sqllog.log
    Rem Start Log Backup
    %sql_dir%\tdpsqlc backup * log /tsmoptfile=%sql_opt%\dsm.opt /config=%sql_opt%\tdpsql.cfg /truncate=yes /logfile=%sql_opt%\sqllog.log >> %sql_opt%\sqllog.log

    SQLLog.cmd:
    set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
    set sql_opt=S:\tsm
    C:
    cd %sql_dir%
    echo %date% %time% >> %sql_opt%\sqllog.log
    Rem Start Log Backup
    %sql_dir%\tdpsqlc backup * log /tsmoptfile=%sql_opt%\dsm.opt /config=%sql_opt%\tdpsql.cfg /truncate=yes /logfile=%sql_opt%\sqllog.log >> %sql_opt%\sqllog.log
     
  5. Xeroid

    Xeroid New Member

    Joined:
    Jul 29, 2010
    Messages:
    18
    Likes Received:
    0
    Location:
    Belgium
    Full backup will not truncate the transaction log, it will only backup the active transactions part of the log. I don't understand why you would do a log backup just after a full btw?
    You will have a potential risk of losing one day of work for your db's. If you would schedule the log backup(or even multiple) in the day you have less risk. If risk is not an issue, why not putting the db(s) in simple mode? That way you don't have to worry about the log.

    Dennis
     

Share This Page