Error on Restore

Question

Error on Restore

ramanathan.raman88

Old Hand

Points: 366
More actions
July 15, 2013 at 1:24 am

#279908

Hi,
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf' cannot be overwritten. It is being used by database 'sample'.
File 'sample' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.
Plz.... Help..?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ramanathan.raman88 (7/15/2013)
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf' cannot be overwritten. It is being used by database 'sample'.
File 'sample' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

The error is given because the specified file allready exists and the file is currently in use. Probably a database with the same name does exist on the SQL instance. Use REPLACE in your restore statement (when you want to replace an existing database) or specify different filenames or file location with the WITH MOVE option.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

ramanathan.raman88 (7/15/2013)
Hi,
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf' cannot be overwritten. It is being used by database 'sample'.
File 'sample' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.
Plz.... Help..?

--to confirm location and contents of the backup file

RESTORE FILELISTONLY FROM DISK = 'C:\temp\YourDataBase.bak'

--these commands will close all transactions and kill all user connections

ALTER DATABASE [YOUR_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [YOUR_DATABASE] SET OFFLINE WITH ROLLBACK IMMEDIATE

--this will do the restore

RESTORE DATABASE [YOUR_DATABASE]

FROM DISK = 'C:\temp\YourDataBase.bak'

WITH

REPLACE-- Overwrite DB - if one exists

,NORECOVERY-- Used so we can recover the tx log

,STATS = 10-- Show progress (every 10%)

,MOVE 'YourDataBase_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MC_SQL2008R2\MSSQL\Backup\YourDataBase.MDF'

,MOVE 'YourDataBase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MC_SQL2008R2\MSSQL\Backup\YourDataBase.LDF'

--this turns everything back on so the recovered db can be used

RESTORE DATABASE [YOUR_DATABASE] WITH RECOVERY