Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

backup during peak hours

Posted on 2011-02-17
15
Medium Priority
?
436 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 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