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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.