November 24, 2012 at 2:56 pm
I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited amount of space to accomodate only one backup and since i am using date time stamp ,it alwasy create new backup file, so at a given point of time i will have two backups on a disk before i delete first old backup, as i cant delete old backup unless new backup is successfull.
Is there any way i can overwrite old timestamp backup with new timestamp backup?
November 24, 2012 at 7:23 pm
No, you do not want to overwrite anything, especially not previous backup before the new one is finished successfully.
It is a very strange requirement to hold a backup just one day.
What sizes are we talking about?
You can decrease backup size by using backup compression (enterprise and development editions only),
differential backups, partial backup of only read-write filegroups, and even all three methods combined.
If you can identify read-only objects (e.g. old history and log tables and partitions) and put them into separate read-only filegroup,
you have to backup them only once.
But, the simplest method would be to get the storage space that is appropriate for the requirement.
November 25, 2012 at 7:44 am
sqlquery-101401 (11/24/2012)
I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited amount of space to accomodate only one backup and since i am using date time stamp ,it alwasy create new backup file, so at a given point of time i will have two backups on a disk before i delete first old backup, as i cant delete old backup unless new backup is successfull.
What are you using to do the backups with? Maintenance Plans? And, how many databases are you talking about?
--Jeff Moden
November 25, 2012 at 10:23 am
i am using T-SQL script to take a backup and then delete the previous one as of now ,and getting budget approved might take more time and database i am talking about is approx 5-6 per server and some are over couple of TB.
Thanks
November 26, 2012 at 4:53 am
To answer your original question, you can overwrite the old file but 1) there would be no timestamp involved and 2) it would render the previous backup useless if anything went wrong.
I take it that your T-SQL is working correctly. If you think something isn't quite right with it, post it and we'll take a look. Other than that, it sounds like you're doing things the right way for the full backups.
How are you handling transaction log backups?
--Jeff Moden
November 26, 2012 at 6:07 am
If you just take away the timestamp, as Jeff says, you can overwrite the backup. I'd suggest though, you should be nervous about doing that. If your backup fails during the process, the old backup is gone. I understand that due to space constraints & budgets, you may be stuck here, but I'd fight for some more money for disk space. Explain to the business just what will happen if you can't restore a backup.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 26, 2012 at 4:19 pm
Is the backup in a "safe" location, such as a SAN drive, that can survive if the server itself fails?
If not, you need to copy the backup to an alternate location every day anyway. Then, you can safely delete the local backup before beginning the new backup ... unless the previous copy failed, so you need either a backup log or to rename the backup so tomorrow's backup can tell that the copy failed and that the local backup is the only recent backup for that db.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 26, 2012 at 11:46 pm
Of course you backup location is on physically separated storage, not the one db is located on ? If it is on the same storage, think the worst scenario if that storage fails.
You could reconfigure your backup storage. If your backup storage uses some form of mirroring (RAID1 or mirror in combination with stripping like RAID10 and RAID 01, or some more exotic combination), than you can save considerable space by reconfiguring that storage to RAID5. It will have slower writes than e.g. RAID10, but you will gain more space.
If you can't use backup compression (your sql server is not enterprise version), you could put your backup in a compressed folder.
November 28, 2012 at 6:11 am
You could also add an extra step to the backup jobs to xcopy the .bak file to another network location. If that fails you've still got the original file to manually copy. If it succeeds you could then delete the backup in a further step.
I do one backup and I overwrite it by using a name without a timestamp. Why? Because my disk backups are then backed up to tapes which are rotated daily, weekly and monthly. I also do the copies to a network fileshare which are also taped nightly - belt and braces!
November 28, 2012 at 7:49 am
can you afford only having one full plus differential and/or incrementals until
additional backup space is installed?
November 28, 2012 at 8:17 am
Note if you're on 2008 R2 backup compression is available in Standard edition too.
Depends on your type of data but > 60% space saving isn't unusual.
November 30, 2012 at 10:43 am
Thanks everyone ,appreciate your replies.
another question is ,i am usin xp_cmdshell to delete the old backup files , and for that i need to make a change on all servers to allow updates for xp_cmdshell.
is there any other way to delete the files from SQL that does not need to enable and extended stored proc?
December 4, 2012 at 3:23 am
Powershell
December 5, 2012 at 8:02 am
How often are you doing restores? Can you show a time series that lists which backup will be available when on an hourly basis?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply