Solved

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

Posted on 2009-07-10
12
233 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

830 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