4

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:

  1. This only happens on some of the receiving SQL Instances most of them are executing very similar commands with no problem.

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

  3. Other DBs in this same instance can execute their corresponding RESTORE command without any problems.

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

  5. 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 sp_lock
  • 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
  • How are you running or executing these procedures, just SSMS query window? – Shawn Melton Sep 16 '14 at 16:47
  • @ShawnMelton Yes, from a process local to the SQL Server instance. My account has local and domain admins. – RBarryYoung Sep 16 '14 at 16:50
  • Could it be connections are getting mixed up for this particular process? Might be why the initial OS error is occurring. – Shawn Melton Sep 16 '14 at 16:59
  • @ShawnMelton No, I have checked for this. – RBarryYoung Sep 16 '14 at 17:01
3

When using the backup files please make sure to use the logical filename. When using multiple backupsets within the same backup files, please make sure to check that the file being used is correct.

In this instance:

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.

It looks as though you have the wrong logical file name or the wrong backup inside a backupset. I know you pointed this out, but it would make sense to interrogate the information in the backupset and double check.

  • can you please explain more about logical name?? – El.Hum Nov 10 '18 at 9:54
  • @El.Ham The physical filename is the path and filename of the actual file on the filesystem, the logical filename is the name given to the file inside of SQL Server and used when identifying individual files. – Sean Gallardy Nov 10 '18 at 15:17
0

Look for 'typos' or syntax errors in your file names such as '\\' in the path. Resolve those and your statement will work.

(Of course a typo in your path is a version of the answer given above regarding an incorrect file name, but just a particular case of that issue!)

  • 1
    The error mentioned: The process cannot access the file because it is being used by another process would suggest that it's not a typo, something has locked the file. – Greg Aug 30 '18 at 1:56
  • If you've been in IT for a while, you know that error messages are not always accurate. I had '\\' in my path which resulted in the error. Removed the extra '\' and the restore worked as expected. – Mark E Aug 30 '18 at 13:27
-2

This worked for me for the 32 error .. another process is using it.

https://support.microsoft.com/en-in/help/3153836/operating-system-error-32-when-you-restore-a-database-in-sql-server-20

If you're restoring from a striped backup set that contains FILESTREAM data in SQL Server 2012 SP3, 2014, or 2016, you can get this error. The fix is to install the Cumulative Update listed for your version in the above link (or a later SP or CU).

  • 2
    Please don't add link only answers. Include the relevant parts in your post in case the link rots away. – Tom V Apr 10 '18 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.