SQL Server - Detach / Attach / Full Text Catalog

I recently had a need to detach a SQL Server database, zip up the files (not included here), reattach the database, and recreate the full text index catalog for my text searches. Here's the script I wrote for VBScript. Just populate the appropriate variables with your DB info and you are all set.

I recently had a need to detach a SQL Server database,
zip up the files (not included here), reattach the
database, and recreate the full text index catalog for
my text searches. Here's the script I wrote for
VBScript. Just populate the appropriate variables with
your DB info and you are all set.


Dim oSQLServer
Dim oFullTextCatalog
Dim oTable
Dim sDBPwd
Dim sDBName
Dim sDBFiles
Dim sDBPath
Dim sPrefix

sDBName = "Your database name goes here"
sDBPwd = "Your database password"
sPrefix = "ftcat"
sDBPath = "Your path to the actual db files"
sDBFiles = sDBPath & "\" & sDBName & "_Data.mdf," & sDBPath & "\" & sDBName
&"_Log.ldf"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

oSQLServer.Connect "(local)", "admin account", sDBPwd
oSQLServer.DetachDB sDBName, True

' At this point, the database has been completely
' disconnected from SQL Server. You can ftp,zip,
' or simply copy the files. The next statement for
' attaching the database makes it available in
' SQL Server again.

oSQLServer.AttachDB sDBName, sDBFiles
oSQLServer.Databases(sDBName).DisableFullTextCatalogs
oSQLServer.Databases(sDBName).EnableFullTextCatalogs
oSQLServer.Databases(sDBName).RemoveFullTextCatalogs

Set oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")

oFullTextCatalog.Name = sPrefix & sDBName

oSQLServer.Databases(sDBName).FullTextCatalogs.Add oFullTextCatalog

Set oTable = oSQLServer.Databases(sDBName).Tables("table name")

oTable.FullTextCatalogName = sPrefix & sDBName
oTable.UniqueIndexForFullText = "Primary key index name for the table"
oTable.FullTextIndex = True
oTable.Columns("BodyText").FullTextIndex = True
oTable.Columns("Subject").FullTextIndex = True
oTable.FullTextIndexActive = True

Set oFullTextCatalog = oSQLServer.Databases(sDBName).FullTextCatalogs(sPrefix
&sDBName)

oFullTextCatalog.Start (SQLDMOFullText_Full)

oSQLServer.Disconnect

Set oFullTextCatalog = Nothing
Set oTable = Nothing
Set oSQLServer = Nothing



Submission Date:  9/23/2005 3:02:52 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com

By Robbe Morris   Popularity  (697 Views)
Picture
Biography - Robbe Morris
Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.  Robbe also loves to scuba dive and go deep sea fishing in the Florida Keys or off the coast of Daytona Beach. Microsoft MVP