MS SQL Server - copying data from sql server 2000 in one domain to sql server 2005 in another

Author

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

news.microsoft.com

2006-02-06, 8:23 pm

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'
(0x214353478B078E489
22901599064B5C1) - 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?

Andrew J. Kelly

2006-02-06, 8:23 pm

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
http://www.sqlservercentral.com/col...irdatabases.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'
> (0x214353478B078E489
22901599064B5C1) - 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?
>

Sponsored Links