October 8, 2008 at 10:01 am
Hi, I am new for database backup. our existing backup plan is full DB and transaction log backup every night. The DB backup is immediately after transaction log backup The purpose of transaction log backup is to truncate the log. So far, we don't has any performance issue. our database has grown from 7 gb to 12 gb in a year. our transaction log is 6 gb now.
from what I read about DB backup, it suggests to full DB backup twice a day and transaction log backup every 2-4 hours. DB backup and transaction log backup scheduled at different time.
so database can be restored to a point of failure.
with our existing plan, if I do log tail backup when disk failed,
can I still be able to restore the DB to a point of failure using full DB and log tail backup? what is risk of our exiting maintenance plan?
Also, how often, should I backup system database(master,msdb,temp). if database need be restored, should I restor them also. what would be lost if no system database is restored? for example, when I moved production DB to develop DB, I was told just restore DB itself without system DBs.
Thanks
October 8, 2008 at 10:15 am
susan smith (10/8/2008)
Hi, I am new for database backup. our existing backup plan is full DB and transaction log backup every night. The DB backup is immediately after transaction log backup The purpose of transaction log backup is to truncate the log. So far, we don't has any performance issue. our database has grown from 7 gb to 12 gb in a year. our transaction log is 6 gb now.
Basically in this scenario you might as well be in SIMPLE recovery mode as you have no log backups throughout the day to restore to.
from what I read about DB backup, it suggests to full DB backup twice a day and transaction log backup every 2-4 hours. DB backup and transaction log backup scheduled at different time.
so database can be restored to a point of failure.
This depends on how much data you can afford to lose and how long you want restores to take. I typically would do at most a daily full backup, differential backups a couple of times a day, and hourly tx log backups. You may need more or less.
with our existing plan, if I do log tail backup when disk failed,
can I still be able to restore the DB to a point of failure using full DB and log tail backup?
If the disk failed how can you do a log tail backup? The disk is not available to backup from. Are your backups on a different disk?
what is risk of our exiting maintenance plan?
If the disk fails you ONLY have the full backup from the night before to restore from. You have no TX Log backups after that point so if the disk fails at 4:30 pm you lose EVERYTHING done from the last backup to that point.
Also, how often, should I backup system database(master,msdb,temp).
I backup the system databases weekly. You should also do a backup whenever you add a database, job, or maintenance plan.
if database need be restored, should I restor them also. what would be lost if no system database is restored? for example, when I moved production DB to develop DB, I was told just restore DB itself without system DBs.
If the server itself goes then Yes you do need to restore the system databases, but if the problem is only with a user database you only need to restore the user database. When moving a database from production to test/dev you only need to restore the user database.
I hope this helps. Backup and recovery plans really depend on the database use and business needs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2008 at 2:00 pm
Thanks. It make sense.
one more question, when you do weekly system DB backup. do you also backup transaction log? how often it need be?
October 8, 2008 at 2:57 pm
The system DBs default to simple recovery mode on install so you don't need log backups for them. A full backup once a week should suffice.
-- You can't be late until you show up.
October 8, 2008 at 3:56 pm
Where I'm working now, for the non-system databases, we typically do transaction log backups every 30 minutes; differential backups daily; and full backups weekly.
We do a full backup of system databases nightly. This might be overkill, but it works.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 8, 2008 at 4:10 pm
Better to backup too much than not enough.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2008 at 6:16 am
Jack Corbett (10/8/2008)
Better to backup too much than not enough.
Amen to that. How many times have we seen "I have corruption but no backup"? Or "my data is gone and I have no backup"? Not to say you cannot backup a corrupted database but proper maintenance and a solid backup scheme can really save you a lot of headaches - and maybe even your job.
-- You can't be late until you show up.
October 9, 2008 at 6:42 am
Thank you for all. It is very helpfull.
I set up backup job via maintenace wizard in sql 2005. but my old backup can't be erased even I select overwrite option and set backup set will be expired in one day.
October 9, 2008 at 6:57 am
A couple comments.
1. Don't overwrite yesterday's backup with today's. Use a new file everyday. Here's the reason. The overwrite occurs first and if the backup fails for some reason, you've lost yesterday's and today's. Yesterday's is better than nothing.
2. Don't use sets. Putting all the backups into one file causes issues. If you lose the file for some reason, you lose all backups. Separate files.
3. Open the maintenance plan and check the cleanup task. If you set the plant o clean up after one day, it should work. It's possible because of timing that one day (24 hours to the second) might not have elapsed. I'd watch it for 2 days before I got worried.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply