possible bug: sp_attach_single_file_db

The scenario:
The server in question has its Default Log Directory set
to "L:\". I used sp_attach_single_file_db to attach an
mdf file. Here is the command that I used:
EXEC sp_attach_single_file_db @.dbname = 'test2',
@.physname = 'f:\test2_Data.MDF'
I received the following error:
Device activation error. The physical file
name 'L:\test2_Log.LDF' may be incorrect.
New log file 'L:\\test2_log.LDF' was created.
Notice the 2 back-slashes in the path of the log file! I
didn't notice it at first, and eventually attempted to
restore the database from a backup taken from a different
server, using a 'with move'. Here is the command that I
used for that:
RESTORE DATABASE test2
FROM DISK = 'x:\test2.bak'
with MOVE 'test2_Data' TO 'f:\SQL_Data\test2_Data.mdf',
MOVE 'test2_Log' TO 'L:\test2_Log.ldf'
and the response:
Server: Msg 3156, Level 16, State 1, Line 1
File 'test2_Log' cannot be restored to 'L:\test2_Log.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.
When I discovered the source of the problem, I inserted
the second back-slash in the move clause, and the restore
worked.
So, there may actually be 2 issues:
1. The extra back-slash that SQL expects to be in the path.
2. The erroneous error message associated with the restore
statement.
Has anyone else seen this?
Steve Phelps
SQL Server DBA
American Fidelity GroupThe extra backslash is due to a bug in EM in that if you use the browse
button to select the default directories it appends the trailing backslash
to the folder path, SQL actually expects the value to not have a trailing
backslash (easily ammended in EM by removing it although it generally
doesn't cause problems except the filename looking funny. Not come across
the restore issue, I'll try and repro that.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Steve Phelps" <Steve.Phelps@.af-group.com> wrote in message
news:014201c356ce$1fa90c50$a601280a@.phx.gbl...
The scenario:
The server in question has its Default Log Directory set
to "L:\". I used sp_attach_single_file_db to attach an
mdf file. Here is the command that I used:
EXEC sp_attach_single_file_db @.dbname = 'test2',
@.physname = 'f:\test2_Data.MDF'
I received the following error:
Device activation error. The physical file
name 'L:\test2_Log.LDF' may be incorrect.
New log file 'L:\\test2_log.LDF' was created.
Notice the 2 back-slashes in the path of the log file! I
didn't notice it at first, and eventually attempted to
restore the database from a backup taken from a different
server, using a 'with move'. Here is the command that I
used for that:
RESTORE DATABASE test2
FROM DISK = 'x:\test2.bak'
with MOVE 'test2_Data' TO 'f:\SQL_Data\test2_Data.mdf',
MOVE 'test2_Log' TO 'L:\test2_Log.ldf'
and the response:
Server: Msg 3156, Level 16, State 1, Line 1
File 'test2_Log' cannot be restored to 'L:\test2_Log.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.
When I discovered the source of the problem, I inserted
the second back-slash in the move clause, and the restore
worked.
So, there may actually be 2 issues:
1. The extra back-slash that SQL expects to be in the path.
2. The erroneous error message associated with the restore
statement.
Has anyone else seen this?
Steve Phelps
SQL Server DBA
American Fidelity Group|||You are aware you have to restart the SQL Service for changes to the default
directory to take effect ? A little test I did was to create a database when
the default log directory had a trailing backslash and confirm it had a \\
in it's name. Change the default log directory to remove the trailing
backslash and restart the SQL Service. I then detached the database created
earlier, deleted the log file and attached it using
sp_attach_single_file_db. This created the log file with the correct path
(no \\) . The device activation error referred to the path with the \\ in it
because it's reading the original logfile location from the database header
in the mdf file. Hope this makes things a little clearer
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Steve Phelps" <Steve.Phelps@.af-group.com> wrote in message
news:038101c356e2$dc0aaa70$a501280a@.phx.gbl...
Jasper,
Thanks for the quick response. I had considererd that
possibility and tried both with and without the slash when
I set the default directory. In neither case did I use
the browse button for that purpose. In both cases, the
default directory *appeared* to have been set properly.
Only when I used the sp_attach_single_file_db stored
procedure did the problem arise. My original thought was
that it was a bug in the stored proc. I hadn't looked at
the source code because I assumed that it was encrypted.
It isn't. I parsed out the code that builds the "Create
database" statement and executed it, printing the
resulting command:
CREATE DATABASE [test2]
ON (FILENAME ='f:\sql_data\test2_data.mdf' )
FOR ATTACH
then pasted it into Query Analyzer and executed it. Got
the same result. The new log file's path contains 2
slashes.
Steve Phelps
SQL Server DBA
American Fidelity Group
>--Original Message--
>The extra backslash is due to a bug in EM in that if you
use the browse
>button to select the default directories it appends the
trailing backslash
>to the folder path, SQL actually expects the value to not
have a trailing
>backslash (easily ammended in EM by removing it although
it generally
>doesn't cause problems except the filename looking funny.
Not come across
>the restore issue, I'll try and repro that.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Steve Phelps" <Steve.Phelps@.af-group.com> wrote in
message
>news:014201c356ce$1fa90c50$a601280a@.phx.gbl...
>The scenario:
>The server in question has its Default Log Directory set
>to "L:\". I used sp_attach_single_file_db to attach an
>mdf file. Here is the command that I used:
>EXEC sp_attach_single_file_db @.dbname = 'test2',
> @.physname = 'f:\test2_Data.MDF'
>I received the following error:
>Device activation error. The physical file
>name 'L:\test2_Log.LDF' may be incorrect.
>New log file 'L:\\test2_log.LDF' was created.
>Notice the 2 back-slashes in the path of the log file! I
>didn't notice it at first, and eventually attempted to
>restore the database from a backup taken from a different
>server, using a 'with move'. Here is the command that I
>used for that:
>RESTORE DATABASE test2
> FROM DISK = 'x:\test2.bak'
> with MOVE 'test2_Data' TO 'f:\SQL_Data\test2_Data.mdf',
> MOVE 'test2_Log' TO 'L:\test2_Log.ldf'
>and the response:
>Server: Msg 3156, Level 16, State 1, Line 1
>File 'test2_Log' cannot be restored to 'L:\test2_Log.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.
>When I discovered the source of the problem, I inserted
>the second back-slash in the move clause, and the restore
>worked.
>So, there may actually be 2 issues:
>1. The extra back-slash that SQL expects to be in the
path.
>2. The erroneous error message associated with the restore
>statement.
>Has anyone else seen this?
>Steve Phelps
>SQL Server DBA
>American Fidelity Group
>
>.
>