November 27, 2015 at 7:34 am
Hi,
I am having trouble scripting a restore.
I keep geeting ".mdf' cannot be overwritten. It is being used by database"
ALTER DATABASE MYDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC('
RESTORE DATABASE [MYDB]
FROM DISK = ''' + @filename + '''
WITH
MOVE N''' + @DB_MDF_LogicalName + ''' TO N''' + @DB_MDF_PhysicalName + ''',
REPLACE, RECOVERY, STATS = 10')
November 27, 2015 at 7:47 am
November 27, 2015 at 7:52 am
No not really. Is there any issue with doing this?
Do I need to do this: ALTER DATABASE [YOUR_DATABASE] SET OFFLINE WITH ROLLBACK IMMEDIATE
November 27, 2015 at 7:59 am
There are much easier ways to do a restore! I can't tell from your script, but are you trying to move the file onto itself? (The reason I ask is that you're using logical name and physical name in the script.)
November 27, 2015 at 8:03 am
run sp_who or query sys.processes to see if anything is using the database.
check the code below and make sure that the file you are trying to overwrite belongs to the database you are trying to replace.
select MF.name as LogicalName,MF.physical_name from sys.master_files MF
inner join sys.databases D on d.database_id = mf.database_id
WHERE D.name = 'MyDB'
November 27, 2015 at 8:12 am
Nothing is using the database - I just ran SP_WHO
Yes the files are right. Do I need to use this: ALTER DATABASE [YOUR_DATABASE] SET OFFLINE WITH ROLLBACK IMMEDIATE
November 27, 2015 at 8:18 am
Beatrix Kiddo (11/27/2015)
There are much easier ways to do a restore! I can't tell from your script, but are you trying to move the file onto itself? (The reason I ask is that you're using logical name and physical name in the script.)
I think you nailed it.
RESTORE command is attempting to overwrite files already in use by the source database.
Move location has to be valid path but file must not exist seems like.
So if replacing and having the files stay where they are, you do not use the keyword move if you want the final location to be the existing DB file location, only if you want the file to be in a different place.
What are the MessageID's for the errors?
3156 = File '%ls' cannot be restored to '%ls'. Use WITH MOVE to identify a valid location for the file.
1834=The file '%ls' cannot be overwritten. It is being used by database '%.*ls'.
Which one are you suffering from?
November 27, 2015 at 8:38 am
OK SOLVED
I removed the "WITH MOVE"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply