Feature Request - Allow a COPY_ONLY backup to be registered

EdVassieEdVassie Posts: 15
edited November 24, 2008 1:04PM in SQL Backup Previous Versions
It should be possible to register a COPY_ONLY backup so that it can be used in a restore in the same way as a normal backup.

A COPY_ONLY backup does not reset the dirty bits used to control a differential backup, and also avoids all the locking issues associated with resetting these bits. For a large and heavily used OLTP database eliminating this overhead can produce a useful increase in database availability, reduction in the CPU cost of taking the backup, and marginally decrease the time required to make the backup.

This concept has been available for DB2 Mainframe for over 10 years and has been very popular both for OLTP databases and for sites that do not need to include differential backups in their recovery strategy.

Comments

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Thank you for your post into the forum.

    The feature you are seeking already exists within the product, SQL Backup supports the COPY_ONLY argument. However it is not available if you use the backup wizard or schedule backup wizard to create your backup task.

    If you wish to use the COPY_ONLY argument you need to script it, as per the following examples:

    FULL Backup

    This example will perform a Full backup of the AdventureWorks database with COPY_ONLY , using compression level 2, erase backup files older than 5 days old and verify the backup file created.
    DECLARE @errorcode INT
    DECLARE @sqlerrorcode INT
    EXEC master.dbo.sqlbackup '-SQL "BACKUP DATABASE [AdventureWorks] TO 
    DISK = ''C:\Backup\<DATABASE>_<TYPE>_<DATETIME YYYYmmdd_hhnnss>.sqb''
    WITH ERASEFILES = 5, COMPRESSION = 2, COPY_ONLY, VERIFY " '
    IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
    END
    

    Transaction Log Backup

    This example will backup the transaction log of the AdventureWorks database using the COPY_Only argument using compression level 1 and erasing old backup files older than 10 days
    DECLARE @errorcode INT
    DECLARE @sqlerrorcode INT
    EXEC master.dbo.sqlbackup '-SQL "BACKUP Log [AdventureWorks] TO 
    DISK = ''C:\Backup\Log\<DATABASE>_<TYPE>_<DATETIME YYYYmmdd_hhnnss>.sqb''
    WITH ERASEFILES = 10, COMPRESSION = 1, COPY_ONLY " '
    IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
    END
    

    Further advice on possible syntax and arguments available can be found in the SQL Backup Help File ->The SQL Backup Toolkit Section ->Toolkit Syntax ->The BACKUP Command.

    Hope the above answers your question.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.