Solved

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

Posted on 2009-07-10
12
235 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 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