set @TSQL= 'BACKUP DATABASE ['+@DBName +'] TO DISK = N'''+@FilePath+'\'+@DBName+'_backup_'+@Date+'.bak'' WITH NOFORMAT, NOINIT, NAME = N'''+@FilePath+'\'+@DBName+'_backup_'+@Date+''', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10'
I have the above T SQL statement to backup my database. On running the same statement twice, the previous backup set is overwritten and the size of the backup is increased. I am confused between the interaction of NOINIT and SKIP in the above statement.
According to MSDN:
NOINIT: Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media.
However i have noticed my backup size is increasing exponentially on taking backup of the same database a second time.
Can anyone explain how this command will function?
Inside a .bak file you can have more than one backup, so basically what you set when specifying:
INIT: overwrite the full .bak file (which is a backup set not a single backup) or just put your backup inside it with the previous backupsNOINIT: do not overwrite and append insteadIf you use INIT, try to restore the backup and you'll see that you can choose a specific backup from your backup set.
More options:
SKIP: checks the expiration date of the backup before overwriting it, in case you choose to do it instead of appending the backups after each other into the .bak file.More details here (I guess you already visited it, but just in case):