Solved

backup during peak hours

Posted on 2011-02-17
15
413 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 39

Accepted Solution

by:
lcohan earned 188 total points
Comment Utility
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
Comment Utility

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 39

Assisted Solution

by:lcohan
lcohan earned 188 total points
Comment Utility
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
 
LVL 2

Assisted Solution

by:niaz
niaz earned 187 total points
Comment Utility
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
Comment Utility
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
Comment Utility

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

Author Comment

by:anushahanna
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 2

Assisted Solution

by:niaz
niaz earned 187 total points
Comment Utility
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 39

Assisted Solution

by:lcohan
lcohan earned 188 total points
Comment Utility
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
Comment Utility
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
Comment Utility
niaz, any reason you recommend incremental and not differential?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 62 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Thank you experts.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now