sql backup files: Backup Priority level

I've searched enough to know that this ability probably doesn't exist
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.

Thanks,

Gabegabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.

It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.

Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.

Thank you and yes, you are correct...it's noon.

Quote:

Originally Posted by

It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.

We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).

Quote:

Originally Posted by

Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?

We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.

Thanks Erland.|||gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

One alternative you may want to investigate is to do a differntial
backup. That may tax your server less.

Quote:

Originally Posted by

Erland Sommarskog wrote:
>
We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.

Argh, you're really stressing that machine.

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.

A backup on the same machine won't do you much good if say the RAID
controller fails.

Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.

Quote:

Originally Posted by

>
Thanks Erland.
>

|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

Argh, you're really stressing that machine.

Yeah, I agree. Unfortunately, it's not in the budget for us to expand
at this time. I will say this though, to me (and my mere two years
experience) it seems to be running very stable. Since we only run with
10 users, I've set SQL Server's memory allocation to 600MB and set
SBSMONITORING to 100MB, Exchange is being kind and staying between 575
- 600 MB and Symantec is occupying another 400 - 500. All together
I've still got about 800 - 1,000 MB of free RAM. My server report
shows an average of 18% busy disk time and both CPU's (shown as 4)
averaging about 8-10% use. Again, I only assume this is good because
I'm new to IT. I just got my degree two years ago. Would you say
our server is doing 'Good', 'Satisfactory' or 'Poor'?

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
>
A backup on the same machine won't do you much good if say the RAID
controller fails.

I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

Quote:

Originally Posted by

Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.

My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.

Thank you both for your comments and if you have anything more to say,
I'm still listening (and learning).|||Yes, this is a response to my own response...

;-)

gabe101 wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.

A backup on the same machine won't do you much good if say the RAID
controller fails.

>
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

I suddenly have a reason for concern...

I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.|||gabe101 wrote:

Quote:

Originally Posted by

Yes, this is a response to my own response...
>
;-)
>
gabe101 wrote:
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>
I suddenly have a reason for concern...
>
I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.

Depends on your network of course but it doesn't surprise me that
sending a large file over a network would take longer than the local
backup, especially during the day. Have you considered getting a tape
drive to do your backups - they're not all that expensive, certainly a
lot cheaper than losing your data would be :)

Also, as someone else mentioned, consider doing a differential backup
during the day rather than the full backup. It's just one more step if
you need to restore, and it should be much smaller and faster than your
full backup.|||gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky. You would need to play with it on a test
box, so that you are confident with the process when the disaster
strikes.

A differential backup only includs the changed pages since the last
full backup, so it would be a lot smaller and thus tax the system
less.

Quote:

Originally Posted by

gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

>
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky.

Not tricky at all. Restore the full backup, then restore the diff.

Sunday, March 11, 2012

Backup Priority level

No comments:

Post a Comment

sql backup files

Blog Archive

About Me