Share via

Hello, I have been trying to restore adventure works bakup to a named instance and i keep getting the error message below. I have attached full error message below.

Katung M Aduwak 21 Reputation points
2022-10-02T19:46:33.363+00:00

TITLE: Microsoft SQL Server Management Studio

------------------------------

Restore of database 'AdventureWorks2019' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: 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 'A:\MSSQL\AdventureWorks2019.mdf'. (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    2022-10-02T20:01:01.207+00:00

    You have an A: drive? I assume it is not a floppy, but it is a little, hm, brave. (I have occasionally had a B: drive in my computer. It worked most of the time, but some programs got a little confused.)

    So I would try a different drive than A:. I would also check what your anti-virus software is up to. Are you using any instant-backup software?

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. wondwossen woldetensay 1 Reputation point
    2022-10-02T20:41:30.133+00:00

    A common cause of this error is an on-access scanner running as part of an anti-virus system. An on-access scanner checks a file for viruses when the file is first written or opened. This prevents SQL Backup Pro from writing the backup file. To avoid this problem, exclude the backup location from the on-access scan.

    https://documentation.red-gate.com/display/SBU8/System+error+32+-+The+process+cannot+access+the+file+because+it+is+being+used+by+another+process#:~:text=A%20common%20cause%20of%20this,from%20the%20on%2Daccess%20scan.

    0 comments
  2. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2022-10-03T02:14:55.637+00:00

    Hi @Katung M Aduwak ,

    This error may arise if another application is accessing the backup file and preventing SQL restore Pro access it. We can use Process Explorer to identify the process that is accessing the backup file. Please follow below steps.

    1.Download Process Explorer from the Microsoft website and run it on the computer on which you tried to restore the backup.
    2.On the Find menu, select Find Handle or DLL.
    3.In the Handle or DLL substring box, type the full restore path and file name, then click Search.
    4.Process Explorer displays the applications that are currently accessing the file. Stop the application from accessing the file, or run the restore at a time when the application is not accessing the file.

    In addition, did you restore the backup up file to default instance firstly, then restore this backup to named instance? Suggest you adding move parameter in restore command as below. Below is a test in my environment. The MOVE statement causes the data and log file to be restored to the specified locations. Name instance and default instance has different location for database files as Erland mentioned.

    RESTORE DATABASE Cathy FROM DISK = 'C:\Cathytest\Cathy.bak'   
    WITH   
     MOVE 'Cathy' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\Cathy.mdf',  
     MOVE 'Cathy_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\Cathy_log.ldf',  
     REPLACE   
    ;

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments