Detaching MDF/LDF files to SQL Server 2005 running on MSCS

I am planning to do the following when migrating to SQL Server 2005 running
on MSCS. Are there any issues or gotchas I should know regarding these steps:
1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone server
using SAN as the storage for DB files )
2) Run DB backup ( SQL 2005 ) on stand alone server
3) Run migration scripts ( the build scripts changes 80% of the db schema )
4) Run DB backup ( SQL 2005 )
5) Detach the DB
6) Attach the DB on SQL Server 2005 running on MS Cluster environment ( same
DB files on SAN storage)
The problem is that we have limited servers and the database servers
currently used as production will become unavailable when we move these
machines to become nodes within the cluster environment.
--
Lito DIf you use SQL Server Logins (SQL Server authentication) you might want to
make sure that you create the account on the new server with the same SID as
on the current server.
--
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>I am planning to do the following when migrating to SQL Server 2005 running
> on MSCS. Are there any issues or gotchas I should know regarding these
> steps:
> 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
> server
> using SAN as the storage for DB files )
> 2) Run DB backup ( SQL 2005 ) on stand alone server
> 3) Run migration scripts ( the build scripts changes 80% of the db
> schema )
> 4) Run DB backup ( SQL 2005 )
> 5) Detach the DB
> 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
> same
> DB files on SAN storage)
> The problem is that we have limited servers and the database servers
> currently used as production will become unavailable when we move these
> machines to become nodes within the cluster environment.
> --
> Lito D|||Thank you, Keith. Question, do you mean create the account on SQL Server
2005 then run sp_change_users_login? Can you give me specifics on how to do
this?
Thanks again.
--
Lito D
"Keith Kratochvil" wrote:
> If you use SQL Server Logins (SQL Server authentication) you might want to
> make sure that you create the account on the new server with the same SID as
> on the current server.
> --
> Keith Kratochvil
>
> "LITO" <anynomous@.msn.com> wrote in message
> news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
> >I am planning to do the following when migrating to SQL Server 2005 running
> > on MSCS. Are there any issues or gotchas I should know regarding these
> > steps:
> >
> > 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
> > server
> > using SAN as the storage for DB files )
> > 2) Run DB backup ( SQL 2005 ) on stand alone server
> > 3) Run migration scripts ( the build scripts changes 80% of the db
> > schema )
> > 4) Run DB backup ( SQL 2005 )
> > 5) Detach the DB
> > 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
> > same
> > DB files on SAN storage)
> >
> > The problem is that we have limited servers and the database servers
> > currently used as production will become unavailable when we move these
> > machines to become nodes within the cluster environment.
> > --
> > Lito D
>
>|||You only need to run sp_change_users_login if the SID for the login on the
server is different than the user's SID within the database.
To prevent this problem from happening on your new server you can specify
the SID during the CREATE LOGIN process on the SQL 2005 box (SID = is one of
the params that you can use during the CREATE LOGIN process)
This query should give you a list of sql logins and their SID on the SQL
2000 box:
select name, sid
from master..syslogins
where isntgroup = 0
--
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:DDC1D8DD-9400-4E6F-AB3F-936111654344@.microsoft.com...
> Thank you, Keith. Question, do you mean create the account on SQL Server
> 2005 then run sp_change_users_login? Can you give me specifics on how to
> do
> this?
> Thanks again.
> --
> Lito D
>
> "Keith Kratochvil" wrote:
>> If you use SQL Server Logins (SQL Server authentication) you might want
>> to
>> make sure that you create the account on the new server with the same SID
>> as
>> on the current server.
>> --
>> Keith Kratochvil
>>
>> "LITO" <anynomous@.msn.com> wrote in message
>> news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>> >I am planning to do the following when migrating to SQL Server 2005
>> >running
>> > on MSCS. Are there any issues or gotchas I should know regarding these
>> > steps:
>> >
>> > 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
>> > server
>> > using SAN as the storage for DB files )
>> > 2) Run DB backup ( SQL 2005 ) on stand alone server
>> > 3) Run migration scripts ( the build scripts changes 80% of the db
>> > schema )
>> > 4) Run DB backup ( SQL 2005 )
>> > 5) Detach the DB
>> > 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
>> > same
>> > DB files on SAN storage)
>> >
>> > The problem is that we have limited servers and the database servers
>> > currently used as production will become unavailable when we move these
>> > machines to become nodes within the cluster environment.
>> > --
>> > Lito D
>>

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

