Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

backup during peak hours

Posted on 2011-02-17
15
Medium Priority
?
438 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
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 752 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 252 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 752 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 2

Assisted Solution

by:niaz
niaz earned 748 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 748 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 752 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 248 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 748 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

885 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