Correct invalid SID

I migrated my SQL 2000 secuity from one NT Domain to another. In the process of changing the login names in the master..sysxlogins table, the SID did not get updated.

Is there an easy way to correct the SID entry without dropping and recreating each user?

Do you mean that you have manually changed the login names in sysxlogins and you wish to update the SID entries as well? Can you explain what you meant by "the process of changing the login names in the master..sysxlogins table"?

Thanks
Laurentiu

|||

I ran the following in the master db:

UPDATE sysxlogins

Set [name] = 'NEWDOMAIN\' + substring([name], patindex('%\%', [name])+1, 200)

WHERE [name] like 'OLDDOMAIN\%'

It changed all the login names to point to the new domain. The problem was the SIDs changed (which I didn't think about) in the new domain. Users can get in, but when we try to use the function suser_sid(), the correct network sid is being returned and we cant compare it to the one in sysxlogins or sysusers because it doesn't match.

|||

If you move from domain A to domain B, the Windows logins from domain A are normally invalidated. An exception to this would be if domain B was trusted by domain A, then you could still use the A logins even though the server runs in domain B.

This kind of domain change is not a supported operation. There is no supported solution for fixing this. It's not only the logins that you would need to fix, but all the database users as well. If you plan to change the domain for your server often, then you should use only SQL authentication.

Thanks
Laurentiu

sql

Copying SQL logins to new Domain

Good day!
I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
for the time being. But I am starting to move users from that domain
to an AD domain. We are doing them bit by bit. I want the Windows
Authentication-based SQL Logins to move over so that I don't have to
re-create the logins.
What is the best way to do this, assuming I want to do them slowly?
Thanks.Have you considered creating Groups on the new domain, give the groups the
relevant SQL permissions and when you're ready to migrate a user simply add
him to the new group.
HTH
Ryan Waight, MCDBA, MCSE
"Bob" <Bob@.nospam.com> wrote in message
news:nkkfqvg1fvjnc0j8lu9l8c2i2trtvvf8jq@.4ax.com...
> Good day!
> I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
> for the time being. But I am starting to move users from that domain
> to an AD domain. We are doing them bit by bit. I want the Windows
> Authentication-based SQL Logins to move over so that I don't have to
> re-create the logins.
> What is the best way to do this, assuming I want to do them slowly?
> Thanks.
>|||I suppose I could do that. But is there a way to do what I am looking
to do?
On Tue, 4 Nov 2003 16:52:42 -0000, "Ryan Waight"
<Ryan_Waight@.nospam.hotmail.com> wrote:
>Have you considered creating Groups on the new domain, give the groups the
>relevant SQL permissions and when you're ready to migrate a user simply add
>him to the new group.|||Bob wrote:
> Good day!
> I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
> for the time being. But I am starting to move users from that domain
> to an AD domain. We are doing them bit by bit. I want the Windows
> Authentication-based SQL Logins to move over so that I don't have to
> re-create the logins.
> What is the best way to do this, assuming I want to do them slowly?
you might try running sp_help_revlogin and then editing the script to your
liking.|||Hi Bob,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that you would like to transfer the Windows Authentication
Logins and passwords to a new AD domain between the instances of SQL Server. Have I fully
understood you? If there is anything I misunderstood, please feel free to let me know.
I agree with Chxxx that you can create sp_help_revlogin stored procedure on the source
server, which this procedure generates a script that you can run on the destination server to re-
create logins with the original security identification number (SID) and retain the current
passwords.
For how to transfer Logins and Passwords between SQL Server 2000, please refer to the
following step by step articles:
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/?id=274188
Bob, does this answer your question? Please apply the suggestion above and let us know if it
helps you resolve your problem. If there is anything more we can assist you with, please feel
free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||> From your description, I understand that you would like to transfer the
Windows Authentication
> Logins and passwords to a new AD domain between the instances of SQL
Server. Have I fully
> understood you? If there is anything I misunderstood, please feel free to
let me know.
If Bob is interested in the Windows authentication logins, why would he like
to transfer the passwords?
One of the problems with migrating the NT user accounts to AD how to deal
with the following scenario:
1. NT user NTDomain\myUser is migrated to ADDomain\myUser (likely with
SIDHistory)
2. The SID of NTDomain\myUser is different from ADDomain\myUser
3. The system tables sysxlogins and sysusers still store the SID for
NTDomain\myUser
4. If the NTDomain domain is completely disabled, SIDHistory will not be
useful any more
(Note, not being an AD migration expert, I don't know whether you can
migrate NT user accounts to AD without assigning them new SIDs. It's not how
we are migrating user account anyway.)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:7GUTrC0oDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Bob,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your description, I understand that you would like to transfer the
Windows Authentication
> Logins and passwords to a new AD domain between the instances of SQL
Server. Have I fully
> understood you? If there is anything I misunderstood, please feel free to
let me know.
> I agree with Chxxx that you can create sp_help_revlogin stored procedure
on the source
> server, which this procedure generates a script that you can run on the
destination server to re-
> create logins with the original security identification number (SID) and
retain the current
> passwords.
> For how to transfer Logins and Passwords between SQL Server 2000, please
refer to the
> following step by step articles:
> 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
Server
> http://support.microsoft.com/?id=246133
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> 274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
> http://support.microsoft.com/?id=274188
>
> Bob, does this answer your question? Please apply the suggestion above and
let us know if it
> helps you resolve your problem. If there is anything more we can assist
you with, please feel
> free to post it in the group.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Hi Bob,
I agree with Linchi's comments and thanks for his reminder.
If you transfer logins to an instance of SQL Server in a different domain than the source
instance of SQL Server, you should edit the script generated by the sp_help_revlogin
procedure, and REPLACE the domain name with the new domain in the sp_grantlogin
statements. Because the integrated logins granted access in the new domain will not have the
same SID as the logins in the original domain, the database users will be orphaned from
these logins.....
This is announced in the "Remarks" of the KB 246133. I recommend you review the remarks
carefully before you apply this logins transference, so that you will not miss some important
points you should pay attention to.
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
If there is anyting unclear or anything more I can assist you with, please feel free to post it in the
group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

copying data from sql server 2000 in one domain to sql server 2005 in another

I'm trying to copy my databases (and ideally do this as a regularly
scheduled job but getting it to happen once will get me started) from the
old SQL Server 2000 to a new SQL Server 2005 database in another domain.
There is no trust between the domains.
I've gotten export/import using the GUI in 2005's Management Studio to work
but that didn't bring over the stored procedures or even the primary keys.
I'm assuming anything I do to bring over objects will lose user/group
rights, but I'm hoping I can at least bring over rights assigned to roles.
The database copy wizard failed with an error of
SQL Server Scheduled Job 'CDW_src_dst_0'
(0x214353478B078E48922901599064B5C1) - Status: Failed - Invoked on:
2006-02-06 15:46:32 - Message: The job failed. Unable to determine if the
owner (ADS\beh) of job CDW_CEREBRUM_PINEAL_0 has server access (reason:
Could not obtain information about Windows NT group/user 'ADS\beh', error
code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminated.
[SQLSTATE 01000] (Error 3621)).
The database is running under a regular domain account with minimal privs on
the database server. The sql server agent service is running under a
different account, with administrator permissions on the database server.
ADS\beh is the account I signed onto the database server with and that is
part of the administrators group on the destination server, where the
message was logged.
Has anybody done this yet? Or at least does anybody have any ideas?If you want to being over all the data and the db objects the easiest is
usually to restore a full backup onto the other server. You will have to
then sync the Logins to the db you just restored. Most of these are from
2000 but the overall rules and such still apply for most things:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"news.microsoft.com" <beh@.case.edu> wrote in message
news:eiL$tw2KGHA.1180@.TK2MSFTNGP09.phx.gbl...
> I'm trying to copy my databases (and ideally do this as a regularly
> scheduled job but getting it to happen once will get me started) from the
> old SQL Server 2000 to a new SQL Server 2005 database in another domain.
> There is no trust between the domains.
> I've gotten export/import using the GUI in 2005's Management Studio to
> work but that didn't bring over the stored procedures or even the primary
> keys. I'm assuming anything I do to bring over objects will lose
> user/group rights, but I'm hoping I can at least bring over rights
> assigned to roles.
> The database copy wizard failed with an error of
> SQL Server Scheduled Job 'CDW_src_dst_0'
> (0x214353478B078E48922901599064B5C1) - Status: Failed - Invoked on:
> 2006-02-06 15:46:32 - Message: The job failed. Unable to determine if the
> owner (ADS\beh) of job CDW_CEREBRUM_PINEAL_0 has server access (reason:
> Could not obtain information about Windows NT group/user 'ADS\beh', error
> code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621)).
> The database is running under a regular domain account with minimal privs
> on the database server. The sql server agent service is running under a
> different account, with administrator permissions on the database server.
> ADS\beh is the account I signed onto the database server with and that is
> part of the administrators group on the destination server, where the
> message was logged.
> Has anybody done this yet? Or at least does anybody have any ideas?
>

