I am writing a set of procedures for (partially) automating DB deployment for a customer, which mostly works fine, but sometimes a RESTORE command is failing with the OS error 32, about a file being in use by another process (details below).
I have googled this extensively but have found little that applies to my specific case. I suspect that there's something that I am overlooking, but I just can't seem to find it.
Here's the command:
RESTORE DATABASE [NBBC_Logistics] FROM DISK = '\\wpdboardq01\Shares\DbCopy\DevBackups\NBBC_Logistics_140916112310.bak' WITH FILE=1, NOUNLOAD, STATS=10, MOVE 'NBBC_Logistics' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics.mdf', MOVE 'NBBC_Logistics_log' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics_log.ldf', REPLACE ;
This results in the following error message:
Msg 3634, Level 16, State 1, Line 11 The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Msg 3156, Level 16, State 8, Line 11 File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 11 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 11 RESTORE DATABASE is terminating abnormally.
Some things to note:
This only happens on some of the receiving SQL Instances most of them are executing very similar commands with no problem.
The instance that is failing is one where there are multiple SQL instances on the same box (DEV and QA) and it is trying to restore a DB backup from DEV into the QA version of the same database.
Other DBs in this same instance can execute their corresponding RESTORE command without any problems.
It may be relevant that the source logical file names (shown in the error) are different from the DBs existing ones (named in the command), however, I beleive that I have cases where this does work.
Also, note carefully, that the file path reported by the error is NOT what I am specifying in the MOVE, but rather the original file path (which does still have the original DB files in use by the DEV instance).
So it appears that it is trying to first RESTORE the DB files to their original path locations, and only then MOVE them to the path I tell it to. That is in contrast to what the doc says it does, and obviously would be compeltely unworkable in general, as someone RESTOREing a DB copy has no control over where the original files were and could not gaurantee that such paths existed and were not already in use.
Any help would be greatly appreciated.
Just to head off some of the automatic responses that don't apply:
- The target database is not currently in use by me or anyone else.
- Nor does it show up in
- The command specifies
REPLACE, it is supposed to overwrite the existing database
- The files specified are not open by any other windows processes
- however, the file mentioned in the error are open by the other Sql Instance on the box
- the BAK file location on a share has nothing to do with it, copying it locally changes nothing, nor does renaming it.
- the db files and paths specified are the correct ones for the target database