Restore log shows file error after restore
eilandd
Posts: 44
We are currently on SQL Backup 4.6.0.815.
From an automated restore we received notification that the restore had failed. However, the restore log showed that the only error was after the 'RESTORE DATABASE successfully processed' message. The error we received was 'Cannot open file' on the input backup file. Can anyone tell us what SQLBackup is trying to do with the input file after a restore?
From an automated restore we received notification that the restore had failed. However, the restore log showed that the only error was after the 'RESTORE DATABASE successfully processed' message. The error we received was 'Cannot open file' on the input backup file. Can anyone tell us what SQLBackup is trying to do with the input file after a restore?
Comments
The only thing that comes to my mind is that you are using RESTORE WITH MOVETO. You'd commonly see this in a log shipping script to move log files out of the log shipping directory after they're processed.
The situation is an automated creation of a reporting copy of a database. The reporting copy is on a different server so, yes, the MOVETO option is being used. But this is not a log shipping scenario. It is a full database restore, no diffs or logs.
Could you pls post the full contents of the log? Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Here's the sanitized log:
SQL Backup log file
5/21/2007 2:45:15 AM: Restoring Target_DB (database) from:
\\Source_Server\sqlbackup\Source_DB\Source_DB_FULL_20070521_0115.sqb
5/21/2007 2:45:15 AM: RESTORE DATABASE [Target_DB] FROM DISK = '\\Source_Server\sqlbackup\Source_DB\Source_DB_FULL_20070521_0115.sqb' WITH RECOVERY, MOVE 'DB_Data' TO 'g:\sqldata\Target_DB_Data.mdf', MOVE 'Other_Data' TO 'g:\sqldata\Target_DB_Other_Data.ndf', MOVE 'Consultant_Data' TO 'g:\sqldata\Target_DB_Consultant_data.NDF', MOVE 'Consultant_Data2' TO 'g:\sqldata\Target_DB_Consultant_Data2.ndf', MOVE 'Sales_Data1' TO 'g:\sqldata\Target_DB_Sales_Data.NDF', MOVE 'Sales_Data2' TO 'g:\sqldata\Target_DB_Sales_Data2.NDF', MOVE 'Sales_Index' TO 'g:\sqldata\Target_DB_Sales_Index.NDF', MOVE 'Consultant_Index2' TO 'g:\sqldata\Target_DB_Consultant_Index2.NDF', MOVE 'Consultant_Index' TO 'g:\sqldata\Target_DB_Consultant_Index.NDF', MOVE 'Operations_Data1' TO 'g:\sqldata\Target_DB_Operations_Data.NDF', MOVE 'Operations_Data2' TO 'g:\sqldata\Target_DB_Operations_Data2.NDF', MOVE 'Operations_Index' TO 'g:\sqldata\Target_DB_Operations_Index.NDF', MOVE 'DB_Log' TO 'g:\sqldata\Target_DB_Log.LDF', REPLACE
Processed 2960 pages for database 'Target_DB', file 'DB_Data' on file 1.
Processed 64 pages for database 'Target_DB', file 'Other_Data' on file 1.
Processed 306232 pages for database 'Target_DB', file 'Consultant_Data' on file 1.
Processed 310592 pages for database 'Target_DB', file 'Consultant_Data2' on file 1.
Processed 89584 pages for database 'Target_DB', file 'Sales_Data1' on file 1.
Processed 115024 pages for database 'Target_DB', file 'Sales_Data2' on file 1.
Processed 89720 pages for database 'Target_DB', file 'Sales_Index' on file 1.
Processed 224136 pages for database 'Target_DB', file 'Consultant_Index2' on file 1.
Processed 223480 pages for database 'Target_DB', file 'Consultant_Index' on file 1.
Processed 703640 pages for database 'Target_DB', file 'Operations_Data1' on file 1.
Processed 700712 pages for database 'Target_DB', file 'Operations_Data2' on file 1.
Processed 131824 pages for database 'Target_DB', file 'Operations_Index' on file 1.
Processed 65 pages for database 'Target_DB', file 'DB_Log' on file 1.
RESTORE DATABASE successfully processed 2898033 pages in 2395.222 seconds (9.911 MB/sec).
5/21/2007 3:25:55 AM: Error: Cannot open file "\\Source_Server\sqlbackup\Source_DB\Source_DB_FULL_20070521_0115.sqb". The process cannot access the file because it is being used by another process.
One of the things that SQL Backup performs after a RESTORE is to identify the actual restore type that's performed. E.g. to restore a differential backup, you use the command
EXEC master..sqlbackup '-sql "RESTORE DATABASE ..." '
Looking at the command alone, it could either be a full or differential restore. After the restore has completed, SQL Backup opens the file to read the SQL Backup header, to determine the restore type.
In your case, it appears that the file was unavailable immediately after the restore had completed. Does this happen everytime, or was this a once-off occurrence? Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8