I am planning to do the following when migrating to SQL Server 2005 running
on MSCS. Are there any issues or gotchas I should know regarding these steps:
1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone server
using SAN as the storage for DB files )
2) Run DB backup ( SQL 2005 ) on stand alone server
3) Run migration scripts ( the build scripts changes 80% of the db schema )
4) Run DB backup ( SQL 2005 )
5) Detach the DB
6) Attach the DB on SQL Server 2005 running on MS Cluster environment ( same
DB files on SAN storage)
The problem is that we have limited servers and the database servers
currently used as production will become unavailable when we move these
machines to become nodes within the cluster environment.
Lito D
If you use SQL Server Logins (SQL Server authentication) you might want to
make sure that you create the account on the new server with the same SID as
on the current server.
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>I am planning to do the following when migrating to SQL Server 2005 running
> on MSCS. Are there any issues or gotchas I should know regarding these
> steps:
> 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
> server
> using SAN as the storage for DB files )
> 2) Run DB backup ( SQL 2005 ) on stand alone server
> 3) Run migration scripts ( the build scripts changes 80% of the db
> schema )
> 4) Run DB backup ( SQL 2005 )
> 5) Detach the DB
> 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
> same
> DB files on SAN storage)
> The problem is that we have limited servers and the database servers
> currently used as production will become unavailable when we move these
> machines to become nodes within the cluster environment.
> --
> Lito D
|||Thank you, Keith. Question, do you mean create the account on SQL Server
2005 then run sp_change_users_login? Can you give me specifics on how to do
this?
Thanks again.
Lito D
"Keith Kratochvil" wrote:

> If you use SQL Server Logins (SQL Server authentication) you might want to
> make sure that you create the account on the new server with the same SID as
> on the current server.
> --
> Keith Kratochvil
>
> "LITO" <anynomous@.msn.com> wrote in message
> news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>
>
|||You only need to run sp_change_users_login if the SID for the login on the
server is different than the user's SID within the database.
To prevent this problem from happening on your new server you can specify
the SID during the CREATE LOGIN process on the SQL 2005 box (SID = is one of
the params that you can use during the CREATE LOGIN process)
This query should give you a list of sql logins and their SID on the SQL
2000 box:
select name, sid
from master..syslogins
where isntgroup = 0
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:DDC1D8DD-9400-4E6F-AB3F-936111654344@.microsoft.com...[vbcol=seagreen]
> Thank you, Keith. Question, do you mean create the account on SQL Server
> 2005 then run sp_change_users_login? Can you give me specifics on how to
> do
> this?
> Thanks again.
> --
> Lito D
>
> "Keith Kratochvil" wrote:

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

I am planning to do the following when migrating to SQL Server 2005 running
on MSCS. Are there any issues or gotchas I should know regarding these step
s:
1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone server
using SAN as the storage for DB files )
2) Run DB backup ( SQL 2005 ) on stand alone server
3) Run migration scripts ( the build scripts changes 80% of the db schema )
4) Run DB backup ( SQL 2005 )
5) Detach the DB
6) Attach the DB on SQL Server 2005 running on MS Cluster environment ( same
DB files on SAN storage)
The problem is that we have limited servers and the database servers
currently used as production will become unavailable when we move these
machines to become nodes within the cluster environment.
--
Lito DIf you use SQL Server Logins (SQL Server authentication) you might want to
make sure that you create the account on the new server with the same SID as
on the current server.
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>I am planning to do the following when migrating to SQL Server 2005 running
> on MSCS. Are there any issues or gotchas I should know regarding these
> steps:
> 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
> server
> using SAN as the storage for DB files )
> 2) Run DB backup ( SQL 2005 ) on stand alone server
> 3) Run migration scripts ( the build scripts changes 80% of the db
> schema )
> 4) Run DB backup ( SQL 2005 )
> 5) Detach the DB
> 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
> same
> DB files on SAN storage)
> The problem is that we have limited servers and the database servers
> currently used as production will become unavailable when we move these
> machines to become nodes within the cluster environment.
> --
> Lito D|||Thank you, Keith. Question, do you mean create the account on SQL Server
2005 then run sp_change_users_login? Can you give me specifics on how to do
this?
Thanks again.
--
Lito D
"Keith Kratochvil" wrote:

