CODEWITHSUNDEEP
sql-server-2008database-backupstransaction-log
diegol
diegol

Reputation: 47

SQL Server backup file growing excessively

I am a software developer with not much expertise on SQL Server other than the basics.

On a daily basis, using the SQL Server Agent I back up the databases using the following command for each of the databases:

BACKUP DATABASE [DatabaseName] 
TO DISK = N'D:\SQLBackups\DatabaseName.bak' 
WITH NOFORMAT, NOINIT, 
     NAME = N'DatabaseName-Full Database Backup', SKIP, NOREWIND, 
     NOUNLOAD, STATS = 10

After running this command for several days, the size of the .bak file has reached 65 GB and growing.

The database files on the DATA directory are 0.92 GB for the .mdf and 1.2 GB for the .ldf one. And the database is set to full recovery mode.

Database engine is SQL Server 2008 R2 (version 10.50.2500.0)

If I create a full back using the SQL Server Management Studio interface, it occupies 0.93 GB.

Could somebody please explain how it is possible to reach such a big .bak file (65 GB) compared to manually creating a single full back up file?

Upvotes: 1

Views: 2667

Answers (1)

marc_s
marc_s

Reputation: 755361

The NOINIT option keeps appending data to the single .bak file. Use this statement to replace the existing .bak file:

BACKUP DATABASE [DatabaseName] 
TO DISK = N'D:\SQLBackups\DatabaseName.bak' 
WITH NOFORMAT, INIT, 
     NAME = N'DatabaseName-Full Database Backup', SKIP, NOREWIND, 
     NOUNLOAD, STATS = 10

All the details can be found in the official MSDN SQL Server Books Online documentation for the BACKUP DATABASE command....

Upvotes: 6

Related Questions