copying data from sql server 2000 in one domain to sql server 2005 in another

I'm trying to copy my databases (and ideally do this as a regularly
scheduled job but getting it to happen once will get me started) from the
old SQL Server 2000 to a new SQL Server 2005 database in another domain.
There is no trust between the domains.
I've gotten export/import using the GUI in 2005's Management Studio to work
but that didn't bring over the stored procedures or even the primary keys.
I'm assuming anything I do to bring over objects will lose user/group
rights, but I'm hoping I can at least bring over rights assigned to roles.
The database copy wizard failed with an error of
SQL Server Scheduled Job 'CDW_src_dst_0'
(0x214353478B078E48922901599064B5C1) - Status: Failed - Invoked on:
2006-02-06 15:46:32 - Message: The job failed. Unable to determine if the
owner (ADS\beh) of job CDW_CEREBRUM_PINEAL_0 has server access (reason:
Could not obtain information about Windows NT group/user 'ADS\beh', error
code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminat
ed.
[SQLSTATE 01000] (Error 3621)).
The database is running under a regular domain account with minimal privs on
the database server. The sql server agent service is running under a
different account, with administrator permissions on the database server.
ADS\beh is the account I signed onto the database server with and that is
part of the administrators group on the destination server, where the
message was logged.
Has anybody done this yet? Or at least does anybody have any ideas?If you want to being over all the data and the db objects the easiest is
usually to restore a full backup onto the other server. You will have to
then sync the Logins to the db you just restored. Most of these are from
2000 but the overall rules and such still apply for most things:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
s.asp" target="_blank">http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"news.microsoft.com" <beh@.case.edu> wrote in message
news:eiL$tw2KGHA.1180@.TK2MSFTNGP09.phx.gbl...
> I'm trying to copy my databases (and ideally do this as a regularly
> scheduled job but getting it to happen once will get me started) from the
> old SQL Server 2000 to a new SQL Server 2005 database in another domain.
> There is no trust between the domains.
> I've gotten export/import using the GUI in 2005's Management Studio to
> work but that didn't bring over the stored procedures or even the primary
> keys. I'm assuming anything I do to bring over objects will lose
> user/group rights, but I'm hoping I can at least bring over rights
> assigned to roles.
> The database copy wizard failed with an error of
> SQL Server Scheduled Job 'CDW_src_dst_0'
> (0x214353478B078E48922901599064B5C1) - Status: Failed - Invoked on:
> 2006-02-06 15:46:32 - Message: The job failed. Unable to determine if the
> owner (ADS\beh) of job CDW_CEREBRUM_PINEAL_0 has server access (reason:
> Could not obtain information about Windows NT group/user 'ADS\beh', error
> code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621)).
> The database is running under a regular domain account with minimal privs
> on the database server. The sql server agent service is running under a
> different account, with administrator permissions on the database server.
> ADS\beh is the account I signed onto the database server with and that is
> part of the administrators group on the destination server, where the
> message was logged.
> Has anybody done this yet? Or at least does anybody have any ideas?
>

