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

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

robleenheerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
did you try the equivalent dos name  temp~1
0
robleenheerAuthor 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?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

robleenheerAuthor 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


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does the folder C:\temp temp\  actually exist? if must exist before the restore!
if it does, what about the permissions?
0
robleenheerAuthor Commented:
It exists and the permissions are fine.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>WorkPlace_DEV3.mdf
is that file eventually locked/used by another database already?
0
robleenheerAuthor 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.  
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here is my test script that works just fine with spaced in folders and files
use master
go
create database ee_restore;
go
 
backup database ee_restore to disk = 'c:\ee_restore.bak' with init
go
 
restore database ee_restore 
from disk = 'c:\ee_restore.bak'
with move 'ee_restore' to 'c:\test test\ee_restore.mdf'
   , move 'ee_restore_log' to 'c:\test test\ee_restore.ldf'
   , replace
go
 
drop database ee_restore
 
 
 
 
output:
 
Processed 152 pages for database 'ee_restore', file 'ee_restore' on file 1.
Processed 2 pages for database 'ee_restore', file 'ee_restore_log' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.595 seconds (2.108 MB/sec).
 
(2 row(s) affected)
Processed 152 pages for database 'ee_restore', file 'ee_restore' on file 1.
Processed 2 pages for database 'ee_restore', file 'ee_restore_log' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.513 seconds (2.445 MB/sec).

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robleenheerAuthor 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 !

 
0
robleenheerAuthor Commented:
Thanks for persuiing with this question, inspite of my un-thoughtfulness.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help.
you might have accepted http://#a24830207  then instead, requested to double-check the permissions :)

CHeers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.