Solved

backup during peak hours

Posted on 2011-02-17
15
429 Views
Last Modified: 2012-05-11
when the production hours are peak round the clock, how would you select the best time to do a good FULL backup, for the 24 hour period.?

what kind of transactions would cause you defer the backup.? and what kind of activity is better for the backup to be the least hinderance? (read or write, index usage etc)

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 188 total points
ID: 34920899
I suggest you use some 3rd party tools that can throttle your backups to not get impacted as bad as from a native SQL backup for instance. We due to our hardware use HP Data Protector to backup busy SQL dbs that are the backend for a global Ecomerce website with no issues. Full backup for one of our 800+GB db takes 10-12 hours on X tapes in parallel but no impact in website activity.
0
 
LVL 6

Assisted Solution

by:Mistralol
Mistralol earned 63 total points
ID: 34921136

If it is truly round the clock you may want to consider a mirror server and take the backup from the mirror.
Or replicate the database to a 2nd server and take a backup of it.

0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 188 total points
ID: 34921225
Those are indeed options however they add complexity, cost, maintenance, etc and solid 3rd party backup tools can offer much more at lower cost in time/effort and money.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Assisted Solution

by:niaz
niaz earned 187 total points
ID: 34921230
What is the size of your database? How long does it take now to do a full backup? Do you write your backup direct to tape or to disk. What kind of storage and HW resources is in place? What is the recovery model set for the database in question?

 http://www.sqlbackuprestore.com/recoverymodels.htm

You can also look at the combination of Full -Differential - Transaction log backup to bring down the backup time.
http://www.sqlbackuprestore.com/mainbackuptypes.htm

Please see the links to read more detail concepts related to backup.

0
 
LVL 6

Author Comment

by:anushahanna
ID: 34922317
the db is 95GB; legato networker(3rd party) does it (FULL backup) in 60 minutes to tape. the server is M2 and storage is SAN (IBM) 8300
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34922319

lcohan. would to be able to compare HP tool you mentioned with the above?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34922328
some times are busier than others- management does not want to consider adding servers at this time.. but wants to just tweak the best time for the backup, for starters..
0
 
LVL 2

Assisted Solution

by:niaz
niaz earned 187 total points
ID: 34923111
I would recommend to take a full backup once a week, Incremental every day and transaction all day may be every half hour or 15 min. (Depends on you how much of data loss you can afford in case of a disaster).

Avoid running backup during any batch processing, or heavy DML (Insert/Update/Delete) transactions.  Indexing is heavy I/O, so avoid running backup while you are running any maintenance job.
The key here is to avoid I/O contentions. If you can manage to disperse your .MDF and .LDF and Transaction log backups files to separate disks or volumes it will improve the I/O contentions. You can gain significant time off backup process if you first write it to disk and then take it to tape.

Do you have any performance issue in general with the database or particularly when running backup?
 
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 188 total points
ID: 34925881
If you do not experience any performance issues during current FULL backup then in my opinion all you need to do is to monitor the backup time and results to ensure they are successfully completed in reasonable amount of time. For that there are various ways to do it and if the backup id done through some 3rd party agent then I bet that one should have some notification on completion/failure. if is standard SQL job then you can use the job notification on completion and eventually set an alert on failure so you can take immediate action.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34935890
niaz/lcohan

the tape backup is taking random time each day for completion - some days 1 hours, and some tims 7-8 hours - so what I can see is that if there are a lot of transactions at that time, then it affects the backup.

0
 
LVL 6

Author Comment

by:anushahanna
ID: 34935907
niaz, any reason you recommend incremental and not differential?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 62 total points
ID: 34939048
>>any reason you recommend incremental and not differential? <<
Perhaps they do not realize that you are using SQL Server and only Differential backups are supported.
0
 
LVL 2

Assisted Solution

by:niaz
niaz earned 187 total points
ID: 34948635
Yes, you are right "acperkins", I work with both Oracle and SQL and used the term mistakenly. If you look at my 2/17 post I have used the term Full - Differential. I apologizes for any misunderstanding that it might have caused.

When it takes 7-8 hours you need to identify WHO is doing WHAT, so that you can do the performance tuning if it is needed. Re-Schedule your backup at a time when it does not conflict with a job or any batch load.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34949043
>>Yes, you are right "acperkins"<<
Yes, I know, but thanks.  Incidentally most people here call me by my name: Anthony.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34949955
Thank you experts.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question