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.

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 | |
  • 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 '13 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 '13 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 | |

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.