Backup Overwrite

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Backup Overwrite

14 posts, Page 1 of 2

1 2 »»

Backup Overwrite

Rate Topic

Display Mode

Topic Options

Author

Message

sqlquery-101401

Posted Saturday, November 24, 2012 2:56 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, Visits: 354

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?

Post #1388336

Vedran Kesegic

Posted Saturday, November 24, 2012 7:23 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1388357

Jeff Moden

Posted Sunday, November 25, 2012 7:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:57 PM
Points: 32,893, Visits: 26,771

sqlquery-101401 (11/24/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1388398

sqlquery-101401

Posted Sunday, November 25, 2012 10:23 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, Visits: 354

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

Post #1388411

Jeff Moden

Posted Monday, November 26, 2012 4:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:57 PM
Points: 32,893, Visits: 26,771

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

Post #1388534

Grant Fritchey

Posted Monday, November 26, 2012 6:07 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1388571

ScottPletcher

Posted Monday, November 26, 2012 4:19 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763

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)
One man with courage makes a majority. Andrew Jackson

Post #1388892

Vedran Kesegic

Posted Monday, November 26, 2012 11:46 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1388972

P Jones

Posted Wednesday, November 28, 2012 6:11 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:58 AM
Points: 513, Visits: 1,014

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!

Post #1389693

anastke

Posted Wednesday, November 28, 2012 7:49 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 6:14 AM
Points: 81, Visits: 1,515

can you afford only having one full plus differential and/or incrementals until
additional backup space is installed?

Post #1389789

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions