copying freextex indexes into multiple destination dbs on same dest server

Hi -
I've looked on Microsoft's web site:
http://support.microsoft.com/default...;EN-US;Q240867
and not yet been able to find a full answer to my question.
I'd appreciate any insight from others:
I have a database that has full text indexes on it, that I need to log ship
to multiple other (destination) servers. Also, to promote availability on
each of the destination servers, I plan to restore into two databases:
dest_db and dest_db_alt. Both are to have the same content, but different db
names. I intend to restore one set of dbs/logs from the source server into
standby mode in each destination database, sequentially, so that, with
proper client re-direction, a client hitting the destination server will
always get redirected to a read-only db in standby mode. While one db is
restoring, the other is readable. Ok, fair enough.
Now, the tricky part. The source db is freextex indexed, and one can't
build/rebuild freetext indexes on a db in standby or read-only mode. So,
apparently I'll have to copy the freetext index directory trees from the
source server and restore them on the destination server. What I'm uncertain
about is whether I'll be able to:
a) Use one set of freetext indexes files for both destination dbs (I don't
think so, If I understand things correctly),
b) Properly set the registry keys and directory/file names on the freetext
catalogs on the destination server so that even though at least one of the
destination dbs (necessarily) has a different dbid than the source database,
it will still be able to have a functioning freetext index. From the
generally related material I read it seems as if the registry keys map the
freetext catalog path(s) into SQL Server, and those catalog paths use dbids
and ftcatids integrated into their folder names to map a set of catalog
files to a dbid.
So here is my question: Can I just make multiple copies of the freetext
catalog files, differing only in the dbid portion of the folder name, and
then, using registry keys, map the newly created Freetext catalog folder(s)
into SQL Server with the relevant dbids on the destination server ?
Are dbids, or other database specific unique identifiers, hard coded within
the freetext catalog files themselves ?
Is there other important information I have to consider here ?
Thanks
Steve
Use replication for this. Create your catalogs and full text indexes using a
post snapshot script or create the tables and catalogs in advance, full text
index the tables, and then configure your article to delete the data not
drop and recreate the table (in the article properties section select the
browse button to the right of your table name, and in the snapshot tab, in
the name conflicts section , select delete all data).
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
"Steve" <Steve_a013452@.newsgroups.nospam> wrote in message
news:OJ9F4IqGGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi -
> I've looked on Microsoft's web site:
> http://support.microsoft.com/default...;EN-US;Q240867
> and not yet been able to find a full answer to my question.
> I'd appreciate any insight from others:
> I have a database that has full text indexes on it, that I need to log
> ship to multiple other (destination) servers. Also, to promote
> availability on each of the destination servers, I plan to restore into
> two databases: dest_db and dest_db_alt. Both are to have the same content,
> but different db names. I intend to restore one set of dbs/logs from the
> source server into standby mode in each destination database,
> sequentially, so that, with proper client re-direction, a client hitting
> the destination server will always get redirected to a read-only db in
> standby mode. While one db is restoring, the other is readable. Ok, fair
> enough.
> Now, the tricky part. The source db is freextex indexed, and one can't
> build/rebuild freetext indexes on a db in standby or read-only mode. So,
> apparently I'll have to copy the freetext index directory trees from the
> source server and restore them on the destination server. What I'm
> uncertain about is whether I'll be able to:
> a) Use one set of freetext indexes files for both destination dbs (I don't
> think so, If I understand things correctly),
> b) Properly set the registry keys and directory/file names on the freetext
> catalogs on the destination server so that even though at least one of the
> destination dbs (necessarily) has a different dbid than the source
> database, it will still be able to have a functioning freetext index. From
> the generally related material I read it seems as if the registry keys map
> the freetext catalog path(s) into SQL Server, and those catalog paths use
> dbids and ftcatids integrated into their folder names to map a set of
> catalog files to a dbid.
> So here is my question: Can I just make multiple copies of the freetext
> catalog files, differing only in the dbid portion of the folder name, and
> then, using registry keys, map the newly created Freetext catalog
> folder(s) into SQL Server with the relevant dbids on the destination
> server ?
> Are dbids, or other database specific unique identifiers, hard coded
> within the freetext catalog files themselves ?
> Is there other important information I have to consider here ?
> Thanks
> Steve
>

copying DBs w/out Copy Wizard

Is it a bad thing to do a manual copy of all my databases
from one server to another in Win Exp? Will it hurt
anything? I detached the DB, copied it over, and then
reattached on the new server. ?Detach/attach is the way the copy database wizard does it as well, so that
is fine.
The other option is to do a backup, copy the backup and then do a restore on
the new server.
--
Jacco Schalkwijk
SQL Server MVP
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?|||If you're concerned run DBCC CHECKDB after you have successfully attached
the db. If the file is very large > 40Gb I generally use robocopy
--
HTH
Ryan Waight, MCDBA, MCSE
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?|||See if these help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?

Copying dBs

I am running SQL 2000 (using replication) & Win
2000.
We are getting a new production sever and would like to
know the best practice for moving all the SQL data from
the Old Server to the New Server. There are a few
questions the concern me.
- Will I have to setup replication on the new server?
- Will all the dB defaults be preserved?
Larry,
if the server names are the same, and you have backups of all databases, then you should be able torestore them on the new production server. If you're doing transactional replication, then make sure the distribution database is synchronized with the prod
uction database. If the server names are different, then I'd script out the publications and recreate on the new server. Provided no subscribers are allowed to do any data changes during this time, you should be able to add the subscriptions without apply
ing the data snapshot (@.sync_type ='none').
HTH,
Paul Ibison
|||In the long run you would be best to drop the subscriptions and publications
on the current production server. Back it up and restore it to the new
publisher.
Recreate the publications from scripts and then do create a nosync
subscription to your existing subscriber.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:238c01c427b5$7a2e6040$a501280a@.phx.gbl...
> I am running SQL 2000 (using replication) & Win
> 2000.
> We are getting a new production sever and would like to
> know the best practice for moving all the SQL data from
> the Old Server to the New Server. There are a few
> questions the concern me.
> - Will I have to setup replication on the new server?
> - Will all the dB defaults be preserved?
>

Copying DBs

What is the Syntax for a statement that can copy one MS SQL DB to another Database (on a different server). Or is this even possible?See BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_0ltf.asp).

-PatP|||BACKUP DATABASE
robocopy
RESTORE DATABASE

-or-

Detach database
robocopy
Attach database

-or-

Clone...lucky enough to have a SAN??

So, why don't you explain what you want to do this for. Then, we can give you a little better advice.|||What is the Syntax for a statement that can copy one MS SQL DB to another Database (on a different server). Or is this even possible?
It'd be easier for you to look into Database Wizards section.

Copying data from PRD to TST

I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also.

Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.

What is the easiest way to do so?

I moved your post from the FAQ fourm to here. Please note that the FAQ forum is for posting answers to commonly asked questions, not asking questions.

Ryan

|||I've always used SQL Server's built-in Data Transformation Services (DTS) to transfer data from one database to another. Be sure not to check the option to Create Destination Objects; choose only to Copy Data.|||Can you suggest a good resource for DTS? If I Google, there are so many hits that it is kinda hard to find a good one.|||Which version of SQL Server are you running? (Please be sure to put this information into any of your posts related to SQL Server.)|||Sorry about that. SQL Server 2005.|||Well, I haven't used SQL Server 2005 much; I currently only have SQL Express on my machine, and no Management Studio. However, I found this article which might help you get started:SQL Server 2005 Integration Services - Import and Export Wizard.