Error when restoring database (Windows 7 test environment)

Question

I have a windows 7 operating system as a test environment. I have SQL Server EE installed with two instances, named as test and production. I took a full backup from AdventureWorks database from test instance and I tried to restore it into the production instance:

RESTORE DATABASE [testikanta] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TESTI\MSSQL\Backup\AdventureWorks.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

I got an error saying:

Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '32(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TESTI\MSSQL\DATA\AdventureWorks_Data.mdf'.
Msg 3156, Level 16, State 8, Line 1
File 'AdventureWorks_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TESTI\MSSQL\DATA\AdventureWorks_Data.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '32(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TESTI\MSSQL\DATA\AdventureWorks_Log.ldf'.
Msg 3156, Level 16, State 8, Line 1
File 'AdventureWorks_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TESTI\MSSQL\DATA\AdventureWorks_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Where's the problem? I'm running these instances as on local machine adminstrator (SQL Server services are running with the same account).

Answer 1

'32(failed to retrieve text for this error. Reason: 15105)'

Based on a little searching this seems to be SQL Server's way of not correctly reporting an OS (Win32 API) error.

Error 32 is ERROR_SHARING_VIOLATION "The process cannot access the file because it is being used by another process."

Something else has the file open, this could be some other connection, or even the one being used to do the restore, has AdventureWorks open.

Answer 2

In my case was a very silly thing. After checking many times I didn't realize that the begining date from the SAS was incorrect "in the future". In México we use dd/mm/yyyy and the problem was that the captured date was "mm/dd/yyyy" and that date was in the future. After updating that date, the restore worked.

Answer 3

I had exactly the same issue. Changeing teh xxx.mdf to xxx2.mdf (and log) - that is - just change name solved it. The restore automatically deleted the xxx.mdf file.dont really know what kept it in process... If you really want it to be xxx.mdf you can do an additional restore when the xxx2.mdf is done... strange but it worked without errors. so complete --> with original names xxx.mdf and xxx.ldf

restore database ThedatebaseName
from disk = 'D:\SQLbackup\mybackup.bak'
WITH MOVE 'PhysicalName' TO 'D:\SQL\xxx2.mdf',
MOVE 'PhysicalName_log' TO 'D:\SQL\xxx2.ldf',
REPLACE,STATS
GO
The physical name is the one you see when you restore manually or use:
restore filelistonly
from disk = 'D:\SQLbackup\mybackup.bak'
and it will show the names. Good luck.

Answer 4

Go to C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TESTI\MSSQL\DATA\ Give your SQL Server 'write' permission of this Folder. (eg. grand write permission to NETWORK SERVICES)

Answer 5

In my situation both the .mdf and the .ldb were located in another directory than the directory to restore to. So also check the directory.