backup with init

Question

backup with init

Martin Pionk

Old Hand

Points: 394
More actions
July 6, 2006 at 2:24 pm

#153075

I'm having a problem with a t-sql backup command. The sql specifies a backup to disk with a specific path and "with init", yet on subsequent backups using the same command it is appending. This is working on one server (a client test server) but not their production server.
We deleted the backup file so that the nightly task would create it new. The next night, the backup was appended, even though "with init" was specified.
Any idea why this is happening?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Make sure you use the EXPIREDATE or RETAINDAYS options in all of your backups. WITH INIT will only overwrite the backup if it is expired or past the retention days allowed.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Martin Pionk Old Hand Points: 394 More actions · Answer 2

Interestingly, we never specified these options. Are they "on" by default?

The command we use is:

BACKUP DATABASE ETLReportMart TO DISK = 'c:\DataHubBackup.bak' with init

John Rowan SSC Guru Points: 56440 More actions · Answer 3

From BOL: "If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic."

Have them check their setting for Media Retention (sp_configure 'Media Retention').

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Martin Pionk Old Hand Points: 394 More actions · Answer 4

Thanks for the information. It turns out that the problem is not what we initially thought. The backups are not appending, the file is initially being created very large (45GB). The database that is being backed up is about 10GB. I deleted the backup file, changed the file name in the task, and executed the backup command again. The new file was created at 45GB and only contains one backup of the 10GB database. I restored the database to a test server, executed a backup command there, and the file size is about 10GB. Any ideas on why/how the production server creates the initial backup file at 45GB?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Check the size of your database in production, including the transaction log. Not sure, but SQL Backup may be making an assumption based on the physical size of the database. I haven't really looked at this on our systems. Let us know what you find.

Lynn