Post reply

TSQL For backing up transaction logs

  • September 15, 2011 at 10:31 am

    #390417

    I'm looking for a TSQL cmd for SQL 2008 R2 that essentially does what these TSQL cmds do below for SQL 2005. xp_sqlmaint has been discontinued w/SQL 2008.

    Basically Transaction Logs are backed up and saved for two days before being deleted. Reports are also written and saved for four days. A vendor created the SQL 2005 script for us several years ago and we are upgrading.

    SQL 2005 TSQL:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID BF70DFCF-ABC5-4BA4-9165-356F99EC3159 -Rpt "F:\MSSQL\BACKUP\REPORTS\paragon6.txt" -DelTxtRpt 4DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\MSSQL\BACKUP" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'

    Any assistance would be appreciated.

  • September 15, 2011 at 10:33 am

    #1382126

    http://ola.hallengren.com/

  • September 15, 2011 at 12:42 pm

    #1382216

    Thanks Ninja,

    There is quite a bit of code required to run on my server to create the stored procedures to follow the instructions in link. I would prefer to utilize what is already built into SQL 2008 R2.

  • September 15, 2011 at 3:34 pm

    #1382291

    Then learn how to use the Maintenance plan wizard.

  • September 15, 2011 at 3:45 pm

    #1382292

    aflores1-976495 (9/15/2011)

    I'm looking for a TSQL cmd for SQL 2008 R2 that essentially does what these TSQL cmds do below for SQL 2005. xp_sqlmaint has been discontinued w/SQL 2008.

    Basically Transaction Logs are backed up and saved for two days before being deleted. Reports are also written and saved for four days. A vendor created the SQL 2005 script for us several years ago and we are upgrading.

    SQL 2005 TSQL:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID BF70DFCF-ABC5-4BA4-9165-356F99EC3159 -Rpt "F:\MSSQL\BACKUP\REPORTS\paragon6.txt" -DelTxtRpt 4DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\MSSQL\BACKUP" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'

    Any assistance would be appreciated.

    It is not true that xp_sqlmaint has been discontinued in SQL Server 2008 R2.

    See the link below from SQL Server 2008 R2 Books online:

    http://msdn.microsoft.com/en-us/library/ms188408.aspx

    xp_sqlmaint (Transact-SQL)

    SQL Server 2008 R2

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply