Monday, March 26, 2012

Recovery from bak file w/ full text problem

I am having a problem restoring to a new or different database than the backup file being restored was created from. I understand the Move commands, and have figured out how to get this to work by mapping the data and log files from the backup file to the files defined for the restore target database.

The problem is with the full text catalog that is part of the backup.

Here's the T-SQL I'm executing, and the error I receive:

T-SQL:
RESTORE DATABASE New_DB
FROM DISK = 'C:\backup\Old_DB.bak'
WITH RECOVERY, REPLACE,
MOVE 'Old_DB' TO 'C:\SQLData\New_DB.mdf',
MOVE 'Old_DB_log' TO 'C:\SQLData\New_DB_log.ldf'

Error:
Msg 1834, Level 16, State 1, Line 1
The file 'C:\SQLData\FTData\ftKeyWords0007' cannot be overwritten. It is being used by database 'Old_DB'.
Msg 3156, Level 16, State 4, Line 1
File 'sysft_ftKeyWords' cannot be restored to 'C:\SQLData\FTData\ftKeyWords0007'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Not sure what to do about this, any help would be greatly appreciated!
Before, disabled FullText indexes for a old_db and repeat your command.|||You mean turn off the full text index before running the backup?

Yes, that would work, but that may not be feasible. I'd hate to have a job running that backs up a db hourly, and has to regenerate ft indexes every single time for obvious reasons.

This problem makes restoring backups that include full text indexes to a new restore point extremely frustrating, and it really shouldn't be.
sql

No comments:

Post a Comment