Solved

How to migrate sql 200o full text to 2005 server

Posted on 2007-11-22
10
404 Views
Last Modified: 2012-05-05
Hi,
How to migrate ms sql 2000 full text index to ms sql 2005 server?

I want script all full text indexes in the sql 2000 server and I want execute in sql 2005 server.
0
Comment
Question by:Srikanthsubramanian
  • 6
  • 2
10 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20333845
I think you would restore the database as normal, ensure that mssearch was enabled on the 2005 server and then 'create full text index'  against the catalogue.    I don't think you would want to script out the index and re-apply it on the 2005 box as a migration strategy.  

AustinSeven
0
 

Author Comment

by:Srikanthsubramanian
ID: 20333927
Actually I have more than 40 dbs and more than 100 full text indexes, so I cant create full text individually. The script will easy my job.
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20334043
Ok, I see.   Well, you can write a script on the target 2005 server to populate the indexes and so on.     If you look in Books Online for sp_fulltext_table for example, you can take it from there.   2005 still supports these system stored procedures but the new method is to use CREATE, ALTER.    Take your pick.

AustinSeven
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20334058
You could write a select statement on the 2000 server to return the list of databases and FT indexes.  Then you could copy that info to the 2005 target server and write another select that generates the required list of t-sql commands to carry out the required actions.   This would be much quicker than hand-crafting the script of course.   Do you see what I mean?

AustinSeven
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Expert Comment

by:AustinSeven
ID: 20334144
Here, I wrote an example for you to select the info into a table on the 2000 server.    From this, you can write a SELECT statement that inserts the command syntax to do whatever you want to your FT indexes on the 2005 server.

AustinSeven
create table FTList (

dbname varchar(50),

FTIndex varchar(50)

)
 

set nocount on

declare dbcur cursor for

select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')
 

open dbcur 
 

declare @dbname varchar(50), @cmd varchar (256)
 

fetch next from dbcur into @dbname
 

while (@@fetch_status <> -1)

begin
 

    select @cmd = 'insert into FTList select ' + quotename(@dbname,'''') + ', name from ' + @dbname + '.dbo.sysfulltextcatalogs'

    exec (@cmd)

    fetch next from dbcur into @dbname

end
 

close dbcur

deallocate dbcur
 

select * from ftlist

Open in new window

0
 
LVL 10

Accepted Solution

by:
AustinSeven earned 500 total points
ID: 20334168
and here is the SELECT statement that, for example, would populate all the full text indexes.   I haven't tested this with databases that have more than one FT Index but I think you can handle it.

AustinSeven
select 'exec ' + dbname + '..sp_fulltext_catalog ' + quotename(FTIndex,'''') + ', ''start_full''' from FTList

Open in new window

0
 

Author Comment

by:Srikanthsubramanian
ID: 20334588
Great AustinSeven,

I need more... Where can i put table name, field names, filename.

EM we can select table name, field name, output file...

The table sysfulltextcatalogs do not have that much of informations....
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20334692

You can use the SQL Server 2000 sp_help_fulltext_* system stored procedures.   Look in Books Online for a good description of what info they return.   For example, sp_help_fulltext_catalogs will give you the path to the output file.   sp_help_fulltext_tables gives you the table related info.   Experiment with these sprocs to see which ones you need to return the required info.   Then, when you have that understood, just dump the output of these sprocs into the table or a staging/ temp table.

You should have all the info to complete now, I think.    

AustinSeven

create table #ft_cats (

ftcatid tinyint,

name varchar(50),

path varchar (100),

status tinyint,

number_fulltext_tables tinyint)
 

insert into #ft_cats

exec sp_help_fulltext_catalogs
 

-- can 'insert into' another table to collect the data.

-- or assign path to a variable @path = path... to use later in the script

select path from #ft_cats
 

drop table #ft_cats

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now