We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

RESTORE DATABASE ... WITH MOVE doesn't accept spaces in path

Medium Priority
250 Views
Last Modified: 2012-06-21
Doing a SQL RESTORE DATABASE WITH MOVE.
When moving to a path with spaces, an error is returned 'cannot be restored to'.

Anyone?
-- THIS WORKS FINE
RESTORE DATABASE myDB FROM DISK = 'C:\temp\someDB.bak'
WITH REPLACE
, MOVE 'File_dat' to 'c:\TEMP\File_dat.mdf'
 
-- THIS DOESN"T LIKE THE SPACES IN THE PATH 
RESTORE DATABASE myDB FROM DISK = 'C:\temp\someDB.bak'
WITH REPLACE
, MOVE 'File_dat' to 'c:\TEMP TEMP\File_dat.mdf'   -- Notice the space in the path '\Temp Temp'

Open in new window

Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
did you try the equivalent dos name  temp~1

Author

Commented:
You would expect that to work, but it actually doesn't either.

And that aside, there are plenty of example codes out here showing a MOVE TO 'C:\Program Files\Microsoft SQL Server\MSSQL.q\MSSQL' (plenty of 'spaces') .
But....not for me :(.

Why?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what os version?
what file system?
what exact version/edition of sql server?
what is the full error you get?
what tool do you use to run the code?

Author

Commented:
what os version? - XP Pro SP 3

what file system? - NTFS

what exact version/edition of sql server? - SQLEXPRESS

what is the full error you get?
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\TEMP TEMP\WorkPlace_DEV3.mdf'.
Msg 3156, Level 16, State 5, Line 1
File 'WEmpty_dat' cannot be restored to 'c:\TEMP TEMP\WorkPlace_DEV3.mdf'. 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.

what tool do you use to run the code? - SQL Server Management Studio Express


Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
does the folder C:\temp temp\  actually exist? if must exist before the restore!
if it does, what about the permissions?

Author

Commented:
It exists and the permissions are fine.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>WorkPlace_DEV3.mdf
is that file eventually locked/used by another database already?

Author

Commented:
No, it is not. And if I try to restore it to C:\Temp\WorkPlace_DEV3.mdf' it works fine, It is only if there is a space in the path that the error occurs.  
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Hi again.
Your script failed.
I had another look att the NTFS permissions. Checked what account SQLEXPRESS uses on the particular system, which was different from what I had assumed.
Made the appropriate changes.
Ran your script -> fine :) !
Ran my script...... -> also fine.....

It's amazing how we sometimes overlook teh obvious, even if someone reinds us to have a closer look at them......
I have upped the point and will accept your solution next.
Thanks !

 

Author

Commented:
Thanks for persuiing with this question, inspite of my un-thoughtfulness.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
glad I could help.
you might have accepted http://#a24830207  then instead, requested to double-check the permissions :)

CHeers
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.