Restoring filegroup which has indexes only

We are planning to put our tables on one filegroup and the indexes on the
other. In SQL 2005, it is possible to restore just 1 filegroup and continue
operations. This sounds fine if the tables and the associated indexes are on
the same filegroup. But what happens in my case ? If the index filegroup
drive fails and is restored from a backup, the indexes on it will be out of
sync. My question is - will we have to rebuild all the indexes (in which
case, it won't make sense to restore the filegroup in the first place), and
is it actually possible to restore the filegroup which holds indexes only an
d
continue operations as normal, or would it cause problems ?Hi Pranil
If you restore a filegroup, you must also restore log backups to bring the
filegroup up to date (i.e. get it in sync) with the rest of the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pranil" <Pranil@.discussions.microsoft.com> wrote in message
news:05780582-E1EA-4EF6-94CF-D19982EAB574@.microsoft.com...
> We are planning to put our tables on one filegroup and the indexes on the
> other. In SQL 2005, it is possible to restore just 1 filegroup and
> continue
> operations. This sounds fine if the tables and the associated indexes are
> on
> the same filegroup. But what happens in my case ? If the index filegroup
> drive fails and is restored from a backup, the indexes on it will be out
> of
> sync. My question is - will we have to rebuild all the indexes (in which
> case, it won't make sense to restore the filegroup in the first place),
> and
> is it actually possible to restore the filegroup which holds indexes only
> and
> continue operations as normal, or would it cause problems ?
>

Restoring filegroup which has indexes only

We are planning to put our tables on one filegroup and the indexes on the
other. In SQL 2005, it is possible to restore just 1 filegroup and continue
operations. This sounds fine if the tables and the associated indexes are on
the same filegroup. But what happens in my case ? If the index filegroup
drive fails and is restored from a backup, the indexes on it will be out of
sync. My question is - will we have to rebuild all the indexes (in which
case, it won't make sense to restore the filegroup in the first place), and
is it actually possible to restore the filegroup which holds indexes only and
continue operations as normal, or would it cause problems ?
Hi Pranil
If you restore a filegroup, you must also restore log backups to bring the
filegroup up to date (i.e. get it in sync) with the rest of the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pranil" <Pranil@.discussions.microsoft.com> wrote in message
news:05780582-E1EA-4EF6-94CF-D19982EAB574@.microsoft.com...
> We are planning to put our tables on one filegroup and the indexes on the
> other. In SQL 2005, it is possible to restore just 1 filegroup and
> continue
> operations. This sounds fine if the tables and the associated indexes are
> on
> the same filegroup. But what happens in my case ? If the index filegroup
> drive fails and is restored from a backup, the indexes on it will be out
> of
> sync. My question is - will we have to rebuild all the indexes (in which
> case, it won't make sense to restore the filegroup in the first place),
> and
> is it actually possible to restore the filegroup which holds indexes only
> and
> continue operations as normal, or would it cause problems ?
>

Restoring filegroup which has indexes only

We are planning to put our tables on one filegroup and the indexes on the
other. In SQL 2005, it is possible to restore just 1 filegroup and continue
operations. This sounds fine if the tables and the associated indexes are on
the same filegroup. But what happens in my case ? If the index filegroup
drive fails and is restored from a backup, the indexes on it will be out of
sync. My question is - will we have to rebuild all the indexes (in which
case, it won't make sense to restore the filegroup in the first place), and
is it actually possible to restore the filegroup which holds indexes only and
continue operations as normal, or would it cause problems ?Hi Pranil
If you restore a filegroup, you must also restore log backups to bring the
filegroup up to date (i.e. get it in sync) with the rest of the database.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pranil" <Pranil@.discussions.microsoft.com> wrote in message
news:05780582-E1EA-4EF6-94CF-D19982EAB574@.microsoft.com...
> We are planning to put our tables on one filegroup and the indexes on the
> other. In SQL 2005, it is possible to restore just 1 filegroup and
> continue
> operations. This sounds fine if the tables and the associated indexes are
> on
> the same filegroup. But what happens in my case ? If the index filegroup
> drive fails and is restored from a backup, the indexes on it will be out
> of
> sync. My question is - will we have to rebuild all the indexes (in which
> case, it won't make sense to restore the filegroup in the first place),
> and
> is it actually possible to restore the filegroup which holds indexes only
> and
> continue operations as normal, or would it cause problems ?
>

Restoring DB with fulltext index to another server: index recreation steps?

When a database with fulltext indexes is restored to a different
server, what are the appropriate steps to take, and in what order,
to recreate the indexes on the new machine if it has a different
directory structure than the original server? Should the indexes
be removed and then recreated? Any problem removing the index if
the native file-system files cannot be located? The native file-
system index files are not brought over to the new machine as part
of the backup/restore process, is that right?
Thanks
TR
TR,
The best source of info on this is the KB article 240867 (Q240867) "INF: How
to Move, Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867 This KB
article should answer most of your questions. However, in regards to the
following questions:
1. Any problem removing the index if the native file-system files cannot be
located?
A. It would be best to drop the FT Catalog prior to backing up the database
in this case.
2. The native file-system index files are not brought over to the new
machine as part of the backup/restore process, is that right?
A. That is correct. However, this will change in SQL Server 2005 (Yukon) as
the SQL Server backup & restore processes will be altered to include the FT
Catalog folder and files in the SQL Server 2005 database backup file.
Regards,
John
"TR" <TR@.anonymous.biz> wrote in message
news:MPG.1b2ba884d2593bac9896f7@.msnews.microsoft.c om...
> When a database with fulltext indexes is restored to a different
> server, what are the appropriate steps to take, and in what order,
> to recreate the indexes on the new machine if it has a different
> directory structure than the original server? Should the indexes
> be removed and then recreated? Any problem removing the index if
> the native file-system files cannot be located? The native file-
> system index files are not brought over to the new machine as part
> of the backup/restore process, is that right?
> Thanks
> TR

Restoring database over existing database and full-text catlog integrity

I know that if you have to drop a full-text enable database and restore it,
you will have to rebuild the full-text indexes. My question is, if you have
a database where the data has not changed since your last backup and you
restore over top of the existing database, is the full-text index's
integrity okay?
I have recently restored a large full-text enabled database over top of an
existing one after an upgrade failed and the full-text appears to be intact.
I can run full-text searches. Does anyone know if there are any potential
issues? The table has about 28 million rows and the full-text rebuild would
take days so I am trying to avoid that.
Thanks,
~Travis
Yes, this should work fine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Travis Nordrum" <tnordrum@.krollontrack.com> wrote in message
news:%23BH%23WEX9FHA.3048@.TK2MSFTNGP10.phx.gbl...
>I know that if you have to drop a full-text enable database and restore it,
>you will have to rebuild the full-text indexes. My question is, if you
>have a database where the data has not changed since your last backup and
>you restore over top of the existing database, is the full-text index's
>integrity okay?
> I have recently restored a large full-text enabled database over top of an
> existing one after an upgrade failed and the full-text appears to be
> intact. I can run full-text searches. Does anyone know if there are any
> potential issues? The table has about 28 million rows and the full-text
> rebuild would take days so I am trying to avoid that.
> Thanks,
> ~Travis
>
|||Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecSzEWf9FHA.3772@.TK2MSFTNGP10.phx.gbl...
> Yes, this should work fine.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Travis Nordrum" <tnordrum@.krollontrack.com> wrote in message
> news:%23BH%23WEX9FHA.3048@.TK2MSFTNGP10.phx.gbl...
>