sqlsql-servert-sqlssis

Backup database command in T SQL - backup size is increasing


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?


Solution

  • Inside a .bak file you can have more than one backup, so basically what you set when specifying:

    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:

    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-