copying data from sql server 2000 in one domain to sql server 2005 in another

I'm trying to copy my databases (and ideally do this as a regularly
scheduled job but getting it to happen once will get me started) from the
old SQL Server 2000 to a new SQL Server 2005 database in another domain.
There is no trust between the domains.
I've gotten export/import using the GUI in 2005's Management Studio to work
but that didn't bring over the stored procedures or even the primary keys.
I'm assuming anything I do to bring over objects will lose user/group
rights, but I'm hoping I can at least bring over rights assigned to roles.
The database copy wizard failed with an error of
SQL Server Scheduled Job 'CDW_src_dst_0'
(0x214353478B078E48922901599064B5C1) - Status: Failed - Invoked on:
2006-02-06 15:46:32 - Message: The job failed. Unable to determine if the
owner (ADS\beh) of job CDW_CEREBRUM_PINEAL_0 has server access (reason:
Could not obtain information about Windows NT group/user 'ADS\beh', error
code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminated.
[SQLSTATE 01000] (Error 3621)).
The database is running under a regular domain account with minimal privs on
the database server. The sql server agent service is running under a
different account, with administrator permissions on the database server.
ADS\beh is the account I signed onto the database server with and that is
part of the administrators group on the destination server, where the
message was logged.
Has anybody done this yet? Or at least does anybody have any ideas?
If you want to being over all the data and the db objects the easiest is
usually to restore a full backup onto the other server. You will have to
then sync the Logins to the db you just restored. Most of these are from
2000 but the overall rules and such still apply for most things:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/colu...rdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scri...tions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"news.microsoft.com" <beh@.case.edu> wrote in message
news:eiL$tw2KGHA.1180@.TK2MSFTNGP09.phx.gbl...
> I'm trying to copy my databases (and ideally do this as a regularly
> scheduled job but getting it to happen once will get me started) from the
> old SQL Server 2000 to a new SQL Server 2005 database in another domain.
> There is no trust between the domains.
> I've gotten export/import using the GUI in 2005's Management Studio to
> work but that didn't bring over the stored procedures or even the primary
> keys. I'm assuming anything I do to bring over objects will lose
> user/group rights, but I'm hoping I can at least bring over rights
> assigned to roles.
> The database copy wizard failed with an error of
> SQL Server Scheduled Job 'CDW_src_dst_0'
> (0x214353478B078E48922901599064B5C1) - Status: Failed - Invoked on:
> 2006-02-06 15:46:32 - Message: The job failed. Unable to determine if the
> owner (ADS\beh) of job CDW_CEREBRUM_PINEAL_0 has server access (reason:
> Could not obtain information about Windows NT group/user 'ADS\beh', error
> code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621)).
> The database is running under a regular domain account with minimal privs
> on the database server. The sql server agent service is running under a
> different account, with administrator permissions on the database server.
> ADS\beh is the account I signed onto the database server with and that is
> part of the administrators group on the destination server, where the
> message was logged.
> Has anybody done this yet? Or at least does anybody have any ideas?
>

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
--
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
--
thanks - dave
"Michael Cheng [MSFT]" wrote:
> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>> Hi Dave,
>> Thanks for your post.
>> From your descriptions, I understood you would like to copy database via
>> two computer that are not in the same domain. If I have misunderstood
>> your
>> concern, please feel free to point it out.
>> Here are two Knowledge Base articles describing how to move database
>> between computers. You could BACKUP the database and then use WITH MOVE
>> parameter to RESTORE it in the new computer.
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/kb/314546
>> INF: Using the WITH MOVE Option with the RESTORE Statement
>> http://support.microsoft.com/kb/221465
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.
> thanks - dave
>
John
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>> Hi Dave,
>> Thanks for your post.
>> From your descriptions, I understood you would like to copy database via
>> two computer that are not in the same domain. If I have misunderstood
>> your
>> concern, please feel free to point it out.
>> Here are two Knowledge Base articles describing how to move database
>> between computers. You could BACKUP the database and then use WITH MOVE
>> parameter to RESTORE it in the new computer.
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/kb/314546
>> INF: Using the WITH MOVE Option with the RESTORE Statement
>> http://support.microsoft.com/kb/221465
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - dave
Hi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.