Post reply

Help in creating Transactional log backups

  • January 4, 2007 at 10:23 am

    #681260

    In either case you will have to apply the logs individually.  When doing restores I generally do not use the wizard.  I will restore with norecovery until I have applied the logs that I need for recovery.   The restore headeronly command will list the backup sets in the file and the relative position.  Then you would use that position one after another to restore to point in time.  Once again your managing one file with multiple backups taken to it and you don't ever have to delete once you move and overwrite the previous days.  I would try it out on the northwind database so your comfortable with it.  It is the same principle as writing everything straight to tape.  Same device multiple files.

    Tom

  • January 4, 2007 at 10:42 am

    #681265

    Easier than trying to do date string manipulation in TSQL is to use the SQLMAINT utility.  You can specify to do TLog backups (-BkUpLog), remove old backups (-DelBkUps), and it will write each backup to separate files, timestamping each file name just as a maint plan would do it.  You do not need to make a maintenance plan, and you don't run into "portability" issues among different users running Enterprise Manager remotely.

    Hope this helps



    Mark

  • January 4, 2007 at 10:43 am

    #681266

    Thanks Tom for the information. I would try it out and would get back.

    Mark -

    can you please brief me as to how to do with SQLMAINT Utility? Thanks.

     

  • January 4, 2007 at 11:33 am

    #681284

    No problem. 

    1. Creating your job to schedule and perform this backup.
    2. Create a jobstep.  Set Type either to

      • TSQL and use "Exec xp_sqlmaint..", or
      • CMDEXEC and use sqlmaint. 

    Parameters are the same in both cases, and are well documented in BOL (look in the index for "sqlmaint").  Here are a couple examples, I've set the parameters close to what I think you want, but reveiw BOL to be sure.

    TSQL:

    EXECUTE master.dbo.xp_sqlmaint N'-D MyDB -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2days -BkExt "TRN"'

    CMDEXEC:

    sqlmaint -D MyDB -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2days -BkExt "TRN"

    Hope this helps



    Mark

  • January 4, 2007 at 11:40 am

    #681286

    Thanks Mark for your reply. i would try it out.

Viewing 5 posts - 16 through 19 (of 19 total)

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