Restoring database - Cannot access file because it is in use by another process

Question

I have a backup of a database from another SQL Server 2005 machine. I'm attempting to restore it to my SQL Server 2008 instance.

I have created a new database for the restore to go in to, but when attempting the restore with the following (generated by ssms):

RESTORE DATABASE [WendyUAT] 
FROM DISK = N'D:\wanda20130503.bak' 
WITH FILE = 1,  
MOVE N'Wendy' TO N'D:\databases\\WendyUAT.mdf',  
MOVE N'Wendy_log' TO N'D:\databases\\WendyUAT.ldf',  
MOVE N'sysft_WendyFti' TO N'D:\databases\\WendyUAT.WendyFti',  
NOUNLOAD,  REPLACE,  STATS = 10

I get the following error:

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 'D:\databases\WendyUAT.mdf'.

As far as I can tell (using Process Explorer etc) nothing else is using the file. I've disabled real-time protection in windows defender. I just cannot understand why SQL Server thinks the file is in use as windows explorer lets me delete it with no problems so Windows doesn't seem to think it's in use.

Any help would be gratefully received.

Answer 1

How are you connecting to your SQL Server in your application before running this backup? What database are you connecting to?

You cannot connect to the WendyUAT database and use it when you want to restore it - you'll have to explicitly use some other database, e.g. master, before running your SMO restore code

All my T-SQL restore scripts will always include:

USE [master]
GO 

RESTORE DATABASE [WendyUAT] ......

So if you run this backup from your app, make sure you explicitly connect to e.g. master before running this T-SQL restore script

Update:

Is this something you did on purpose, or might it just be a typo??

MOVE N'Wendy' TO N'D:\databases\\WendyUAT.mdf',  
                     *         **
                     *         *
                     *         *  two backslashes here  -why??? 
                     * only one backslash here...

Does it work if you use single backslashes only??

RESTORE DATABASE [WendyUAT] 
FROM DISK = N'D:\wanda20130503.bak' 
WITH FILE = 1,  
MOVE N'Wendy' TO N'D:\databases\WendyUAT.mdf',  
MOVE N'Wendy_log' TO N'D:\databases\WendyUAT.ldf',  
MOVE N'sysft_WendyFti' TO N'D:\databases\WendyUAT.WendyFti',  
NOUNLOAD,  REPLACE,  STATS = 10