April 12, 2002 at 4:06 pm
I have posted a similar message earlier but want to know if this can be done using TSQL or not. I am wanting to backup my database every hour on the hour. I then want to back up my transaction logs every 15 minutes starting at the 10 minute mark. Meaning I would have a full backup at 12:00 and transactiong log backups at 12:10, 12:25, 12:40 and 12:55. If I wanted to restore back to 12:40, and my 12:10 and/or 12:25 transaction log backup was corrupted, how can I restore the 12:40 transaction log backup. I know that I need to keep the transaction log after every backup. If this can be done, what are the commands when doing the full backup and transaction log backups. If this is not possible, what methods are others using.
April 12, 2002 at 5:02 pm
Jeff, it one of you transaction log backups is bad, you're in trouble. You'd end up being able to restore up through the final good one and you'd lose any changes made after that. Between BOL and our script library you should be able to find plenty of material about the syntax for the backups. The maintenance plans are a great way to start until you're comfortable with the TSQL, it has options to do both full and transaction log backups at whatever intervals you need. A nice plus for doing it that way is you can set it to remove old backups after x days.
Andy
April 15, 2002 at 8:16 am
If I loose one transaction backup and I am in trouble, how do others handle this? From what I understand, most DBAs do a full backup once a day and transaction log backups through out the day. How do they handle their disaster recovery? Does this not leave a potential big hole if one of the transaction log backups is corrupted or lost? Thanks in advance for all inputs.
April 15, 2002 at 9:58 am
It does leave a potential gap. I fall into that category, full every night, then sporadic log backups during the day based on usage. Have to weigh the risk of a corrupt back up versus how often they get used. I think if you want it guaranteed you should look at log shipping, that way you know the backup is good. So far I've never had a corrupt back up file though.
Andy
April 15, 2002 at 10:08 am
Alot of us who are concerned with specific databases becoming corrupt and unusable usually also do a transactional replication to copy the data to another site for safety sake.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 15, 2002 at 10:57 am
You can also use differentials to minimize your risk. If you made a 12:30 differentials, then the corrupt 12:25 would not matter. You'd do the 12:00 full, the 12:30 diff, and the 12:40 log.
There is not much you can do here. What if your 12:00 backup gets corrupted? Another hole. There is no way to avoid this, any solution, including log shipping, replication, etc. will have some risk as well and there may be data loss.
The SQL backup is pretty solid. I test it regularly and haven't had issues. That is another reason I avoid 3rd party tools. I run backups to disk, copy to antoher disk and get on tape. Gives me 3 places in 24 hours from which I can restore.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply