Backup database command in T SQL - backup size is increasing

Question

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?

V. Bozz · Accepted Answer · 2025-02-05 09:32:22Z

3

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 backups
NOINIT: do not overwrite and append instead

If 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):

https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16#-noinit--init-

edited Feb 5 at 9:32

V. Bozz

1,6361 gold badge23 silver badges42 bronze badges

answered May 23, 2016 at 6:23

BytesOfMetal

73411 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Backup database command in T SQL - backup size is increasing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related