May 16, 2006 at 5:15 pm
Greetings, I have created a maintenance plan which is backing up all the system databases, master, model, msdb. I am curious as to suggestions re best practices for the following questions,
1) What factors should be taken into account in determining whether this should be done daily, weekly or at some other interval?
2) What conditions should be taken into account in determining which optimizations to perform, Reorganize Data, Update Statistics and Remove unused space from database files?
3) Is it helpful to check database integrity for the system databases?
4) Is it recommended to back up the transaction logs for these databases?
Thanks.
May 17, 2006 at 5:57 am
Hi Bill,
Please find my ans in the lines marked with ***
) What factors should be taken into account in determining whether this should be done daily, weekly or at some other interval?
*** Mostly system databases will not be modified as frequently as user databases. So depending on its modification frequency u can do it daily, weekly. Prefer doing it weekly basis for less critical application & daily on mission critical servers.
2) What conditions should be taken into account in determining which optimizations to perform, Reorganize Data, Update Statistics and Remove unused space from database files?
*** Same as above
3) Is it helpful to check database integrity for the system databases?
*** YES ofcourse.
4) Is it recommended to back up the transaction logs for these databases?
*** not required if full database is done on daily basis.
May 17, 2006 at 7:35 am
On #4.
If your database has a lot of use you might consider doing transaction log backups.
A couple of my databases have heavy use. I do hourly transaction logs. So if something happens I can always restore and only lose a maximum of an hour's work. I know some people that do them every 4 hrs. Just depends on how much work it would be to re-enter the lost data.
May 17, 2006 at 8:23 am
How much data can you afford to lose? If you can afford to lose several hours or days worth of data - then don't do transaction log backups.
Only transaction log backups allow you to do a 'point-in-time' restore.
Let's say you do a full backup daily (midnight), a differential backup every six hours (0500, 1100, 1700, 2300). Now a user dropped a table at 0930. OOPPS. You restore the Full Backup using WITH NORECOVERY. That allows you to do additional restores. So what Differential do you restore? You can only do the 0500 one and you lose 5 hours of data.
Now, lets say the same schedule, but you also do an hourly transaction log backup. You now would immediately backup the active transaction log. Restore the Full Backup using WITH NORECOVERY, restore the 0500 Differential WITH NORECOVERY and restore the 0600, 0700, 0800, 0900, logs WITH NORECOVERY and then the latest log backup (presumably before 1000) using WITH STOPAT and supplying a time prior to the bad command. ('2006-05-17 09:29' for example).
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply