Stopping backup files from being datetime stamped

Question

Post reply

Stopping backup files from being datetime stamped

wawork

SSC Rookie

Points: 29
More actions
September 29, 2010 at 1:33 pm

#224563

The Maintenance Plan Wizard is very cool but I've run into an issue. I chose the option to do all system databases. But with this choice each time it runs it creates a new datetime stamped backup file. But I need it to overwrite the existing backup.
What am I missing?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 1

I don't believe this is possible. Even when calling SQLMaint utility directly it doesn't seem that this can be done.

http://msdn.microsoft.com/en-us/library/ms162827.aspx

You could possibly do some post-processing after the maintenance plan is run to rename the file.

If you tell us why you don't want datetime stamped files maybe we can suggest some alternatives.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Normally, you do not want to overwrite a prior backup, because it leaves you without a backup file if something goes wrong while you are running a backup.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Michael Valentine Jones (9/29/2010)
Normally, you do not want to overwrite a prior backup, because it leaves you without a backup file if something goes wrong while you are running a backup.

This

The only way to overwrite your backup would be to set up your own job to do it, which is how I typically do things. We have a tape backup that runs every night and stores backups to tape so overwriting it is not a huge deal...I can just restore from tape if needed.

If you don't want the clutter, add a cleanup task that will delete backups older than 2 days or so after the backup task runs.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Derrick Smith (9/29/2010)
Michael Valentine Jones (9/29/2010)
Normally, you do not want to overwrite a prior backup, because it leaves you without a backup file if something goes wrong while you are running a backup.
This
The only way to overwrite your backup would be to set up your own job to do it, which is how I typically do things. We have a tape backup that runs every night and stores backups to tape so overwriting it is not a huge deal...I can just restore from tape if needed.
If you don't want the clutter, add a cleanup task that will delete backups older than 2 days or so after the backup task runs.

I prefer to have the backup on disk. Most of the time, I want to restore from the most recent backup, and I need to do it right away.

Restoring from tape can be very slow for a lot of reasons:

Tape is in use.

All Tape drives are in use.

Tape is offsite.

You need an admin to restore the backup file from tape before you can restore.

Tape backup didn't actually run.

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Michael Valentine Jones (9/29/2010)
Derrick Smith (9/29/2010)
Michael Valentine Jones (9/29/2010)
Normally, you do not want to overwrite a prior backup, because it leaves you without a backup file if something goes wrong while you are running a backup.
This
The only way to overwrite your backup would be to set up your own job to do it, which is how I typically do things. We have a tape backup that runs every night and stores backups to tape so overwriting it is not a huge deal...I can just restore from tape if needed.
If you don't want the clutter, add a cleanup task that will delete backups older than 2 days or so after the backup task runs.
I prefer to have the backup on disk. Most of the time, I want to restore from the most recent backup, and I need to do it right away.
Restoring from tape can be very slow for a lot of reasons:
Tape is in use.
All Tape drives are in use.
Tape is offsite.
You need an admin to restore the backup file from tape before you can restore.
Tape backup didn't actually run.

If you need the most recent backup, you don't have to go to tape..which like you said is the case 99.5% of the time. My daily backups run between 10pm and 2am, and then then get copied to tape around 3-4am. The previous day's backup stays available for the entire day until the next night's backup starts around 10.

I also do not have enough space on the SAN to triple my backup sizes..I wouldn't even have enough free space to store two full days worth.

wawork SSC Rookie Points: 29 More actions · Answer 6

emily-1119612 (9/29/2010)
If you tell us why you don't want datetime stamped files maybe we can suggest some alternatives.

The system databases are all small but we are a GIS shop so we have some very large databases.

Our backup software is Tivoli by IBM. We have to pay for storage by the disk/tape volume. The Backup Group has configered the system to maintain the last 5 versions of a file including bak files. If the backup file has a new filename generated each day then they will never age and the space they take up can never be reclaimed. That is the only reason I don't want to go with unique file names even on the small databases.

Thanks

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Derrick Smith (9/29/2010)
Michael Valentine Jones (9/29/2010)
Derrick Smith (9/29/2010)
Michael Valentine Jones (9/29/2010)
Normally, you do not want to overwrite a prior backup, because it leaves you without a backup file if something goes wrong while you are running a backup.
This
The only way to overwrite your backup would be to set up your own job to do it, which is how I typically do things. We have a tape backup that runs every night and stores backups to tape so overwriting it is not a huge deal...I can just restore from tape if needed.
If you don't want the clutter, add a cleanup task that will delete backups older than 2 days or so after the backup task runs.
I prefer to have the backup on disk. Most of the time, I want to restore from the most recent backup, and I need to do it right away.
Restoring from tape can be very slow for a lot of reasons:
Tape is in use.
All Tape drives are in use.
Tape is offsite.
You need an admin to restore the backup file from tape before you can restore.
Tape backup didn't actually run.
If you need the most recent backup, you don't have to go to tape..which like you said is the case 99.5% of the time. My daily backups run between 10pm and 2am, and then then get copied to tape around 3-4am. The previous day's backup stays available for the entire day until the next night's backup starts around 10.
I also do not have enough space on the SAN to triple my backup sizes..I wouldn't even have enough free space to store two full days worth.

That was my point. You don't have any backup on disk from 10 pm to 2 am, which is one sixth of each day, so you are just gambling that there will be no problems during that time period

Not sure where you get "triple my backup size". You only need enough extra space to hold your largest database backup. Since this is SQL 2008, backup compression is available in Enterprise edition, or if you are using 2008 R2, backup compression is available in Standard edition if disk space is really an issue.

Steve T SSCommitted Points: 1994 More actions · Answer 8

While I agree with the other posters that you should have more than 1 backup...

But should you want to continue..

In the maintenace plan, if you choose

Backup to databases across one or more files... this will allow you to specify a specific file name without the date/time stamps.

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

Michael Valentine Jones (9/30/2010)
That was my point. You don't have any backup on disk from 10 pm to 2 am, which is one sixth of each day, so you are just gambling that there will be no problems during that time period
Not sure where you get "triple my backup size". You only need enough extra space to hold your largest database backup. Since this is SQL 2008, backup compression is available in Enterprise edition, or if you are using 2008 R2, backup compression is available in Standard edition if disk space is really an issue.

A lot of our servers are on 2005, and at my previous company, the vast majority were still on 2000. I used Litespeed for compression anyway, but even still, I did not have enough space on my SAN to take a full second backup before I deleted the first. Yes, poor infrastructure, but there wasn't much I could do about it..our actual storage appliance itself had every tray used and completely full of disks.

If something happened between 10-2 and I had already started overwriting the backup, then I would pull from tape and restore with transaction logs from there. The only difference between your situation and mine is that you would have the database copy available on the SAN to restore from, and I would have to copy from tape. There is extra time involved, but the cost of having an additional 5+tb of raid-5 storage outweighed that potential risk.

I don't know of any companies were SAN storage is NOT an issue. It's so damn expensive and budgets have been tight for the last couple years as it is.

Steve T SSCommitted Points: 1994 More actions · Answer 10

You might want to look at lower performance drives. I know we use SATA LUNS for stuff that people don't care about at least in performance and they just need space. You wouldn't want to backup to this SATA LUN as now you would have a performance issue, but to move the backups so you have a better backup and restore schema.