> If you use SQL Server Logins (SQL Server authentication) you might want to
> make sure that you create the account on the new server with the same SID
as
> on the current server.
> --
> Keith Kratochvil
>
> "LITO" <anynomous@.msn.com> wrote in message
> news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>
>|||You only need to run sp_change_users_login if the SID for the login on the
server is different than the user's SID within the database.
To prevent this problem from happening on your new server you can specify
the SID during the CREATE LOGIN process on the SQL 2005 box (SID = is one of
the params that you can use during the CREATE LOGIN process)
This query should give you a list of sql logins and their SID on the SQL
2000 box:
select name, sid
from master..syslogins
where isntgroup = 0
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:DDC1D8DD-9400-4E6F-AB3F-936111654344@.microsoft.com...[vbcol=seagreen]
> Thank you, Keith. Question, do you mean create the account on SQL Server
> 2005 then run sp_change_users_login? Can you give me specifics on how to
> do
> this?
> Thanks again.
> --
> Lito D
>
> "Keith Kratochvil" wrote:
>

Detach/Attach database

I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
performed the following:
1> detach database
2> moved the .ldf file to the 'l' drive(deleted from the w drive)
3> attched the database
4> received a RED 'x'on the ldf file >> retyped the new location of the file
and hit OK . The attach failed with the following error.
error 5105:devide activation error: The physical file name '....' may be
incorrect
cannot create '....' because it already exists.
Hi
Rather use T-SQL. Open up query analyser and use the following code. Change
the Db names, files and paths as appropriate:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
"Joe" wrote:

> I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
> performed the following:
> 1> detach database
> 2> moved the .ldf file to the 'l' drive(deleted from the w drive)
> 3> attched the database
> 4> received a RED 'x'on the ldf file >> retyped the new location of the file
> and hit OK . The attach failed with the following error.
> error 5105:devide activation error: The physical file name '....' may be
> incorrect
> cannot create '....' because it already exists.
>
>
>
>
|||I got the same results.--see below:
EXEC sp_attach_db @.dbname = N'MessageEngine',
@.filename1 = N'w:\Program Files\Microsoft SQL
Server\MSSQL\Data\MessageEngine.mdf',
@.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'[vbcol=seagreen]
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
Server: Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
exists.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Rather use T-SQL. Open up query analyser and use the following code. Change
> the Db names, files and paths as appropriate:
> EXEC sp_attach_db @.dbname = N'pubs',
> @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
> @.filename2 = N'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\pubs_log.ldf'
>
> "Joe" wrote:
|||Hi
From the command prompt, do a DIR l:\SQLLOGS\MessageEngine_log.LDF
Alos, check the the SQL Server Service account has permissions in the
directory and file.
Regards
Mike
"Joe" wrote:
[vbcol=seagreen]
> I got the same results.--see below:
> EXEC sp_attach_db @.dbname = N'MessageEngine',
> @.filename1 = N'w:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MessageEngine.mdf',
> @.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> Server: Msg 5170, Level 16, State 1, Line 1
> Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
> exists.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> "Mike Epprecht (SQL MVP)" wrote:
|||Make sure you move the old MessageEngine_log.LDF file to L:\SQLLOGS\
directory before you run sp_attach_db.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:94AF06C2-E3A1-4089-9620-7315F6EE9CD0@.microsoft.com...[vbcol=seagreen]
>I got the same results.--see below:
> EXEC sp_attach_db @.dbname = N'MessageEngine',
> @.filename1 = N'w:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MessageEngine.mdf',
> @.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> Server: Msg 5170, Level 16, State 1, Line 1
> Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
> exists.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> "Mike Epprecht (SQL MVP)" wrote:

