How to create maintenance plan to backup files and filegoups

Hello Everyone,
Database Maintenance Plan Wizard in SQL Server 2000 helps
to create a maintenance plan to perform database backups
on a regular basis. How could I create a plan to backup
file and filegroups on a regular basis.
I also appreciate if you would comment on what conditions
would you suggest to place a table in a different
filegroup than the rest of the tables in a database?
Thank you,
-MitraMitra,
You can't use the Maintenance Plan to perform backups other than Full and
Log. You can easily create your own scheduled job to do this though. The
two biggest reasons why you might place a table in a separate filegroup are:
1. You have two drive arrays and want to place the table (Data or clustered
index) on one and the non-clustered indexes onthe other. Done when you have
heavy access to the table and lots of drive space.
2. If you need to back up one or several tables at a different schedule
than the others. This might be because you have large tables that are
mostly static and don't need backing up each night or if you simply have
VLDB's that take too long to backup all at once.
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> Hello Everyone,
> Database Maintenance Plan Wizard in SQL Server 2000 helps
> to create a maintenance plan to perform database backups
> on a regular basis. How could I create a plan to backup
> file and filegroups on a regular basis.
> I also appreciate if you would comment on what conditions
> would you suggest to place a table in a different
> filegroup than the rest of the tables in a database?
> Thank you,
> -Mitra|||In addition to Andrew's explanation, filegroups can provide you with the
ability to make part of your database read-only for a period of the time(and
bring it to read-write state when finished your job). It could be useful in
particular situations for administrative tasks.
Also I believe it would be still better to keep the data(table) and indexes
in two different filegroups even when we have one physical disk dedicated to
them, because at least it could help to reduce the fragmentation's growth
(IMHO of course!).
Regards,
Amin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OORtG31nEHA.3216@.tk2msftngp13.phx.gbl...
> Mitra,
> You can't use the Maintenance Plan to perform backups other than Full and
> Log. You can easily create your own scheduled job to do this though. The
> two biggest reasons why you might place a table in a separate filegroup
are:
> 1. You have two drive arrays and want to place the table (Data or
clustered
> index) on one and the non-clustered indexes onthe other. Done when you
have
> heavy access to the table and lots of drive space.
> 2. If you need to back up one or several tables at a different schedule
> than the others. This might be because you have large tables that are
> mostly static and don't need backing up each night or if you simply have
> VLDB's that take too long to backup all at once.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
> news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> > Hello Everyone,
> >
> > Database Maintenance Plan Wizard in SQL Server 2000 helps
> > to create a maintenance plan to perform database backups
> > on a regular basis. How could I create a plan to backup
> > file and filegroups on a regular basis.
> >
> > I also appreciate if you would comment on what conditions
> > would you suggest to place a table in a different
> > filegroup than the rest of the tables in a database?
> >
> > Thank you,
> >
> > -Mitra
>|||Thanks everyone for the suggestions!
One of the reasons I am thinking that I should take this
approach, creating this one table on a different
filegroup, is because we have this one table that
consumes 75% of the size of the database. And it will
take long time to backup the whole database. This
particular table is not a statice table. It has three
columns, id(int), size(int), content(Text). And we are
storing the message body of all emails inside the
organization inside the content column.
Do you think one table causing a database to become a
VLDB is good enough reason to create this one table on a
separate filegroup to reduce to time of the backup?
Thanks,
-Mitra
>--Original Message--
>Mitra,
>You can't use the Maintenance Plan to perform backups
other than Full and
>Log. You can easily create your own scheduled job to do
this though. The
>two biggest reasons why you might place a table in a
separate filegroup are:
>1. You have two drive arrays and want to place the
table (Data or clustered
>index) on one and the non-clustered indexes onthe
other. Done when you have
>heavy access to the table and lots of drive space.
>2. If you need to back up one or several tables at a
different schedule
>than the others. This might be because you have large
tables that are
>mostly static and don't need backing up each night or if
you simply have
>VLDB's that take too long to backup all at once.
>
>--
>Andrew J. Kelly SQL MVP
>
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
>> Hello Everyone,
>> Database Maintenance Plan Wizard in SQL Server 2000
helps
>> to create a maintenance plan to perform database
backups
>> on a regular basis. How could I create a plan to backup
>> file and filegroups on a regular basis.
>> I also appreciate if you would comment on what
conditions
>> would you suggest to place a table in a different
>> filegroup than the rest of the tables in a database?
>> Thank you,
>> -Mitra
>
>.
>|||Mitra,
SQL Server can under take the task for physically separating the BLOBs
('Content' field for your table) from the rest of table.
If the speed of backup operation is the most important factor, you could
place the BLOBs on other filegroup(physical disk) and add a mirrored disk
for that filegroup. By this approach, imagine that the important part of
table is being backed up as you write to the table.
Although it does not completely eliminate the need for backup, but now
you're able to backup only 'Content' field of table that will cause to
reduce time and size of backup.
Amin
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> Thanks everyone for the suggestions!
> One of the reasons I am thinking that I should take this
> approach, creating this one table on a different
> filegroup, is because we have this one table that
> consumes 75% of the size of the database. And it will
> take long time to backup the whole database. This
> particular table is not a statice table. It has three
> columns, id(int), size(int), content(Text). And we are
> storing the message body of all emails inside the
> organization inside the content column.
> Do you think one table causing a database to become a
> VLDB is good enough reason to create this one table on a
> separate filegroup to reduce to time of the backup?
> Thanks,
> -Mitra
>
> >--Original Message--
> >Mitra,
> >
> >You can't use the Maintenance Plan to perform backups
> other than Full and
> >Log. You can easily create your own scheduled job to do
> this though. The
> >two biggest reasons why you might place a table in a
> separate filegroup are:
> >
> >1. You have two drive arrays and want to place the
> table (Data or clustered
> >index) on one and the non-clustered indexes onthe
> other. Done when you have
> >heavy access to the table and lots of drive space.
> >2. If you need to back up one or several tables at a
> different schedule
> >than the others. This might be because you have large
> tables that are
> >mostly static and don't need backing up each night or if
> you simply have
> >VLDB's that take too long to backup all at once.
> >
> >
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> Hello Everyone,
> >>
> >> Database Maintenance Plan Wizard in SQL Server 2000
> helps
> >> to create a maintenance plan to perform database
> backups
> >> on a regular basis. How could I create a plan to backup
> >> file and filegroups on a regular basis.
> >>
> >> I also appreciate if you would comment on what
> conditions
> >> would you suggest to place a table in a different
> >> filegroup than the rest of the tables in a database?
> >>
> >> Thank you,
> >>
> >> -Mitra
> >
> >
> >.
> >|||If the table is 75% of the entire db you won't save a whole lot of time by
splitting them but it si certainly an option. I would look at other
measures to speed it up. How are you doing backups now? Have you tried SQL
LiteSpeed? Have you thought of archiving some of the table?
--
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> Thanks everyone for the suggestions!
> One of the reasons I am thinking that I should take this
> approach, creating this one table on a different
> filegroup, is because we have this one table that
> consumes 75% of the size of the database. And it will
> take long time to backup the whole database. This
> particular table is not a statice table. It has three
> columns, id(int), size(int), content(Text). And we are
> storing the message body of all emails inside the
> organization inside the content column.
> Do you think one table causing a database to become a
> VLDB is good enough reason to create this one table on a
> separate filegroup to reduce to time of the backup?
> Thanks,
> -Mitra
>
> >--Original Message--
> >Mitra,
> >
> >You can't use the Maintenance Plan to perform backups
> other than Full and
> >Log. You can easily create your own scheduled job to do
> this though. The
> >two biggest reasons why you might place a table in a
> separate filegroup are:
> >
> >1. You have two drive arrays and want to place the
> table (Data or clustered
> >index) on one and the non-clustered indexes onthe
> other. Done when you have
> >heavy access to the table and lots of drive space.
> >2. If you need to back up one or several tables at a
> different schedule
> >than the others. This might be because you have large
> tables that are
> >mostly static and don't need backing up each night or if
> you simply have
> >VLDB's that take too long to backup all at once.
> >
> >
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> Hello Everyone,
> >>
> >> Database Maintenance Plan Wizard in SQL Server 2000
> helps
> >> to create a maintenance plan to perform database
> backups
> >> on a regular basis. How could I create a plan to backup
> >> file and filegroups on a regular basis.
> >>
> >> I also appreciate if you would comment on what
> conditions
> >> would you suggest to place a table in a different
> >> filegroup than the rest of the tables in a database?
> >>
> >> Thank you,
> >>
> >> -Mitra
> >
> >
> >.
> >|||Andrew, currently, we have created a maintenance plan
using Enterprise Manager to perform our database backups.
We have not tried SQLLiteSpeed.
You asked if we have thought about archiving some of the
tables. What is archiving table?
Amin, the time it takes to perform a database backup is
what we are trying to reduce.
You suggested to place only the TEXT column, 'Content',
on a different filegroup than the table itself. I read
the same thing in Book Online that any text column within
a table can be created on a filegroup other than the one
that contains the base table. Why separating only the
column? What's the advantage?
After creating and populating a table or a column in a
different filegroup, is there a way to incorporate this
specific filegroup with the default filegroup if we had
to?
Thanks,
-Mitra
>--Original Message--
>If the table is 75% of the entire db you won't save a
whole lot of time by
>splitting them but it si certainly an option. I would
look at other
>measures to speed it up. How are you doing backups
now? Have you tried SQL
>LiteSpeed? Have you thought of archiving some of the
table?
>--
>Andrew J. Kelly SQL MVP
>
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
>> Thanks everyone for the suggestions!
>> One of the reasons I am thinking that I should take
this
>> approach, creating this one table on a different
>> filegroup, is because we have this one table that
>> consumes 75% of the size of the database. And it will
>> take long time to backup the whole database. This
>> particular table is not a statice table. It has three
>> columns, id(int), size(int), content(Text). And we are
>> storing the message body of all emails inside the
>> organization inside the content column.
>> Do you think one table causing a database to become a
>> VLDB is good enough reason to create this one table on
a
>> separate filegroup to reduce to time of the backup?
>> Thanks,
>> -Mitra
>>
>> >--Original Message--
>> >Mitra,
>> >
>> >You can't use the Maintenance Plan to perform backups
>> other than Full and
>> >Log. You can easily create your own scheduled job to
do
>> this though. The
>> >two biggest reasons why you might place a table in a
>> separate filegroup are:
>> >
>> >1. You have two drive arrays and want to place the
>> table (Data or clustered
>> >index) on one and the non-clustered indexes onthe
>> other. Done when you have
>> >heavy access to the table and lots of drive space.
>> >2. If you need to back up one or several tables at a
>> different schedule
>> >than the others. This might be because you have large
>> tables that are
>> >mostly static and don't need backing up each night or
if
>> you simply have
>> >VLDB's that take too long to backup all at once.
>> >
>> >
>> >
>> >--
>> >Andrew J. Kelly SQL MVP
>> >
>> >
>> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
>> >> Hello Everyone,
>> >>
>> >> Database Maintenance Plan Wizard in SQL Server 2000
>> helps
>> >> to create a maintenance plan to perform database
>> backups
>> >> on a regular basis. How could I create a plan to
backup
>> >> file and filegroups on a regular basis.
>> >>
>> >> I also appreciate if you would comment on what
>> conditions
>> >> would you suggest to place a table in a different
>> >> filegroup than the rest of the tables in a database?
>> >>
>> >> Thank you,
>> >>
>> >> -Mitra
>> >
>> >
>> >.
>> >
>
>.
>|||Mitra,
I was hoping to get a little more detail about exactly how you are backing
the db up currently. Are you backing up directly to tape or to disks? If
it is to disk is this a disk array that is separate from any of the data or
log files? What type array is it? How large is the database? How long does
it currently take etc.?
There are lots of things you can do to speed up a backup but depending on
how you are doing it now. Chances are you are backing up to a relatively
slow disk array or one that has only a few physical disks in it. If your
disk array has lots of capacity or better yet you have multiple disk arrays
you can issue a backup with multiple files. This can make a big difference
in the time it actually takes to complete the backup depending on the
hardware and number of processors.
You should definitely look at using SQL LiteSpeed to do your backups as
well. It can not only backup to multiple files but can compress the backup
on the fly at the same time. This not only results in backup files that are
dramatically smaller than the originals but speeds the backup process at the
same time. www.imceda.com
By archiving I was referring to removing some of the data that you might not
need anymore. Or possibly placing older emails in a separate database once
every so often to keep the main db size at a reasonable level.
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:359601c4a039$3c005010$a501280a@.phx.gbl...
> Andrew, currently, we have created a maintenance plan
> using Enterprise Manager to perform our database backups.
> We have not tried SQLLiteSpeed.
> You asked if we have thought about archiving some of the
> tables. What is archiving table?
> Amin, the time it takes to perform a database backup is
> what we are trying to reduce.
> You suggested to place only the TEXT column, 'Content',
> on a different filegroup than the table itself. I read
> the same thing in Book Online that any text column within
> a table can be created on a filegroup other than the one
> that contains the base table. Why separating only the
> column? What's the advantage?
> After creating and populating a table or a column in a
> different filegroup, is there a way to incorporate this
> specific filegroup with the default filegroup if we had
> to?
> Thanks,
> -Mitra
> >--Original Message--
> >If the table is 75% of the entire db you won't save a
> whole lot of time by
> >splitting them but it si certainly an option. I would
> look at other
> >measures to speed it up. How are you doing backups
> now? Have you tried SQL
> >LiteSpeed? Have you thought of archiving some of the
> table?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> >> Thanks everyone for the suggestions!
> >>
> >> One of the reasons I am thinking that I should take
> this
> >> approach, creating this one table on a different
> >> filegroup, is because we have this one table that
> >> consumes 75% of the size of the database. And it will
> >> take long time to backup the whole database. This
> >> particular table is not a statice table. It has three
> >> columns, id(int), size(int), content(Text). And we are
> >> storing the message body of all emails inside the
> >> organization inside the content column.
> >> Do you think one table causing a database to become a
> >> VLDB is good enough reason to create this one table on
> a
> >> separate filegroup to reduce to time of the backup?
> >>
> >> Thanks,
> >>
> >> -Mitra
> >>
> >>
> >> >--Original Message--
> >> >Mitra,
> >> >
> >> >You can't use the Maintenance Plan to perform backups
> >> other than Full and
> >> >Log. You can easily create your own scheduled job to
> do
> >> this though. The
> >> >two biggest reasons why you might place a table in a
> >> separate filegroup are:
> >> >
> >> >1. You have two drive arrays and want to place the
> >> table (Data or clustered
> >> >index) on one and the non-clustered indexes onthe
> >> other. Done when you have
> >> >heavy access to the table and lots of drive space.
> >> >2. If you need to back up one or several tables at a
> >> different schedule
> >> >than the others. This might be because you have large
> >> tables that are
> >> >mostly static and don't need backing up each night or
> if
> >> you simply have
> >> >VLDB's that take too long to backup all at once.
> >> >
> >> >
> >> >
> >> >--
> >> >Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> >> wrote in message
> >> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> >> Hello Everyone,
> >> >>
> >> >> Database Maintenance Plan Wizard in SQL Server 2000
> >> helps
> >> >> to create a maintenance plan to perform database
> >> backups
> >> >> on a regular basis. How could I create a plan to
> backup
> >> >> file and filegroups on a regular basis.
> >> >>
> >> >> I also appreciate if you would comment on what
> >> conditions
> >> >> would you suggest to place a table in a different
> >> >> filegroup than the rest of the tables in a database?
> >> >>
> >> >> Thank you,
> >> >>
> >> >> -Mitra
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Mitra,
A table can be placed on maximum of two different filegroups. One specified
for BOLBs(fields of type text,ntext,image) and one specified for non-BLOBs.
Therefore in your table, only 'Content' field can be separated.
When you backup filegroup of 'Contents' field, the amount of data that is
being backed up is smaller than the whole of table.
Please attention to this text from books online:
**************
Microsoft® SQL ServerT 2000 supports backing up or restoring individual
files or file groups within a database. This is a relatively sophisticated
backup and restore process usually reserved for very large databases (VLDB)
with high availability requirements. If the time available for backups is
not long enough to support backing up the full database, subsets of the
database can be backed up at different times.
For example, it takes three hours for a site to back up a database, and
backups can be performed only during a two-hour period each day. The site
can back up half the files or file groups on one night and half the next. If
a disk holding database files or filegroups fails, the site can restore just
the lost files or filegroups. The site must also be making transaction log
backups, and must restore all transaction log backups made after the file or
filegroup backup.
File and filegroup restores can also be made from a full database backup
set. This allows for a quicker recovery because only the damaged files or
filegroups are restored in the first step, not the entire database.
**************
And yes. You may change the filegroup of BLOBs if you have to. In EM, go to
design mode of your table. Bring up the properties dialogbox. Note the 'Text
Filegroup' and 'Table Filegroup' dropdown lists.
However this is good solution when you don't have enough budgette to spend
on more disks. If you can dedicate more disks for backup operation, it will
be much faster to make a media family consisting of two or more files(on
your additional disk).
Amin
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:359601c4a039$3c005010$a501280a@.phx.gbl...
> Andrew, currently, we have created a maintenance plan
> using Enterprise Manager to perform our database backups.
> We have not tried SQLLiteSpeed.
> You asked if we have thought about archiving some of the
> tables. What is archiving table?
> Amin, the time it takes to perform a database backup is
> what we are trying to reduce.
> You suggested to place only the TEXT column, 'Content',
> on a different filegroup than the table itself. I read
> the same thing in Book Online that any text column within
> a table can be created on a filegroup other than the one
> that contains the base table. Why separating only the
> column? What's the advantage?
> After creating and populating a table or a column in a
> different filegroup, is there a way to incorporate this
> specific filegroup with the default filegroup if we had
> to?
> Thanks,
> -Mitra
> >--Original Message--
> >If the table is 75% of the entire db you won't save a
> whole lot of time by
> >splitting them but it si certainly an option. I would
> look at other
> >measures to speed it up. How are you doing backups
> now? Have you tried SQL
> >LiteSpeed? Have you thought of archiving some of the
> table?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> >> Thanks everyone for the suggestions!
> >>
> >> One of the reasons I am thinking that I should take
> this
> >> approach, creating this one table on a different
> >> filegroup, is because we have this one table that
> >> consumes 75% of the size of the database. And it will
> >> take long time to backup the whole database. This
> >> particular table is not a statice table. It has three
> >> columns, id(int), size(int), content(Text). And we are
> >> storing the message body of all emails inside the
> >> organization inside the content column.
> >> Do you think one table causing a database to become a
> >> VLDB is good enough reason to create this one table on
> a
> >> separate filegroup to reduce to time of the backup?
> >>
> >> Thanks,
> >>
> >> -Mitra
> >>
> >>
> >> >--Original Message--
> >> >Mitra,
> >> >
> >> >You can't use the Maintenance Plan to perform backups
> >> other than Full and
> >> >Log. You can easily create your own scheduled job to
> do
> >> this though. The
> >> >two biggest reasons why you might place a table in a
> >> separate filegroup are:
> >> >
> >> >1. You have two drive arrays and want to place the
> >> table (Data or clustered
> >> >index) on one and the non-clustered indexes onthe
> >> other. Done when you have
> >> >heavy access to the table and lots of drive space.
> >> >2. If you need to back up one or several tables at a
> >> different schedule
> >> >than the others. This might be because you have large
> >> tables that are
> >> >mostly static and don't need backing up each night or
> if
> >> you simply have
> >> >VLDB's that take too long to backup all at once.
> >> >
> >> >
> >> >
> >> >--
> >> >Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> >> wrote in message
> >> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> >> Hello Everyone,
> >> >>
> >> >> Database Maintenance Plan Wizard in SQL Server 2000
> >> helps
> >> >> to create a maintenance plan to perform database
> >> backups
> >> >> on a regular basis. How could I create a plan to
> backup
> >> >> file and filegroups on a regular basis.
> >> >>
> >> >> I also appreciate if you would comment on what
> >> conditions
> >> >> would you suggest to place a table in a different
> >> >> filegroup than the rest of the tables in a database?
> >> >>
> >> >> Thank you,
> >> >>
> >> >> -Mitra
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >