Sql-server – SQL Backup Is Taking All My Hard Drive Space

backupsql serversql-server-2008

I have a job that I run every day to backup my SQL database. I just bought a band new server and was going to transfer the database backup over to the new serer and restore it. Our database isn't as big as most companies, it is only 40 GB in size. We I went to go and copy the backup, I noticed it the hard dive was full that it was stored on and the backup was 900GB in size. I have used the same job on different SQL versions, and the database backup never grew like this.

  • Running Windows Server 2008 R2
  • Running SQL Server 2008 R2 Ent
  • 64 bit

I am not sure if I scripted out something wrong with the job, here is the code for it:

BACKUP DATABASE [DATABASE] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.COMPANY\MSSQL\Backup\DATABASE.BAK' WITH NOFORMAT, NOINIT, NAME = N'DATABASE', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DATABASE' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DATABASE' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DATABASE'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.COMPANY\MSSQL\Backup\DATABASE.BAK' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

(I add the code tags but for some reason they didn't take)

Can someone tell me why my database backup keeps growing? Thanks for the help in advance.

Best Answer

NOINIT = Indicates that the backup set is appended to the specified media set, preserving existing backup sets.

Your backups are being appended. If you don't want the backup to continue growing then you need to use INIT.

http://technet.microsoft.com/en-us/library/ms186865.aspx