Detach/Attach database

I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
performed the following:
1> detach database
2> moved the .ldf file to the 'l' drive(deleted from the w drive)
3> attched the database
4> received a RED 'x'on the ldf file >> retyped the new location of the file
and hit OK . The attach failed with the following error.
error 5105:devide activation error: The physical file name '....' may be
incorrect
cannot create '....' because it already exists.Hi
Rather use T-SQL. Open up query analyser and use the following code. Change
the Db names, files and paths as appropriate:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
"Joe" wrote:

> I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
> performed the following:
> 1> detach database
> 2> moved the .ldf file to the 'l' drive(deleted from the w drive)
> 3> attched the database
> 4> received a RED 'x'on the ldf file >> retyped the new location of the fi
le
> and hit OK . The attach failed with the following error.
> error 5105:devide activation error: The physical file name '....' may be
> incorrect
> cannot create '....' because it already exists.
>
>
>
>|||I got the same results.--see below:
EXEC sp_attach_db @.dbname = N'MessageEngine',
@.filename1 = N'w:\Program Files\Microsoft SQL
Server\MSSQL\Data\MessageEngine.mdf',
@.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'[vbcol=seagreen]
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
Server: Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
exists.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Rather use T-SQL. Open up query analyser and use the following code. Chang
e
> the Db names, files and paths as appropriate:
> EXEC sp_attach_db @.dbname = N'pubs',
> @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.md
f',
> @.filename2 = N'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\pubs_log.ldf'
>
> "Joe" wrote:
>|||Hi
From the command prompt, do a DIR l:\SQLLOGS\MessageEngine_log.LDF
Alos, check the the SQL Server Service account has permissions in the
directory and file.
Regards
Mike
"Joe" wrote:
[vbcol=seagreen]
> I got the same results.--see below:
> EXEC sp_attach_db @.dbname = N'MessageEngine',
> @.filename1 = N'w:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MessageEngine.mdf',
> @.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> Server: Msg 5170, Level 16, State 1, Line 1
> Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
> exists.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Make sure you move the old MessageEngine_log.LDF file to L:\SQLLOGS\
directory before you run sp_attach_db.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:94AF06C2-E3A1-4089-9620-7315F6EE9CD0@.microsoft.com...[vbcol=seagreen]
>I got the same results.--see below:
> EXEC sp_attach_db @.dbname = N'MessageEngine',
> @.filename1 = N'w:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MessageEngine.mdf',
> @.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> Server: Msg 5170, Level 16, State 1, Line 1
> Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
> exists.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> "Mike Epprecht (SQL MVP)" wrote:
>

Detach/Attach database

