Backup file name

Question

Backup file name

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

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

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 1

Maybe I don't understand the question, but shouldn't you just modify the process that is running the backups so that it overwrites the file each night instead of generating a new backup?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

johnl 80306 Grasshopper Points: 12 More actions · Answer 2

Well we do delete the previous night's file just before the new dump file is created. The issue is that we are going to start utilizing an off site or over the wire backup system and because of this we need the date removed from the backup file name per their request. I was hoping there was a quick and easy way to generate the file nightly without the date in the file name.

Steve Jones - SSC Editor SSC Guru Points: 739545 More actions · Answer 3

I am wary of backing up to the same file every time. It is asking for an issue. What if the tape failed, you didn't fix things, and your backup tonight runs out of disk space? You'll have started by killing yesterday's backup? That is the type of stuff Mr. Murphy loves.

What if someone does an ad hoc backup?

I would recommend you stick with a new file every night and use a cleanup process to remove old ones AFTER the new backup is done.

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 4

johnl 80306 (1/7/2011)
Well we do delete the previous night's file just before the new dump file is created. The issue is that we are going to start utilizing an off site or over the wire backup system and because of this we need the date removed from the backup file name per their request. I was hoping there was a quick and easy way to generate the file nightly without the date in the file name.

But you can (not saying you should, I agree with Steve)

BACKUP DATABASE TO DISK = 'MyBackup.bak' WITH INIT

That's going to overwrite the same file, every time. That's where my confusion is coming from, just modify your backup process.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

johnl 80306 Grasshopper Points: 12 More actions · Answer 5

Thanks.

FYI tape is secondary we are also utilizing a SAN that replicates all files changes to our DR site SAN.

So I feel it's safe deleting the dump file before the next dump file as I can retrieve the file from the DR if necessary.