Post reply

Backup Overwrite

  • November 24, 2012 at 2:56 pm

    #270688

    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

    #1562015

    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.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • November 25, 2012 at 7:44 am

    #1562044

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 25, 2012 at 10:23 am

    #1562053

    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

    #1562149

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 26, 2012 at 6:07 am

    #1562181

    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 Roosevelt

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

  • November 26, 2012 at 4:19 pm

    #1562461

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • November 26, 2012 at 11:46 pm

    #1562516

    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.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • November 28, 2012 at 6:11 am

    #1563073

    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

    #1563158

    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

    #1563185

    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

    #1564493

    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

    #1565189

    Powershell

  • December 5, 2012 at 8:02 am

    #1565775

    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