Unresolved - Restore Errors spaces in filename

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Unresolved - Restore Errors spaces in filename

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2005-09-01 : 16:43:52

I'm trying to create a standby server. Server 1 has all files locacted in the D drive, Server 2 will have the files located on the C drive. I've done a backup on server1 and moved the x.bak file to server 2. I then run the command

restore filelistonly
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\HS_Webcalendar backup.BAK'

and get the results

HS_Webcalendar_dat D:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.mdf D PRIMARY 2097152 35184372080640
HS_Webcalendar_log D:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.ldf L NULL 2097152 35184372080640

When I run the restore command

restore DATABASE HS_Webcalendar
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\HS_Webcalendar backup.BAK'
with move 'HS_Webcalendar_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.mdf',
move 'HS_Webcalendar_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.ldf',
standby = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_WebcalendarUndo.ldf'

I'm getting the following error

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'HS_Webcalendar_dat' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'HS_Webcalendar_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 17:32:30

Is it because there are spaces in the path? If so maybe surrounding the path with double-quotes would help.

You might want to take the opportunity to move the data files somewhere OTHER than "X:\program files\..."

Its a pet hate of mine, but it seems a daft place that SQL Server chooses by default ...

MOO

Kristen

bogey
Posting Yak Master

166 Posts

Posted - 2005-09-01 : 18:43:15

Moving everything to the c:\ drive worked. The quotes did not. I've got alot of db's to move so this is gonna be a pain

This worked
------------
restore DATABASE HS_Webcalendar
from disk = 'C:\HS_Webcalendar backup.BAK'
with move 'HS_Webcalendar_dat' to 'C:\HS_Webcalendar.mdf',
move 'HS_Webcalendar_log' to 'C:\HS_Webcalendar.ldf',
standby = 'C:\HS_WebcalendarUndo.ldf'

Processed 80 pages for database 'HS_Webcalendar', file 'HS_Webcalendar_dat' on file 1.
Processed 1 pages for database 'HS_Webcalendar', file 'HS_Webcalendar_log' on file 1.
RESTORE DATABASE successfully processed 81 pages in 0.193 seconds (3.406 MB/sec).

This did not
-------------
restore DATABASE HS_Webcalendar
from disk = '"C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\HS_Webcalendar backup.BAK"'
with move 'HS_Webcalendar_dat' to '"C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.mdf"',
move 'HS_Webcalendar_log' to '"C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_Webcalendar.ldf"',
standby = '"C:\Program Files\Microsoft SQL Server\MSSQL\data\HS_WebcalendarUndo.ldf"'

Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\"C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\HS_Webcalendar backup.BAK"'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 02:38:27

I don't have an answer to that - but I'm almost certain I've had a problem with restore paths containing spaces in the past

Dumb question! Does this path exist?

C:\Program Files\Microsoft SQL Server\MSSQL\data
Particularly because the path the backup file is in is slightly different:

C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP
"I've got alot of db's to move so this is gonna be a pain"

It should be possible to create a script for all the RESTOREs - and then you can influence the path they are restored to etc. without too much trouble.

Kristen

bogey
Posting Yak Master

166 Posts

Posted - 2005-09-02 : 08:41:23

Kristen - the path is most certainly there. I guess I'll change the location of the files.

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 09:52:34

"the path is most certainly there"

Yeah, I thought it would be. Always worth asking the obvious question though!

Kristen

bogey
Posting Yak Master

166 Posts

Posted - 2005-09-02 : 13:27:31

Is there then an answer to this? I've looked into the 8.3 format, which is a standard (8 or less characters).

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 13:44:05

Sorry, I've had the problem and not found an answer (we don't use spaces in any paths that hold SQL files, so not much incentive for me!)

You might want to edit the subject of this topic and append "UNRESOLVED" or somesuch so that others stop by this thread

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources