Restore log shows file error after restore

eilanddeilandd Posts: 44
edited May 30, 2007 6:36AM in SQL Backup Previous Versions
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?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    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.
  • Thanks for the response.

    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.
  • peteypetey Posts: 2,358 New member
    I think Brian was referring to the SQL Backup MOVE option, not the SQL Server MOVETO option.

    Could you pls post the full contents of the log? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter;

    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.
  • peteypetey Posts: 2,358 New member
    Thanks for posting the log contents.

    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    If that's the case, Peter, I think it may be caused by some anti-virus software. This has happened in the past. Disabling any auto-protect features may stop this from happening.
  • This happens occasionally; not every time but enough to be worrysome. We'll check the AV and we're also looking at the tape backup process.
Sign In or Register to comment.