Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

RESTORE DATABASE -- same name, same files physical and logical -- the files cannot be overwritten

I have been doing SQL Server RESTORES for a very, very long time -- but I'm having a problem today that I've never seen before.

I am simply identifying the most recent bak file from ServerA, copying it to ServerB, and performing the RESTORE.
Both database have the same name, same physical and logical filenames --- it's all the same.  I've even double checked with RESTORE FILELISTONLY -- the files have all the same names, and are in the same directory structure.  

But, my attempts at using REPLACE are all failing with the errors below.  I am simply restoring a backup from one server to another, where all names are the same -- database, files, directory structure.  I am using REPLACE because I don't care that the files already exist.  I just want to overwrite / replace any existing objects with those from the bak file.

Source server is SQL Server 2012, destination server is SQL Server 2014.  This is my code:

		ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
			DECLARE @bkupFile1 VARCHAR(355) = (SELECT bakfilename FROM @db1)
			RESTORE DATABASE dbname
			FROM DISK = @bkupFile1
			WITH REPLACE;

Open in new window


--These are my errors:
Msg 1834, Level 16, State 1, Line 25
The file 'E:\MSSQL\Data\dbname.mdf' cannot be overwritten.  It is being used by database 'dbname'.
Msg 3156, Level 16, State 4, Line 25
File 'dbname' cannot be restored to 'E:\MSSQL\Data\dbname.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 25
The file 'L:\MSSQL\Log\dbname_log.ldf' cannot be overwritten.  It is being used by database 'dbname'.
Msg 3156, Level 16, State 4, Line 25
File 'dbname_log' cannot be restored to 'L:\MSSQL\Log\dbname_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 25
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.
Failed to restore the bak file for dbname. Please review.


I have written procedures to do this very thing dozens of times, and I've never had problems before.  The only thing I can think is that most of the time, I am actually using WITH MOVE, sending the files to a different physical location.  That is not the case here.  ALL names are the same -- the database, the physical and logical filenames.  

Does any Expert see the flaw in my above statement?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,
Its looks like your DB is still in use. You are not changing the file locations - correct?

Try appending below your command.-

ALTER DATABASE yourDB
SET SINGLE_USER

-------Your Command------

ALTER DATABASE yourDB SET MULTI_USER
GO

Hope it helps!
Avatar of dbaSQL

ASKER

Thank you, Pawan, but did you not see the first line of my code?

      ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Ohh yes missed.

You are not changing the file locations - correct?
Avatar of dbaSQL

ASKER

>>You are not changing the file locations - correct?
Correct.  I am not changing anything;   All names are exactly the same on the destination server as they are on the source.
File 'dbname' cannot be restored to 'E:\MSSQL\Data\dbname.mdf'. Use WITH MOVE to identify a valid location for the file.

Ok, I think if you are not using WITH Move then the files will go to the default location. I think you have installed SQL in C:\ , That seems to be the problem.

Please check. try creating a new DB (for time being) and see where the default files are going.

Hope it helps!
Avatar of dbaSQL

ASKER

I have used WITH MOVE numerous times, though unneccessarily, as I am not trying to move the files.  It fails with the very same error.

			DECLARE @bkupFile1 VARCHAR(355) = (SELECT bakfilename FROM @db1)
			RESTORE DATABASE dbname
			FROM DISK = @bkupFile1
			WITH RECOVERY, REPLACE, STATS = 10,
			  MOVE 'dbname' TO 'E:\MSSQL\Data\dbname.mdf',
			  MOVE 'dbname_log' TO 'L:\MSSQL\Log\dbname_log.ldf';

Open in new window



-- error
Msg 1834, Level 16, State 1, Line 25
The file 'E:\MSSQL\Data\dbname.mdf' cannot be overwritten.  It is being used by database 'dbname'.
Msg 3156, Level 16, State 4, Line 25
File 'dbname' cannot be restored to 'E:\MSSQL\Data\dbname.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 25
The file 'L:\MSSQL\Log\dbname_log.ldf' cannot be overwritten.  It is being used by database 'dbname'.
Msg 3156, Level 16, State 4, Line 25
File 'dbname_log' cannot be restored to 'L:\MSSQL\Log\dbname_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 25
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.
Failed to restore the bak file for dbname. Please review.



The SQL binaries are on C, but the databases are all on different drives.  I cannot imagine why this would have anything at all to do with the locaiton of the SQL Server binaries.  Or where SQL is installed, as you've said.  Please clarify.
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

Hi Eugene.  Thanks for the input!  I just performed the test you suggested, and it worked successfully.  I've pasted my logic here, and you can see the 'dbname' is no longer generic.  I just wanted you to see exactly the code that I am running.  The only change is that I've added a '2' to the end of the physical filename for both the data and log file, and have used WITH MOVE:

                  RESTORE DATABASE InsiteFO
                  FROM DISK = @bkupFile1
                  WITH RECOVERY, REPLACE, STATS = 10,
                    MOVE 'InsiteFO' TO 'E:\MSSQL\Data\InsiteFO2.mdf',
                    MOVE 'InsiteFO_log' TO 'L:\MSSQL\Log\InsiteFO2_log.ldf';

But -- this suggests that I have to use different filenames WITH MOVE to get it done.  Surely that is not the case.
Avatar of dbaSQL

ASKER

While I am still hoping to understand why the REPLACE did not work, I don't want to keep this thing open forever.  I did change the physical file names as you suggested, Eugene, and tested it multiple times afterward.  This will work.  Thanks for the assist!