October 1, 2009 at 12:27 pm
I have SQL Server 2005. I have a question regarding deleting old backups. I have schedules 3 jobs that does the following :-
Full backup -- Thursday, Saturday
Diff Backup --every three hours everyday (between 9 am to 7 pm)
Trans backup -- every 1 hour (between 9 am to 7 pm)
Now since we do not have drive space. i always delete all the differentialbackups every day when i get the latest differential backup and also leave all the transactional backups occured after the diff backup.
Now lets say that i have a full backup that was taken on saturday, after that i had diff backup that occured on monday and whole day as i said every three hours means i had 4 diff backups, now again another differential started on Tuesday morning then again i got 4 diff backups. so can i go ahead and delete all the diff backups that occured on all monday as well as the three backups that occured before the fourth backup on tuesday? and i will leave all the transactional backups after the last diff backups.so any suggestions.
October 1, 2009 at 2:26 pm
can someone reply me please?
October 1, 2009 at 2:36 pm
Are you moving/copying the backup files to other storage (e.g. tape, another server, usb drive, etc...)?
If not, then you really need to think about your restore options and what you want to be able to perform. A good example is when your boss comes to you and states that you need to restore the system to Tuesday evening at 4:55pm because at 5pm on that day someone deleted all of the data in a very important table.
So, in that situation - if you have deleted all of the differentials, but have all of the transaction log backups you can restore the last backup and then restore all of the transaction log backups to the requested date & time (using STOPAT).
If you have not deleted all of the differentials (because they are moved to offline storage), you can copy the differential backup taken on Tuesday at 3pm. Then you would restore the backup from Saturday, the differential from Tuesday and the transaction log backups from Tuesday at 3pm through Tuesday at 5pm using the STOPAT qualifier to restore to your point in time.
If you have deleted the differential backups, and deleted the transaction log backups - and they were not copied to offline storage you don't have any options to recover.
Now, try thinking up different scenarios that could happen and make sure you have the ability to recover the system to a point in time within an acceptable time. If that cannot be done, document the limitations and make sure the organization understands those limitations.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2009 at 2:36 pm
Since tlog backups back up transactions since the last full or log backup, you could delete any of the differential backups and still be able to recover to a point in time using the log backups. The advantage of having differential backups is the number of log files you have to restore is reduced.
For example, if you needed to recover to 10:30 AM on Tuesday (I'm assuming you don't do any backups on Sunday):
With differential backups available, you'd restore the full backup made on Saturday, the differential made at 9 AM Monday, and the log backups made at 10:00 AM and at 11:00 AM on Monday.
Without differential backups available, you'd restore the Saturday full backup and every log backup made after that through 11:00 AM Monday.
I'll suggest that you determine if you really need to make differential backups 4 times a day. Maybe you could do a differential once a day, say, at Noon.
Greg
October 1, 2009 at 3:23 pm
Understanding SQL Server Backups
Good article from July 2009 issue of Technet on backups.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply