Back Up SQL 2005 without Full-Text Index

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.

This site uses Akismet to reduce spam. Learn how your comment data is processed.