3

I am regularly backing up the database using the SQL Server UI (Tasks --> Backup) with Backup Type:Full. Each time I backup, the .bak file gets bloated. If I delete the .bak file and do the backup, then 50 MB .bak file is created instead of the 500 MB file that I deleted.

What is the reason behind this?

  • 6
    You're writing additional backups into the same file. – Erik Darling Jul 26 '17 at 17:55
8

You are appending the backup into same file so it is actually accumulating the backups. You need to give a new name each time you trigger backup from Tasks->Backup.

Use maintenance plan as it has timestamp for each backup file or else go with Ola Hellengren's solution.

https://ola.hallengren.com/

|improve this answer|||||
6

Backing up a database involves quite a few parameters, even if you let SQL Server Management Studio do it with your assistance or if you schedule a Maintenance Plan to do it for you.

The easiest solution is to verify that the following parameter is set, if you are using the same filen name for every full database backup:

INIT

You Backup command might look similar to this:

BACKUP DATABASE [Your_DataBase] 
TO DRIVE = 'G:\Backup\Full\Your_Database_Full.bak' 
WITH NO_COMPRESSION, 
RETAINDAYS = 7, 
NOFORMAT,
NOINIT,
NAME = 'Name you gave your backup',
SKIP, 
NOREWIND,
NOUNLOAD,
STATS = 10,
CHECKSUM

The default BACKUP command will use the NOINIT option, which is defined as follows:

NOINIT | INIT
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 (NOINIT).

NOINIT
Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

INIT
Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit.

Reference: BACKUP (Transact-SQL)

Solution Nr. 1

Changing the NOINIT parameter to INIT will initialise the backup file each time you perform a backup with the same filename and will overwrite the contents of the backup file.

Your backup comamnd should then look like this:

BACKUP DATABASE [Your_DataBase] 
TO DRIVE = 'G:\Backup\Full\Your_Database_Full.bak' 
WITH NO_COMPRESSION, 
RETAINDAYS = 7, 
NOFORMAT,
INIT, -- << Important change
NAME = 'Name you gave your backup',
SKIP, 
NOREWIND,
NOUNLOAD,
STATS = 10,
CHECKSUM

Solution Nr. 2

As pointed out by @RamakantDadhichi, changing the filename to be unique by adding the date and time can help prevent multiple backups in one file.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.