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

dhtucker

DECLARE @exitCode INT
DECLARE @sqlErrorCode INT
DECLARE @strSql NVARCHAR(1000)

SET @strSql = '
-sql "RESTORE DATABASE _ynz_tx1 FROM DISK=[\\nas-sql\wilcomp_sql\temporary\YNZ_TX1_205042.sqb] WITH RECOVERY, REPLACE, DISCONNECT_EXISTING, SINGLERESULTSET" '
EXEC master..sqlbackup @strSql, @exitCode OUTPUT, @sqlErrorCode OUTPUT

---

returns error resultset:

---

Restoring _ynz_tx1 (database) from:
\\nas-sql\wilcomp_sql\temporary\YNZ_TX1_205042.sqb

SQL Server error

SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3119: SQL error 3119: Problems were identified while planning for the RESTORE statement. Previous messages provide details.
SQL error 3156: SQL error 3156: File 'sysft_LibraryFullTextCatalog' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\LibraryFullTextCatalog0043'. Use WITH MOVE to identify a valid location for the file.
SQL error 1834: SQL error 1834: The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\LibraryFullTextCatalog0043' cannot be overwritten. It is being used by database 'YNZ_TX1'.

SQL Backup exit code: 1100
SQL error code: 1834

---

same backup file restores successfully using alternate syntax, generated by SQL Backup 6 GUI:

---

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [_ynz_tx1]
FROM DISK = ''\\nas-sql\Wilcomp_SQL\temporary\YNZ_TX1_205042.sqb''
WITH RECOVERY, DISCONNECT_EXISTING,
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'', REPLACE"'

---

Shouldn't both approaches work identically? The whole point of the first syntax would seem to be avoiding the explicit MOVE statements.

Thanks in advance,
Doug Tucker (RenWeb)

petey

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).

dhtucker

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?

petey

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.

dhtucker

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?

petey

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

dhtucker

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).

petey

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.

dhtucker

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.
*/