Post reply

Transaction log backup - format or noformat?

  • March 29, 2011 at 7:45 am

    #233931

    Hi,

    I'm using the following code to backup the transaction logs to unique filenames. I'm unclear, even after reading the BOL if I should be using the option format or noformat. Can someone help me understand this?

    DECLARE @DBName varchar(255)

    DECLARE @DateStamp VARCHAR(50)

    DECLARE @Path VARCHAR(255)

    BEGIN

    SET @DBName = 'YourDatabaseName'

    SET @DateStamp = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '') + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')

    SET @Path = 'C:\BACKUP\'

    SET @Path = @path + @DBNAME + '_'+ 'LOG'+'_' + @DateStamp + '.trn'

    BACKUP LOG @DBNAME

    TO DISK = @Path

    WITH

    NOFORMAT, INIT, SKIP, NOREWIND,

    NOUNLOAD, STATS = 10

    END

    GO

    Thanks.

    Brian

  • March 29, 2011 at 8:45 am

    #1304925

    From BOL

    NOFORMAT

    Specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. This is the default behavior.

    FORMAT

    Specifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

    Seems similar to formatting a disk.

    If you use NOFORMAT (the default) you can write multiple backups to the same file.

    If you use FORMAT any existing contents get erased.

    NOFORMAT is like adding files to a disk

    FORMAT completely wipes the disk

    If you are creating a brand new file for each backup the effect of either is the same for you.

    ---------------------------------------------
    If data can screw you, it will; never assume the data are correct.

  • March 29, 2011 at 8:54 am

    #1304932

    Thank you very much Kevin.

  • June 22, 2011 at 8:15 am

    #1342417

    one question:

    what is the mean of "use it carefully"...

    makes unusable older backups?

    affects all backups in the directory?

    affects differential backups

    please, because if all is false, i prefer to use FORMAT (doesnt use multiple files backup)

Viewing 4 posts - 1 through 3 (of 3 total)

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