errors 1834, 3013, 3119, 3156 restore backup w/ full-text
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)
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)
Doug Tucker
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS
Comments
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
-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?
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS
Here's an example of what I mean:
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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).
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
*/
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS