August 3, 2009 at 10:15 am
I'm trying to restore a SQL Server 2005 SP3 database on a "source" server to a different SQL Server 2005 SP3 "target" server.
The "source" database, which is named "theOcean", contains a full text catalog, is backed up via full and differential backups, and resides on the "source" server's D: drive.
The "target" database, which is named "jpo-homeless-restore", will reside on the "target" server's C: drive.
I tried running the following commands on the "target" server:
RESTORE DATABASE [jpo-homeless-restore] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\JPO-Homeless\JPO-Homeless_backup_200908030913.bak' WITH FILE = 1, NORECOVERY, REPLACE,
move 'THEOcean_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\jpo-homeless-restore.ldf',
move 'THEOcean' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\jpo-homeless-restore.mdf',
move 'sysft_client' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\jpo-homeless-restore.client\';
GO
RESTORE DATABASE [jpo-homeless-restore] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\JPO-Homeless\JPO-Homeless_backup_200908030914.bak' WITH FILE = 1, NORECOVERY ;
GO
RESTORE DATABASE [jpo-homeless-restore] WITH RECOVERY
GO
Please note the following:
JPO-Homeless_backup_200908030913.bak - full database backup from the "source" server
JPO-Homeless_backup_200908030914.bak - differential database backup from the "source" server
Here's the error message resulting from the above commands:
Processed 33888 pages for database 'jpo-homeless-restore', file 'THEOcean' on file 1.
Processed 2 pages for database 'jpo-homeless-restore', file 'THEOcean_log' on file 1.
Processed 478 pages for database 'jpo-homeless-restore', file 'sysft_client' on file 1.
RESTORE DATABASE successfully processed 34367 pages in 209.093 seconds (1.346 MB/sec).
Location: filesystem.cpp:502
Expression: newTree.IsInArchive ()
SPID: 86
Process ID: 1416
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Additionally, I've attached the SQL Server error log.
I was able to perform the following troubleshooting scenarios with success:
1. execute the 3 "RESTORE DATABASE" commands without the "move" (i.e., restore the full backup without recovery, restore the differential backup without recovery, then recover the database)
2. execute the 3 "RESTORE DATABASE" commands with the "move" only for the data (.mdf) and log (.ldf) files (i.e., remove the line starting with "move 'sysft_client' ...")
3. execute only the full backup with the "move" (i.e., remove the statement containing "JPO-Homeless_backup_200908030914.bak")
Does anyone have any ideas why the "differential backup with move" scenario is failing and/or suggestions on a workaround/fix?
August 5, 2009 at 7:11 am
Would not restoring the full text catalog until all restores are completed be a viable option? If so, how would that be accomplished?
September 15, 2009 at 8:01 am
In order to move the full text indices when the database restore involves a differential backup, the "move" command can be used as long as the final full-text index location does not end in a '\'. If it does contain a '\', you will get the "A system assertion check has failed" error when restoring the differential backup only. The "move" command with a '\' will succeed when restoring full database backups or transaction log backups.
Here's the commands that I used:
RESTORE DATABASE [jpo-homeless-restore] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\JPO-Homeless\JPO-Homeless_backup_200908030913.bak' WITH FILE = 1, NORECOVERY, REPLACE,
move 'THEOcean_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\jpo-homeless-restore.ldf',
move 'THEOcean' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\jpo-homeless-restore.mdf',
move 'sysft_client' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\jpo-homeless-restore.client';
GO
RESTORE DATABASE [jpo-homeless-restore] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\JPO-Homeless\JPO-Homeless_backup_200908030914.bak' WITH FILE = 1, NORECOVERY ;
GO
RESTORE DATABASE [jpo-homeless-restore] WITH RECOVERY
GO
Additionally, I tried doing a database file restore via the following steps:
1. restore the database data and log files only
2. bring the database back online via database recovery (full text index will be offline after the database recovery)
3. put the database offline
4. move/rename the full text index on the computer's hard drive
5. change the full text's hard drive location in the system catalogs via the ALTER DATABASE command
6. bring the database back online
7. rebuild the full text index
This only works when the database is using the full recovery model. As a result, I wasn't able to utilize a database file restore as my solution.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply