Copy database wizard 1

[wolfert]

Hello,

This is the situation:
I have got an SQL-2k-server running several dbases, which has to be online.
I've got an notebook which I use for development and it's running MSDE 2k.

The problem:
I have developed a new database on my notebook which I want to transfer / copy to the sql-server. If I use the copy database wizard, the following error shows in the log detail:

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700

The SQL-server is logged into our domain with the NT administrator account. My own notebook is running my own username account (which is part of the NT admin group).

Can somebody please explain me what is going wrong?

Thank you in advance,

Patrick

 

[MaffewW]

first thing I'd check is that the SQL Server Service account is started as a domain account which has read access from the local drives on your notebook.

Alternatively instead of using the copy db wizard you could backup the database and restore it on the server, or even quicker, copy the mdf and ldf files from your notebook onto the desired physical drives on the server and use the stored proc sp_attach_db to attach them to the new server. very quick and easy way

example freom BOL
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'



Matt

Brighton, UK

 

[wolfert]

Thanks. It worked (the stored proc).
Are all security issues (user rights) also copied using this routine?


Patrick.

 

[MaffewW]

yes, however you may experience at first a problem with orphaned users. This would occur which ever way you transfer the database (ie its not because you used the proc)

When you set up a logon on SQL Server it is assigned a SID. When you give that logon access to a database it creates a user in the database and gives it the same SID. When you move a db to new server, if you create a new login, then the SIDS no longer match and you may get a message when trying to grant permissions that the user already exists in the db. quite easy to fix, check faq183-3007

Matt

Brighton, UK

 

[wolfert]

Thanks again.

btw: I think you've linked to the wrong FAQ.

Patrick.,

 

[MaffewW]

ooops. faq183-2153

btw, from my first post, did you check the sql server service access or just use the stored proc?

Matt

Brighton, UK

 

[wolfert]

I did check them and both are running under their NT-account (Administrator on the server and my own username on the notebook).


Patrick.

 

[MaffewW]

hmm. on the server is it Domain\administrator or just administrator (which would use the local administrator account)

Matt

Brighton, UK