April 26, 2010 at 9:18 am
Guys, hi all,
i was wondering if you can help me with the following.
I have my ERP database and i want to create a back up plan, as i describe here. I would like some guidance on how to do this.
The plan i want to create is the following
12.00 am Full Back Up
01.00 pm Tail Log Back Up1
02.00 pm Tail Log Back Up2
03.00 pm Tail Log Back Up3
04.00 pm Tail Log Back Up4
05.00 pm Tail Log Back Up5
06.00 pm Tail Log Back Up6
07.00 pm Tail Log Back Up7
08.00 pm Differential1
09.00 pm Tail Log Back Up1
10.00 pm Tail Log Back Up2
11.00 pm Tail Log Back Up3
12.00 am Tail Log Back Up4
13.00 am Tail Log Back Up5
14.00 am Tail Log Back Up6
15.00 am Tail Log Back Up7
16.00 am Differential2
until 12.00 am where a new Full Back Up will come again and so on.
My question is, do i set it up with maintenance plan, or do i build 3 different buck ups assuming that they talk to each other? On the maintenance plan, I can't find out where i schedule the tail log every hour, and the differencial every 8 hours.
Any step by step / how to tutorial or any advice, is highly appreciated!
Thats all folks!!! 🙂
April 26, 2010 at 10:47 am
Generally approaching something like this I would create three different processes. They won't "talk to each other" but the log backups on care that there has been a full backup and that the database is in full recovery mode. Other than that you can schedule as needed. Same thing with the differential backup. You can't run that until you get a full backup, but then it'll run fine on whatever schedule you want.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2010 at 11:10 am
You don't actually do a "tail log backup" on a schedule, it is a "Log Backup". A tail log backup is done during recovery steps.
You can do this in one maintenance plan, just create "sub plans" that you can do individual task separate from the other sub plans and on different schedules.
You can Google for "SQL Server maintenance plans" and find numerous articles on doing this. You might also look at doing it with scripts (T-SQL code) in that it gives a little bit more control and granularity (at least in my opinion ;-))
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 27, 2010 at 1:54 am
Thank you both for your valuable advice, i will bother you again! 🙂
Do you consider the solution i proposed to be most effective for my ERP system where invoice are beeing issued every minute, or do you have a more efficient maintenance plan for critical ERP - SQL servers?
April 27, 2010 at 5:48 am
The upper management should decide how much data can be lost. They would be more familar with the business needs of that data. As well it lifts the burden off you if data is lost, they made the decision.
You should also consider since this invoice data falls under financial (PCI, HIPPA, etc), what the retention period needs to be for your backups. That can also dictate how many times you backup as well.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 27, 2010 at 5:50 am
Let's put it this way. Your disaster recovery is only as good as the last backup. You're capturing the logs once an hour. That means, in a really serious failure, you could lose up to 59 minutes worth of transactions. Is the business prepared to lose that much data? The company I work for has declared that no more than 10 minutes worth of transactions can be lost, so we capture the logs every 10 minutes, all day long, on most of our production systems.
That's the only basic suggestion I'd make as far as backups go. Do you have a plan for DBCC checks, statistics updates, index defragmentation...?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 27, 2010 at 6:14 am
"Do you have a plan for DBCC checks, statistics updates, index defragmentation...? " NOOOOOOO
i have a lot of reading to do!!! lol Do i need all this? isn't a back up plan just necessary?
Ok, any general guidelines -ideas like what to do (like the best frequencies of the above as regarding erp systems , and the rest i will study myself) will be highly appreciated. Do i need to have a plan for all those? lol!
don't laugh, i though a back up plan would be enough!
********************************************************************
"You can do this in one maintenance plan, just create "sub plans" that you can do individual task separate from the other sub plans and on different schedules".
If i create a back up plan with three sub plans, lets say first subplan, full back up every 12 hours, 2nd subplan differential every 8 hours and 3rd subplan tail log every 30minutes, will each subplan take into consideration the other subplans? For instance will the differential back up recognize the full back even though it is on a different subplan? (i know subplans, they belong to a maintenance plan, so that should not be a problem but please follow reading).
Also, is there any way to build a maintenance plan AND a " seperate full back up" on the same database, without differential back ups to get confused between the seperate full back up and the maintenance plan? (one full back up from the maintenance plan, and one full back up plain, outside the maintenance plan).
April 27, 2010 at 6:22 am
Everyone does it differently, so there's no hard and fast rule. Plus, it's really dependent on your business needs.
These are some general guidelines, but you'll need to customize them.
Run DBCC checks prior to full backups. If the DBCC fails, don't backup the database, get to work on addressing the consistency errors first.
Start with statistics and index maintenance running once a week. Adjust to more frequent updates as needed based on performance and behavior of your system.
I'd say a thin majority of DBA's write their own scripts, or use scripts from online, and the rest use maintenance plans. You can choose either method. They both have pluses and minuses. Regardless, you can arrive at a schedule that does what you need.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply