errors 1834, 3013, 3119, 3156 restore backup w/ full-text

• 
  The full text catalog data file is currently in use by the 'YNZ_TX1' database, which is why you cannot overwrite it, even with the REPLACE option. This is because you are restoring the backup set as a new database (_YNZ_TX1) instead of replacing the existing database (YNZ_TX1).

  petey August 14, 2011 21:17

  - 0 +
• 
  If I'm restoring a backup to a second (target) database, why does it care that the full-text catalog of the first (source) database is in use by the first database? Shouldn't it be creating a second full-text catalog as a part of the target database?

  dhtucker August 15, 2011 11:51

  - 0 +
• 

  why does it care that the full-text catalog of the first (source) database is in use by the first database?

  Because SQL Server uses the full text details from the backup set to determine where to create the full text catalog.
  

  Shouldn't it be creating a second full-text catalog as a part of the target database?

  Without any MOVE options, SQL Server will use the details from the backup set to recreate the database. Even if you did not have a full text catalog in that database, the restore would still fail as SQL Server would try to restore the database using the original databases' file names for the data file and transaction log. And because the original database still exists, the data files and transaction log cannot be overwritten.

  petey August 15, 2011 12:01

  - 0 +
• 
  For database backups NOT containing full-text catalogs, the first RESTORE syntax works without requiring explicit MOVE statements:
  
  -sql "RESTORE DATABASE _ynz_tx1 FROM DISK=[\\nas-sql\wilcomp_sql\temporary\YNZ_TX1_205042.sqb] WITH RECOVERY, REPLACE, DISCONNECT_EXISTING, SINGLERESULTSET" '
  
  This form is able to link the logical names from the backup to the phyical paths of the target database and overwrite the contents of the target without needing explicit MOVE statements. What's different about full-text catalogs?

  dhtucker August 15, 2011 15:51

  - 0 +
• 
  Did you drop the '_ynz_tx1' database before running the restore? If the '_ynz_tx1' existed before you performed the restore, then SQL Server will use the file names from the existing database during the restore.
  
  Here's an example of what I mean:
  

  PRINT '*****   creating database'
  CREATE DATABASE rgdb13892test
  GO
  PRINT ''
  PRINT '*****   backing up database'
  BACKUP DATABASE rgdb13892test TO DISK = 'c:\temp\rgdb13892test.bak' WITH FORMAT
  GO
  PRINT ''
  PRINT '*****   restoring database without MOVE options'
  RESTORE DATABASE _rgdb13892test FROM DISK = 'c:\temp\rgdb13892test.bak' WITH REPLACE
  GO
  PRINT ''
  PRINT '*****   restoring database with MOVE options'
  RESTORE DATABASE _rgdb13892test FROM DISK = 'c:\temp\rgdb13892test.bak' WITH MOVE 'rgdb13892test' TO 'c:\temp\rgdb13892test.mdf', MOVE 'rgdb13892test_log' TO 'c:\temp\rgdb13892test.ldf', REPLACE
  GO
  PRINT ''
  PRINT '*****   backing up database to new file'
  BACKUP DATABASE rgdb13892test TO DISK = 'c:\temp\rgdb13892test_02.bak' WITH FORMAT
  GO
  PRINT ''
  PRINT '*****   restoring database from second file without MOVE options'
  RESTORE DATABASE _rgdb13892test FROM DISK = 'c:\temp\rgdb13892test_02.bak' WITH REPLACE
  GO
  USE rgdb13892test
  GO
  PRINT ''
  PRINT '*****   creating full text catalog'
  CREATE FULLTEXT CATALOG ftCatalogrgdb13892test AS DEFAULT
  GO
  PRINT ''
  PRINT '*****   backing up database with full text catalog'
  BACKUP DATABASE rgdb13892test TO DISK = 'c:\temp\rgdb13892test_ft.bak' WITH FORMAT
  GO
  USE master
  GO
  PRINT ''
  PRINT '*****   restoring database with full text catalog'
  RESTORE DATABASE _rgdb13892test FROM DISK = 'c:\temp\rgdb13892test_ft.bak' WITH REPLACE
  GO
  PRINT ''
  PRINT '*****   dropping test databases'
  DROP DATABASE rgdb13892test
  GO
  DROP DATABASE _rgdb13892test
  GO
  

  petey August 15, 2011 19:32

  - 0 +
