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
>>
Showing posts with label mdf. Show all posts
Showing posts with label mdf. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
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:
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:
>
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:
>
Detaching a database and CHECKPOINT
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>
Detaching a database and CHECKPOINT
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>
Friday, March 9, 2012
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 attaching changing file permissions
Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks Ellie
I've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks Ellie
I've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
Detach and attaching changing file permissions
Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>> Hi Ellie,
>> The change in permissions on the mdf file after detaching and attaching
>> is by design in SQL Server 2005. This Books Online topic explains what
>> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Ellie" <nospam@.nospam.net> wrote in message
>> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>>
>
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>> Hi Ellie,
>> The change in permissions on the mdf file after detaching and attaching
>> is by design in SQL Server 2005. This Books Online topic explains what
>> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Ellie" <nospam@.nospam.net> wrote in message
>> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>>
>
Detach and attaching changing file permissions
Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/...r/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/...r/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
Detach and Attach for Backup and Deployment?
Hello,
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus
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
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
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
Subscribe to:
Posts (Atom)