March 29, 2011 at 7:45 am
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
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
Thank you very much Kevin.
June 22, 2011 at 8:15 am
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