• 
  I did not drop _ynz_tx1 prior to running the restore - the intent is to overlay the test database _ynz_tx1 with the contents of the production database YNZ_TX1.
  
  The restore is not attempting to overwrite the files belonging to _ynz_tx1 (the target, test database) as one would expect it to do. Instead, according to SQL error 1834, it's complaining about being unable to overwrite the file \FTData\LibraryFullTextCatalog0043 because it's in use by YNZ_TX1 (the production database from which the backup file was generated).

  dhtucker August 16, 2011 00:27

  - 0 +
• 
  Database _tnz_tx1 does not currently have a full text catalog. Hence, when you attempt to restore from a backup set containing a full text catalog, SQL Server tries to use the same full text catalog directory as the original database. And it can't do that because the original database is still using that directory.

  petey August 16, 2011 02:52

  - 0 +
• 
  Confirmed!
  
  If I use the explicit RESTORE...MOVE syntax to restore a production backup (creating the initial full-text catalog) to my test database, subsequent restores using the implicit syntax work flawlessly (see below).
  
  It seems strange that SQL Server knows how to create .mdf and .ldf files when required by a RESTORE command and yet defaults to attempting a reuse of the source database's full-text catalog when one doesn't exist in the target database - I'm having a hard time imagining a scenario where that's the behavior I'd want. Sounds like a MS "feature" to me.
  
  Thanks for your research!
  
  ~Doug

  ---

  /*
  server app1db (SQL2005)
  
  Command generated by SQL Backup GUI:
  */
  
  EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [_ynz_tx1]
  FROM DISK = ''\\nas-sql\Wilcomp_SQL\ewhserver520\weekly\EWHSERVER520_YNZ_TX1_FULL_201108140246.sqb''
  WITH RECOVERY, MOVE ''SAMPLE_Data'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_ynz_tx1_1.mdf'',
  MOVE ''sysft_LibraryFullTextCatalog'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_ynz_tx1_2'',
  MOVE ''SAMPLE_Log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_ynz_tx1_3.ldf''"'
  
  /*
  results:
  
  Restoring _ynz_tx1 (database) from:
  \\nas-sql\Wilcomp_SQL\ewhserver520\weekly\EWHSERVER520_YNZ_TX1_FULL_201108140246.sqb
  
  Processed 45856 pages for database '_ynz_tx1', file 'SAMPLE_Data' on file 1.
  Processed 3 pages for database '_ynz_tx1', file 'SAMPLE_Log' on file 1.
  Processed 258 pages for database '_ynz_tx1', file 'sysft_LibraryFullTextCatalog' on file 1.
  RESTORE DATABASE successfully processed 46116 pages in 14.318 seconds (26.384 MB/sec).
  SQL Backup process ended.
  
  exitcode 0
  sqlerrorcode 0
  filename001 \\nas-sql\Wilcomp_SQL\ewhserver520\weekly\EWHSERVER520_YNZ_TX1_FULL_201108140246.sqb
  */
  
  EXECUTE master..sqlbackup '-sql "RESTORE DATABASE _ynz_tx1
  FROM DISK=[\\nas-sql\wilcomp_sql\ewhserver520\weekly\EWHSERVER520_YNZ_TX1_FULL_201108140246.sqb]
  WITH RECOVERY, REPLACE, DISCONNECT_EXISTING, SINGLERESULTSET" '
  
  /*
  results:
  
  Restoring _ynz_tx1 (database) from:
  \\nas-sql\wilcomp_sql\ewhserver520\weekly\EWHSERVER520_YNZ_TX1_FULL_201108140246.sqb
  
  Processed 45856 pages for database '_ynz_tx1', file 'SAMPLE_Data' on file 1.
  Processed 3 pages for database '_ynz_tx1', file 'SAMPLE_Log' on file 1.
  Processed 258 pages for database '_ynz_tx1', file 'sysft_LibraryFullTextCatalog' on file 1.
  RESTORE DATABASE successfully processed 46116 pages in 15.549 seconds (24.295 MB/sec).
  SQL Backup process ended.
  */

  dhtucker August 16, 2011 12:03

  - 0 +

Add comment

Please sign in to leave a comment.