Solved

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

Posted on 2009-07-10
12
236 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

0
Comment
Question by:robleenheer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24825062
did you try the equivalent dos name  temp~1
0
 

Author Comment

by:robleenheer
ID: 24825115
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24828064
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:robleenheer
ID: 24830183
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24830207
does the folder C:\temp temp\  actually exist? if must exist before the restore!
if it does, what about the permissions?
0
 

Author Comment

by:robleenheer
ID: 24830736
It exists and the permissions are fine.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24830754
>WorkPlace_DEV3.mdf
is that file eventually locked/used by another database already?
0
 

Author Comment

by:robleenheer
ID: 24830901
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24834014
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
 

Author Comment

by:robleenheer
ID: 24836003
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
 

Author Closing Comment

by:robleenheer
ID: 31602150
Thanks for persuiing with this question, inspite of my un-thoughtfulness.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24837408
glad I could help.
you might have accepted http://#a24830207  then instead, requested to double-check the permissions :)

CHeers
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Viewers will learn how the fundamental information of how to create a table.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question