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

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.

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

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).

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

--
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|||"gabe101" <gabe101@.gmail.comwrote in message
news:1166551052.960302.151960@.80g2000cwy.googlegro ups.com...

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

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.

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

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.

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.

--
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

gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

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.

>
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.

backup in maint plan failing

Hello, i'm having a problem with a db backup in a maint plan-- i get an
error saying
Msg 3007, Level 16, State 1, Line 2
The backup of the file or filegroup "sysft_ft" is not permitted because it
is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 2
What is "sysft_ft"? If i search this in google i literally only get one
hit, which is unbelieveable to me. How can I get around this? I really
don't want to do a file backup and would like to get to the bottom of this.
Thanks, NWOP.This looks like an offline full text catalog. If you are using SQL Server
2005 try
select name, state, state_desc from sys.database_files
If one of the names matchs sysft_ft and state_desc is OFFLINE you will need
to fix it first. Perhaps you just need to rebuild the catalog. After the
catalog is ONLINE you will be able to perform your backup.
Hope this helps,
Ben Nevarez
"new_world_order_pigs" wrote:
> Hello, i'm having a problem with a db backup in a maint plan-- i get an
> error saying
> Msg 3007, Level 16, State 1, Line 2
> The backup of the file or filegroup "sysft_ft" is not permitted because it
> is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses
> to restrict the selection to include only online data.
> Msg 3013, Level 16, State 1, Line 2
> What is "sysft_ft"? If i search this in google i literally only get one
> hit, which is unbelieveable to me. How can I get around this? I really
> don't want to do a file backup and would like to get to the bottom of this.
> Thanks, NWOP.|||Ben,
This did seem to work in that it did put the full text cat back "online" so
I'm assuming that when I run the backup in the evening that it should work
just fine. You are officially a "SQL Server Animal" in my book and I
appreciate your help.
Sincerely,
NWOP.
"Ben Nevarez" wrote:
> This looks like an offline full text catalog. If you are using SQL Server
> 2005 try
> select name, state, state_desc from sys.database_files
> If one of the names matchs sysft_ft and state_desc is OFFLINE you will need
> to fix it first. Perhaps you just need to rebuild the catalog. After the
> catalog is ONLINE you will be able to perform your backup.
> Hope this helps,
> Ben Nevarez
>
>
> "new_world_order_pigs" wrote:
> > Hello, i'm having a problem with a db backup in a maint plan-- i get an
> > error saying
> > Msg 3007, Level 16, State 1, Line 2
> > The backup of the file or filegroup "sysft_ft" is not permitted because it
> > is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses
> > to restrict the selection to include only online data.
> > Msg 3013, Level 16, State 1, Line 2
> >
> > What is "sysft_ft"? If i search this in google i literally only get one
> > hit, which is unbelieveable to me. How can I get around this? I really
> > don't want to do a file backup and would like to get to the bottom of this.
> > Thanks, NWOP.

backup in maint plan failing

Hello, i'm having a problem with a db backup in a maint plan-- i get an
error saying
Msg 3007, Level 16, State 1, Line 2
The backup of the file or filegroup "sysft_ft" is not permitted because it
is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses
to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 2
What is "sysft_ft"? If i search this in google i literally only get one
hit, which is unbelieveable to me. How can I get around this? I really
don't want to do a file backup and would like to get to the bottom of this.
Thanks, NWOP.
This looks like an offline full text catalog. If you are using SQL Server
2005 try
select name, state, state_desc from sys.database_files
If one of the names matchs sysft_ft and state_desc is OFFLINE you will need
to fix it first. Perhaps you just need to rebuild the catalog. After the
catalog is ONLINE you will be able to perform your backup.
Hope this helps,
Ben Nevarez
"new_world_order_pigs" wrote:

> Hello, i'm having a problem with a db backup in a maint plan-- i get an
> error saying
> Msg 3007, Level 16, State 1, Line 2
> The backup of the file or filegroup "sysft_ft" is not permitted because it
> is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses
> to restrict the selection to include only online data.
> Msg 3013, Level 16, State 1, Line 2
> What is "sysft_ft"? If i search this in google i literally only get one
> hit, which is unbelieveable to me. How can I get around this? I really
> don't want to do a file backup and would like to get to the bottom of this.
> Thanks, NWOP.
|||Ben,
This did seem to work in that it did put the full text cat back "online" so
I'm assuming that when I run the backup in the evening that it should work
just fine. You are officially a "SQL Server Animal" in my book and I
appreciate your help.
Sincerely,
NWOP.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> This looks like an offline full text catalog. If you are using SQL Server
> 2005 try
> select name, state, state_desc from sys.database_files
> If one of the names matchs sysft_ft and state_desc is OFFLINE you will need
> to fix it first. Perhaps you just need to rebuild the catalog. After the
> catalog is ONLINE you will be able to perform your backup.
> Hope this helps,
> Ben Nevarez
>
>
> "new_world_order_pigs" wrote:

Backup from SQL Server 2005 - restore to 2000