I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
performed the following:
1> detach database
2> moved the .ldf file to the 'l' drive(deleted from the w drive)
3> attched the database
4> received a RED 'x'on the ldf file >> retyped the new location of the file
and hit OK . The attach failed with the following error.
error 5105:devide activation error: The physical file name '....' may be
incorrect
cannot create '....' because it already exists.Hi
Rather use T-SQL. Open up query analyser and use the following code. Change
the Db names, files and paths as appropriate:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
"Joe" wrote:
> I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
> performed the following:
> 1> detach database
> 2> moved the .ldf file to the 'l' drive(deleted from the w drive)
> 3> attched the database
> 4> received a RED 'x'on the ldf file >> retyped the new location of the file
> and hit OK . The attach failed with the following error.
> error 5105:devide activation error: The physical file name '....' may be
> incorrect
> cannot create '....' because it already exists.
>
>
>
>|||I got the same results.--see below:
EXEC sp_attach_db @.dbname = N'MessageEngine',
@.filename1 = N'w:\Program Files\Microsoft SQL
Server\MSSQL\Data\MessageEngine.mdf',
@.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
>>>
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
Server: Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
exists.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Rather use T-SQL. Open up query analyser and use the following code. Change
> the Db names, files and paths as appropriate:
> EXEC sp_attach_db @.dbname = N'pubs',
> @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
> @.filename2 = N'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\pubs_log.ldf'
>
> "Joe" wrote:
> > I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
> > performed the following:
> > 1> detach database
> > 2> moved the .ldf file to the 'l' drive(deleted from the w drive)
> > 3> attched the database
> > 4> received a RED 'x'on the ldf file >> retyped the new location of the file
> > and hit OK . The attach failed with the following error.
> >
> > error 5105:devide activation error: The physical file name '....' may be
> > incorrect
> > cannot create '....' because it already exists.
> >
> >
> >
> >
> >
> >
> >|||Hi
From the command prompt, do a DIR l:\SQLLOGS\MessageEngine_log.LDF
Alos, check the the SQL Server Service account has permissions in the
directory and file.
Regards
Mike
"Joe" wrote:
> I got the same results.--see below:
> EXEC sp_attach_db @.dbname = N'MessageEngine',
> @.filename1 = N'w:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MessageEngine.mdf',
> @.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
> >>>
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> Server: Msg 5170, Level 16, State 1, Line 1
> Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
> exists.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Rather use T-SQL. Open up query analyser and use the following code. Change
> > the Db names, files and paths as appropriate:
> >
> > EXEC sp_attach_db @.dbname = N'pubs',
> > @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
> > @.filename2 = N'c:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\pubs_log.ldf'
> >
> >
> >
> > "Joe" wrote:
> >
> > > I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
> > > performed the following:
> > > 1> detach database
> > > 2> moved the .ldf file to the 'l' drive(deleted from the w drive)
> > > 3> attched the database
> > > 4> received a RED 'x'on the ldf file >> retyped the new location of the file
> > > and hit OK . The attach failed with the following error.
> > >
> > > error 5105:devide activation error: The physical file name '....' may be
> > > incorrect
> > > cannot create '....' because it already exists.
> > >
> > >
> > >
> > >
> > >
> > >
> > >|||Make sure you move the old MessageEngine_log.LDF file to L:\SQLLOGS\
directory before you run sp_attach_db.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:94AF06C2-E3A1-4089-9620-7315F6EE9CD0@.microsoft.com...
>I got the same results.--see below:
> EXEC sp_attach_db @.dbname = N'MessageEngine',
> @.filename1 = N'w:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MessageEngine.mdf',
> @.filename2 = N'l:\SQLLOGS\MessageEngine_log.LDF'
>>>>
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> Server: Msg 5170, Level 16, State 1, Line 1
> Cannot create file 'L:\SQLLOGS\\MessageEngine_log.LDF' because it already
> exists.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'MessageEngine'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'l:\SQLLOGS\MessageEngine_log.LDF' may be incorrect.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Rather use T-SQL. Open up query analyser and use the following code.
>> Change
>> the Db names, files and paths as appropriate:
>> EXEC sp_attach_db @.dbname = N'pubs',
>> @.filename1 = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\pubs.mdf',
>> @.filename2 = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\pubs_log.ldf'
>>
>> "Joe" wrote:
>> > I need to move the ".ldf" log file from my 'w' drive to my 'l' drive. I
>> > performed the following:
>> > 1> detach database
>> > 2> moved the .ldf file to the 'l' drive(deleted from the w drive)
>> > 3> attched the database
>> > 4> received a RED 'x'on the ldf file >> retyped the new location of the
>> > file
>> > and hit OK . The attach failed with the following error.
>> >
>> > error 5105:devide activation error: The physical file name '....' may
>> > be
>> > incorrect
>> > cannot create '....' because it already exists.
>> >
>> >
>> >
>> >
>> >
>> >
>> >

Detach Dbase on SAN with SQL2005

I'm not sure if this is the right place for this but I have an issue.

I have a SQL2005 server with SAN drives for the MDF/LDF files. I have updated recently to SP2.

When I detach a Dbase using the appropriate command it strips the Security on the file level (which SP2 is supposed to fix) and I am unable to attach the database.

I am using a domain account to do this with from the query analyser window.

Anyone have any thoughts on this?

There was no change to the file level security done for detach/attach in SP2. This is by design. Why do you think something was supposed to change?

