I'm attempting to restore a SQL Server 2008 R2 backup into a SQL Server 2014 database WITH MOVE but am encountering the following error:

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 'E:\Intuitive8.7Data\iERP87_Data.mdf'.

Following is the code I'm using attempting to accomplish this:

USE Master
GO

ALTER DATABASE iERP87 SET OFFLINE WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [iERP87]
FROM DISK = N'E:\Temp\iERP81_2008.bak'
WITH
MOVE N'Production70_Data' TO N'E:\Intuitive8.7Data\iERP87_Data.mdf',
MOVE N'Production70_Log' TO N'E:\Intuitive8.7Log\iERP87_Log.ldf',
NOUNLOAD, REPLACE, STATS = 10

ALTER DATABASE iERP87 SET ONLINE WITH ROLLBACK IMMEDIATE;

As a result of searching on-line for help I have user "Process Explorer" to look for processes using the mdf file without finding any and have rebooted the server to ensure there are no other open connections.

I've run out of ideas on this one.  Any help would be most appreciated.

So  you already have the database iERP87 on SQL 2014 instance, right?? if not - then why are you  setting the database to offline

Do you already have the database files(mdf,ldf) in that location, right?? are these sql 2008R2 files or 2014 files

and you want to overwrite this database with backup, right??

If so, USE Replace at the end of the script and you probably do not need MOVE command  or else MOVE the database files to different location or change the file name

Again first confirm on these three questions 

RESTORE DATABASE [iERP87] 
FROM DISK = N'E:\Temp\iERP81_2008.bak' With Replace

Rather than setting the database offline (and permit AV and other evil forces to set their teeth into it), set it to SINGLE_USER (and MULTI_USER when you are done).

Hi,

In Addition to Stan's answer,

As the error describes that your database files are in use by an existing database that is online. If you want to overwrite this, the only way is to use WITH  REPLACE option while restoring.

Don't get confused with the existing connections only being the ones from the users or clients. In this case it is SQL server database holding a connection to its respective files(.mdf & .ldf files) as it is online and cant be overwritten unless specified.

Hope this helps

Thanks

• Edited by bhanu_nz 9 hours 12 minutes ago
• Proposed as answer by Olaf HelperMVP, Moderator a few seconds ago

1)verify and see file list 

RESTORE VERIFYONLY
FROM DISK = 'E:\Temp\iERP81_2008.bak';

RESTORE filelistONLY
FROM DISK = 'E:\Temp\iERP81_2008.bak';

2) make database single user mode

3) restore

4) make database multiuser

    no need make database offline.

When DB is already on the instance where we perform restore we cannot use the same file names with MOVE command. Please change the file names / use WITH REPLACE option. If we need same DB name either rename old DB to _OLD or go for replace.

Hi,

In Addition to Stan's answer,

As the error describes that your database files are in use by an existing database that is online. If you want to overwrite this, the only way is to use WITH  REPLACE option while restoring.

Don't get confused with the existing connections only being the ones from the users or clients. In this case it is SQL server database holding a connection to its respective files(.mdf & .ldf files) as it is online and cant be overwritten unless specified.

Hope this helps

Thanks

• Edited by bhanu_nz Thursday, May 28, 2015 9:54 PM
• Proposed as answer by Olaf HelperMVP, Moderator 23 hours 59 minutes ago

Hi,

In Addition to Stan's answer,

As the error describes that your database files are in use by an existing database that is online. If you want to overwrite this, the only way is to use WITH  REPLACE option while restoring.

Don't get confused with the existing connections only being the ones from the users or clients. In this case it is SQL server database holding a connection to its respective files(.mdf & .ldf files) as it is online and cant be overwritten unless specified.

Hope this helps

Thanks

• Edited by bhanu_nz Thursday, May 28, 2015 9:54 PM
• Proposed as answer by Olaf HelperMVP, Moderator Friday, May 29, 2015 7:06 AM

The database iERP87 already exists.

The existing iERP87 database's mdf and ldf files are in the locations specified.

Yes, I am attempting to overwrite iERP87 with a backup from the older iERP81 database which is currently in production on another server. 

The purpose of this exercise is to upgrade to a newer version of the database application on a new server running a newer version of SQL Server.  I have done this many times at different locations, but this issue is a first for me.

Point taken, I made the change, but it still did not resolve my issue.

REPLACE was one of the WITH parameters; it was listed after the MOVE parameters.  I relocated it to the beginning of the WITH string but it did not resolve the issue.

Running the RESTORE VERIFYONLY informs me the physical files don't exist (which I know because they are on a different server) but that the backup file is good.

Running the RESTORE FILELISTONLY confirms that I have the correct logical file names associated with the old database from which I'm restoring to this new one.

Thus no new insight here or progress in resolving my problem.

The old database was named iERP81 and the new one is iERP87, thus they have different database names.  Their physical file names are also different hence my use of the MOVE parameters.

So if you do

sp_helpdb iERP87

do you see the files listed in the script?

I'm starting to suspect that there is a second instance on the server, and the files you are trying to use are attached to that instance.