Solved

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

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

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
 

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 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 142

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 142

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now