I need to restore a backup of a db in SQL 2005 with 80-compatibility level
into SQL Server 2000. I have tried to do it but I have an error. Is it
possible to do it?
Does exist another fast method to bring a SQL Server 2005 db into 2000?
Many thanks for your suggests.Pasquale wrote:
> I need to restore a backup of a db in SQL 2005 with 80-compatibility level
> into SQL Server 2000. I have tried to do it but I have an error. Is it
> possible to do it?
> Does exist another fast method to bring a SQL Server 2005 db into 2000?
> Many thanks for your suggests.
you cannot restore 2005 database to 2000 server
try using import/export wizard
gl|||I known this answer, but I hope if possible with a SQL Server 2005 db in
80-compatibility level.
Using import/export wizard is less quick.
Thanks
"Zarko Jovanovic" wrote:
> Pasquale wrote:
> > I need to restore a backup of a db in SQL 2005 with 80-compatibility level
> > into SQL Server 2000. I have tried to do it but I have an error. Is it
> > possible to do it?
> > Does exist another fast method to bring a SQL Server 2005 db into 2000?
> >
> > Many thanks for your suggests.
> you cannot restore 2005 database to 2000 server
> try using import/export wizard
> gl
>|||There aren't really any other ways of doing it. No matter what
compatibility level you have set in your database, you can't restore
"backwards". You'll have to "manually" transfer the data to a SQL2000
database.
Regards
Steen Schlüter Persson
Pasquale wrote:
> I known this answer, but I hope if possible with a SQL Server 2005 db in
> 80-compatibility level.
> Using import/export wizard is less quick.
> Thanks
>
> "Zarko Jovanovic" wrote:
>> Pasquale wrote:
>> I need to restore a backup of a db in SQL 2005 with 80-compatibility level
>> into SQL Server 2000. I have tried to do it but I have an error. Is it
>> possible to do it?
>> Does exist another fast method to bring a SQL Server 2005 db into 2000?
>> Many thanks for your suggests.
>> you cannot restore 2005 database to 2000 server
>> try using import/export wizard
>> gl|||Steen Schlüter Persson (DK) wrote:
> There aren't really any other ways of doing it. No matter what
> compatibility level you have set in your database, you can't restore
> "backwards". You'll have to "manually" transfer the data to a SQL2000
> database.
> Regards
> Steen Schlüter Persson
>
> Pasquale wrote:
>> I known this answer, but I hope if possible with a SQL Server 2005 db
>> in 80-compatibility level.
>> Using import/export wizard is less quick.
>> Thanks
>>
>> "Zarko Jovanovic" wrote:
>> Pasquale wrote:
>> I need to restore a backup of a db in SQL 2005 with 80-compatibility
>> level into SQL Server 2000. I have tried to do it but I have an
>> error. Is it possible to do it?
>> Does exist another fast method to bring a SQL Server 2005 db into 2000?
>> Many thanks for your suggests.
>> you cannot restore 2005 database to 2000 server
>> try using import/export wizard
>> gl
just to acknowledge that: no, you cannot restore or attach or anything
with 2005 database on 2000 server no matter compatibility level|||Ok!
I hope to do it because the db in SQL Server 2005 is the result of a db
backupped into SQL Server 2000 and restored into 2005 with 80 compatibility
level.
Many thanks.
"Zarko Jovanovic" wrote:
> Steen Schlüter Persson (DK) wrote:
> >
> > There aren't really any other ways of doing it. No matter what
> > compatibility level you have set in your database, you can't restore
> > "backwards". You'll have to "manually" transfer the data to a SQL2000
> > database.
> >
> > Regards
> > Steen Schlüter Persson
> >
> >
> > Pasquale wrote:
> >> I known this answer, but I hope if possible with a SQL Server 2005 db
> >> in 80-compatibility level.
> >> Using import/export wizard is less quick.
> >>
> >> Thanks
> >>
> >>
> >> "Zarko Jovanovic" wrote:
> >>
> >> Pasquale wrote:
> >> I need to restore a backup of a db in SQL 2005 with 80-compatibility
> >> level into SQL Server 2000. I have tried to do it but I have an
> >> error. Is it possible to do it?
> >> Does exist another fast method to bring a SQL Server 2005 db into 2000?
> >>
> >> Many thanks for your suggests.
> >> you cannot restore 2005 database to 2000 server
> >>
> >> try using import/export wizard
> >>
> >> gl
> >>
> just to acknowledge that: no, you cannot restore or attach or anything
> with 2005 database on 2000 server no matter compatibility level
>|||"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:46033DC3-842B-488F-8844-5BAB0411064C@.microsoft.com...
> Ok!
> I hope to do it because the db in SQL Server 2005 is the result of a db
> backupped into SQL Server 2000 and restored into 2005 with 80
> compatibility
> level.
Correct, you can restore a DB from SQL 2000 to 2005, but you can not under
any circumstances do the reverse.
The 80 compatibility only refers to features available, etc. The structure
itself is still a 2005 DB.
> Many thanks.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html