Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2006
    Posts
    28

    Unanswered: Backup Transaction Logs Using T-SQL

    I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.

    BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\ca001_TRN_bkuplog.trn' with retaindays = 1

    Any suggestions welcomed.

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    At my place, we have a separate cleanup job that
    deletes the files using xp_cmdshell .

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Try the Database Maintenance Plan. It's a database object and one of the properties is "Remove Files Older Than ___". It creates an entry in your SQL SERVER AGENT under JOBS to run the plan as scheduled.

    It also allows much more than just simple backups and it very easy to manage.

    I set up a Sunday backup that keeps it's prior Sunday file but removes older ones, but that backup doesn't affect my ongoing daily backups and logfile changes (separate jobs).

    Also: I notice you're referencing a "S:" drive. Does SQL backup recognize mapped drives? Maybe that's just a shortcoming of the Database Maintenance Plan Object. It would seem risky. I'd rather do it to a separate (physical) volume physically attached, then immediately copy that the mapped drive (that's what I do). That way a RAID corruption on the DB volume doesn't corrupt the backup too. Also; my backup volume is just RAID 1 (mirrored), since that's easily transfered to a different server in a disaster situation.
    Last edited by vich; 10-12-06 at 15:51.

  4. #4
    Join Date
    Jan 2006
    Posts
    28
    The S:\ drive is actually an iscsi drive that is recognised by the server. What is funny is that the maintenance plan for the database backup works just not the transaction logs. I also have checked to make sure the database was not in simple recovery mode.

    I am sort of trying to find a way to give my backup names a date and time. Then I want to only keep a days worth of transaction logs becuase we do a full backup of the database everynight.

    I do have
    Code:
    declare @bkupdate datetime
    set @bkupdate = getdate()
    but I don't know how to include it into my sql statement. When I do I get an syntax error.

    Code:
    BACKUP LOG CA001 TO DISK = 'S:\aztargp\ca001\CA001_' + @bkupdate + '_TRNLOG.trn' with retaindays = 1
    I am just trying to use t-sql until I can find the problem with why my database maintenance plan is not working. Thanks for the help so far.

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    OK. Here's one that works here.

    BACKUP DATABASE [wfab] TO DISK = N'd:\SQL Backups\Daily\mydb_db_latest.bak' WITH INIT , NAME = N'mydbLatest', SKIP , FORMAT

    This hard codes the filename. I have this running every 6 hours. Surely using a variable for the filename would be fine, although why not build the whole thing first.

    So:

    declare @bkdbName varchar(50)
    set @bkdbName = 'mydb' + Convert(varchar(50),getdate(),110)

    declare @bkName varchar(255)
    set @bkName = 'd:'
    set @bkName = trim(@bkName) + '\SQL Backups\Daily\'
    set @bkName = trim(@bkName) + trim(@bkdbName)

    BACKUP DATABASE [wfab] TO DISK = trim(@bkName) WITH INIT , NAME = trim(@bkName), SKIP , FORMAT

    The reason I liked it without the special naming is because then it shows up pre-registered when I want to do a restore. I do a scheduled copy of these backup files to various places before the next backup takes place. Actually; I copy it to a standby server (as with the half-hour log files), ftp it to an offsite server, and I have a scheduled CD burn (encrypted) to my desktop at 3:30 every day, that I take home with me. This in addition to replication - I think it's safe to say we'll never loose much.

    I haven't tried the above - I'm curious if it would then cause all the prior backups to show on the pre-registered backup file list. That would be very nice for restores - something we do regularly to test environments.

  6. #6
    Join Date
    Jan 2006
    Posts
    28
    code works great. Had to change a couple of things. How can I get the @bkdbName to also contain the time?

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by jstephens
    code works great. Had to change a couple of things. How can I get the @bkdbName to also contain the time?
    Here's a link to MSDN for TSQL Ref. Make this a favorite.

    Look at "Cast and convert" for conversion formats.

    This should help:

    Code:
    select CONVERT(varchar(8),getdate(),114)
    Result is: 15:57:34 Note: 114 returns 11 or 12 characters, but you probably don't need miliseconds.

    Use SUBSTRING to parse out the ":" since that's probably not allowed as part of a filename.

    I'm curious (and too lazy to try it). After creating a couple of these backups, does it list all these backups on the drop-down list when you go into the RESTORE dialog box? I know that works if the name is static.

  8. #8
    Join Date
    Jan 2006
    Posts
    28
    Have not tried it yet. Will when I get a change this afternoon.

  9. #9
    Join Date
    Jan 2006
    Posts
    28
    Thanks guys for the help. I also found out what was wrong with my DB maintenance plans. When you set to backup master,model,msdb and other databases in one plan it works fine for the db backups but not the translog. When i created a seperate plan for my user databases the transaction log backups completed successfully.

  10. #10
    Join Date
    Jul 2009
    Posts
    4

    Cool With SQL Server 2005 I had to make a fair few amendments ...

    Just in case this solution is not working for people or they need a more specfic backup script, I made the following alterations to ensure that they would work correctly:

    -- Declares a string
    declare @sql nvarchar(255)

    -- Declares the filename. Please change _CHANGE_ to backup name
    declare @bkdbName varchar(50)
    set @bkdbName = '_CHANGE_' + Convert(varchar(50),getdate(),110)

    -- declares the path name. Seperated by disk drive and directory.
    declare @bkName varchar(255)

    -- Enter the Drive letter below. Please remove C: as current value.
    set @bkName = 'C:'

    -- Enter the folder name below (with lead and end '\' back slashes.
    -- Ensure that you change the folder from Temp below.
    set @bkName = @bkName + '\Temp\'
    set @bkName = @bkName + @bkdbName

    -- Sets the backup running by executing the @sql string.
    set @sql = 'BACKUP DATABASE DB_NAME TO DISK = '''+@bkName+'.bak'''
    print @sql
    exec sp_executesql @sql

    The TRIM was not working (understandably) and WITH INIT was never going to work because the whole point of this script was to give the backups different names, not to overwrite the backup file each time with your new backup.

    If you do use this altered script, be very careful when copying and pasting as I have a feeling that the quotes (') might get changed in translation.

    This runs each evening as we use a third-party solution to take off-site backups continuously and therefore local backups are more for allowing faster restores if necessary ... generally on test systems.

    I hope this was helpful, but do post any issues that you might have when using it.

    NOTE: The reason that I have gone for thsi approach is that I am getting increasingly annoyed with maintenance plans falling over for no apparent reason.

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Look at SQLMaint.exe too, pretty handy.

  12. #12
    Join Date
    Jul 2009
    Posts
    4
    Quote Originally Posted by PMASchmed
    Look at SQLMaint.exe too, pretty handy.
    VERY handy - Thanks a lot PMASchmed! I've just added a lot more functionality using this, although getting the syntax right on the first attempt was a little bit challenging.

    Also, for anyone that uses this it's important to add the path to the path environmental variable sdo that it can be run from any location.

    Example:

    • Right-click My Computer > Properties > Advanced > Environmental Variables
    • Add C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn at the end after the semi-colon ... without any spaces and making sure you installed in the same location as me (actually, i installed on E: )!
    • Ok it, then you can run sqlmaint from wherever you need to.


    Just tupe sqlmaint -? for a list of (extremely) useful commands.

Posting Permissions

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