Backing up Log Files

deweigenddeweigend Posts: 20
edited September 22, 2005 11:58AM in SQL Backup Previous Versions
I am switching from SQL LiteSpeed to your product. With SQL Litespeed I was able to append to the Log backup file by passing an init switch to the backup stored procedure. I would like to do this same thing with SQL Backup but I have been unable to figure out to do this from the help system or from the knowledge base or FAQ on your website. I think that the INIT parameter for the backup command will do what I want but there is no documentation that explains what the INIT parameter does. Is this the correct parameter to use to do what I want or is there another parameter that I should use?

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup files are standalone i.e. one file can contain only 1 backup, be it a full, differential or log backup.

    The INIT parameter basically tells SQL Backup to overwrite any existing backup files of the same name.

    In most scenarios, trx log backups are appended to each other so that it is easy to identify the sequence during the restore.

    In SQL Backup, each trx log backup resides in its own file. Which this might look cumbersome to restore, bear in mind that SQL Backup has the ability to analyze a group of trx log backups, determine the sequence in which to restore them, and perform the restore correctly, via a single command e.g.

    RESTORE LOG pubs FROM DISK = 'E:\Backups\*_log.sqb'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks for the reply.

    My log backup job runs every two hours from 4 am until 12 am. So with SQL Backup I will create a new file each time this job runs. I don't suppose there is an option to make SQL Backup add something to a backup file name so they will be unique? Ideally I would like to have a rotating set of files that will get reset with the 4 am log backup then I will not have to worry about deleting old log backups once I take a new full backup. Not being able to append to a single log backup file throughout the day is definitely a drawback to an otherwise very good product. Is this function being considered for future releases of SQL Backup, if not, how can I get it on the list.

    Donald Weigend
  • peteypetey Posts: 2,358 New member
    One way may be to construct the filename dynamically based on the hour, and backup to this file using the INIT option e.g.

    DECLARE @filename varchar(128)
    SET @filename = 'E:\Backups\pubs_log_' + (SELECT CAST(datepart(hh, getdate()) AS varchar(2))) + '.sqb'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thank you for the tip. I was thinking along the same lines as you.

    I think my best option is to embed the hour in the same of the file and use the INIT option for each backup run. This should give me a set of 10 log backup files that are reused from day to day (backing up every 2 hours from 4 am until 12 am).

    Thank you for your help on this.

    Donald Weigend
Sign In or Register to comment.