Advanced backup solution - how do we make sure we dont delete the wrond backup file?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Advanced backup solution - how do we make...

Advanced backup solution - how do we make sure we dont delete the wrond backup file?

Rate Topic

Display Mode

Topic Options

Author

Message

ksj 60318

Posted Wednesday, December 12, 2012 1:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:24 AM
Points: 27, Visits: 60

Basic :

We take a full backup (BAK) once a week, a differential backup every days (not the day we make the full backup), a transactional backup every hour.

Full backup (BAK) files gets deleted if older than 6 days
Differential backup (DIF) files gets deleted if older than 22 hours
Transaction log backup (TRN) files get deleted if older than 22 hours.

Problem :

We want to make sure that we dont delete a TRN file, that does not have a corresponding BAK/DIF file, and we dont delete a DIF file that does not have a corresponding BAK file.

Solutions tried / looked into

We tried to take a look at Log sequence number (LSN), but since we dont overwrite existing backup sets, when we make a full database backup, we cant use that.

Question :

Anybody have a solution, because I have seached a lot on the internet and found nothing.

Post #1395504

ksj 60318

Posted Friday, December 14, 2012 1:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:24 AM
Points: 27, Visits: 60

nobody does this?

So you guys just delete your backup files and 'hope' you have everything or do you overwrite existing backup sets with every full backup?

Post #1396517

Jason-299789

Posted Friday, December 14, 2012 1:53 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122

Most companies run the SQL backups then have the backups committed to Tape or transfered to off site repositories.

Previous employers I've worked with have then generally run quarterly DR tests on non-prod servers to ensure they have a position to go back to and how long the business would be without mission critical systems if such a situation occured.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1396518

ksj 60318

Posted Friday, December 14, 2012 3:16 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:24 AM
Points: 27, Visits: 60

Sorry I can see that I forgot to mention everything Sad

The backup mentioned above is a daily backup taken to a local drive, after the local backup a third party backup software ship the backup files to an off site. So we do indeed have backup for more that a week.

Hope my question makes more sense now :)

Post #1396548

« Prev Topic | Next Topic »

Permissions