This script was supposed to be a download from the previous post, Moving Liberum Help Desk to SQL 2005 Express. However, WordPress won’t let me set up a SQL script or a zip file as a download, so I’m putting this out there as a plain old post. I’ll use a <pre> tag so it may overlap the right column, but at least it won’t contain HTML. Cut and paste!
The Script
/* 07/15/2010: It's pretty difficult to move a full-text index to a new server, and the HelpDesk is so small that an index can be regenerated quickly. So in case the backup needs to be restored elsewhere, before doing the backup, drop and disable the full-text index. Re-enable the index after the backup completes. This approach assumes the database uses the Simple recovery model. It seems that otherwise, log backups are required to fully remove the full-text catalog. Note: Adjust the path to your backup location (line 16) and the name of the primary key index on the problems table (line 50). Copyright 2010 by Mark Berry, MCB Systems, www.mcbsys.com. Free for personal or commercial use. Use at your own risk. */ DECLARE @BackupPath NVARCHAR(255) DECLARE @dateYYYYMMDD NCHAR(8) -- Set the next line to the actual backup path SET @BackupPath = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\' -- Convert today's date to YYYYMMDD form. -- Backup name will be @BackupPath + 'HelpDesk_db_YYYYMMDD.BAK SET @dateYYYYMMDD = REPLACE(CONVERT(VARCHAR, GETDATE(),112),'/','') /* Step 1: Remove Full-Text index and catalog, then disable full-text indexing. */ USE HelpDesk DROP FULLTEXT INDEX ON problems DROP FULLTEXT CATALOG HelpDesk_Problems_FullTextIndex EXEC sp_fulltext_database 'disable' /* Step 2: Back up the database */ SET @BackupPath = @BackupPath + N'HelpDesk_db_' + @dateYYYYMMDD + N'.BAK' BACKUP DATABASE [HelpDesk] TO DISK = @BackupPath WITH NOFORMAT, INIT, NAME = N'HelpDesk-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 /* Step 3: Enable full-text indexing, then recreate the catalog and index Note: A full population is started automatically when the index is created.. */ USE HelpDesk EXEC sp_fulltext_database 'enable' CREATE FULLTEXT CATALOG HelpDesk_Problems_FullTextIndex AS DEFAULT CREATE FULLTEXT INDEX ON problems (title LANGUAGE 1033, [description] LANGUAGE 1033, solution LANGUAGE 1033) KEY INDEX PK__problems__060DEAE8 ON HelpDesk_Problems_FullTextIndex WITH CHANGE_TRACKING AUTO -- Next line can be used to list full-text catalogs -- select * from sysfulltextcatalogs