One thing to look at is why you are doing detach/attach instead of OFFLINE/ONLINE if you are going to reattach to the same instance.

|||

With SAN attached drives, when you detach a Dbase the file level security changes. If using the same account used to execute the script with xpcmdshell you cannot rename the file before attaching. When SP2 is applied it allows you to reattach the file and fixes the security at the same time. Because the security is changed when detaching you have to add security to the file before you can rename it within the same SQL script before attaching.

I was under the impression that the hot fix included in SP2 would not strip all of the secuirty off the files, as a result we have to change the script that is running to add a group to the file before renaming it. I found a work around to our problem, but thanks

|||

There was a change to file permissions done under NAS that was done in SP1 and in a QFE. It is discussed in this KB article.

http://support.microsoft.com/default.aspx/kb/922804

Unfortunately, I do not think the article is complete as the fix is just to add a traceflag to revert to SQL 2000 behavior but the article does not actually mention that. You need traceflag 1802 for this behavior.

|||

Thanks, I got that one earlier. Before this hot fix when you detach the security changes and then you cannot reattach.

When you apply this hotfix, and flag, you can detach and reattach with no issues as the security get "reset" when you reattach.

My issue was that I was running an in house script that would detach, rename the physical files and then reattach with the new name. With the security changed it cannot change the file name.

Workaround i have had to add a line of code to add the local admin group to the file permission before the file rename.

Works for me. Don;t think MS was thinking about this use.

Detach and Attach

Can we use mdf and ldf files from SQl 7 and attach into SQL 2000 instance
Thanks
Yes. The attach process will perform all necessary upgrades. The changes
are to the system stuff only. You will need to update statistics on the
newly upgraded database immediately or query plans for the newly attached
database will be goofy.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:0D2356C7-4E49-458E-B133-1613939DBFB6@.microsoft.com...
> Can we use mdf and ldf files from SQl 7 and attach into SQL 2000 instance
> Thanks

Detach and Attach

Can we use mdf and ldf files from SQl 7 and attach into SQL 2000 instance
ThanksYes. The attach process will perform all necessary upgrades. The changes
are to the system stuff only. You will need to update statistics on the
newly upgraded database immediately or query plans for the newly attached
database will be goofy.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:0D2356C7-4E49-458E-B133-1613939DBFB6@.microsoft.com...
> Can we use mdf and ldf files from SQl 7 and attach into SQL 2000 instance
> Thanks

detach / attach to reduce log file size

Hi, i was wondering if someone could help me. When one detaches a database
via sp_detach_db, all the transactions in the ldf log file are commited to
the mdf file right? After this is done, i delete the old log file and then
do a:
EXEC sp_attach_db 'pubs',
'd:\Data\pubs.mdf'
But how does it know where to put the new ldf log file? Does it have a
memory as to where the original log file was placed? What if i want to place
it somewhere else? And is it possible to attach the database and give it a
new name and location for the log files?
Any help appreciated!
cheers johnThis is not the correct procedure to reduce the size of a transaction log
file. You should use DBCC SHRINKFILE for this purpose.
The location of database data and log files are recorded in the database's
sysfiles table. When you attach a database, attach tries to find the
unspecified files using this table. In the special case that a file is
missing and it is a log file and the database has only one log file, a new
log file will be created. The new log will be created in the log file
default location when the original location is unavailable.
To move your log file (after shrinking, if desired), detach the database,
move the log file and attach specifying all files.
Hope this helps.
Dan Guzman
SQL Server MVP
"john r" <johnr@.trailer.com> wrote in message
news:Oj3bvHN7FHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi, i was wondering if someone could help me. When one detaches a database
> via sp_detach_db, all the transactions in the ldf log file are commited to
> the mdf file right? After this is done, i delete the old log file and then
> do a:
> EXEC sp_attach_db 'pubs',
> 'd:\Data\pubs.mdf'
> But how does it know where to put the new ldf log file? Does it have a
> memory as to where the original log file was placed? What if i want to
> place it somewhere else? And is it possible to attach the database and
> give it a new name and location for the log files?
> Any help appreciated!
> cheers john
>