1

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.

Improve this question

3 Answers 3

Reset to default
3

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

Improve this answer
2
  • Question: If I use INIT instead of NOINIT, the back will be over written. So instead of just backing up what has changed, it backups the whole database again?
    – nate
    Dec 6, 2013 at 17:02
  • Yes, but that's happening anyway. It's just sticking the full on the end of the last full. If you only want changes, you need to take differentials.
    – Katherine Villyard
    Dec 8, 2013 at 0:41
1

Its appending to the end of it every time it backs up.

Improve this answer
0

As DanBig and joeqwerty said, your backups are appending to the same file. If you want to retain versions without appending or overwriting, create individual dated files. For example:

Declare @DBname varchar(255),
        @SQL nvarchar(max), 
        @today nvarchar(50),
        @servername nvarchar(50)

set @today = convert(nvarchar,GETDATE(),12)  
set @servername = REPLACE(@@SERVERNAME,'\','-')
set @DBName = 'YourDB'

set @SQL='BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:\some backup path\' + @servername + '-' + @DBName + '-' + @today + '.bak'' WITH FORMAT, INIT,  NAME = N''Full ' + @DBName + ' Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
EXEC SP_EXECUTESQL @SQL
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .