error with Copy Database Wizard 1

[shoalcreek]

I am copying databases from SQL 2000 to SQL 2005. I have tried using the Copy Database Wizard many times. It fails on the "Execute SQL job agent." I have tried it over 20 times. Event Viewer keeps giving a 12550 error.

It seems as though there is some issue with permissions. I am using a Windows account that is in the Domain Admins group. It should have access to everything.

Some forums have said that the wizard is "flaky." But I would like to know what needs to be done to get this to work. I just went to Barnes and Noble to see if any books would have the answer. None of them did, and I must have looked at well over ten.

Named pipes and TCP/IP are enabled on both servers. I am using the detach-attach method.

I have done some backups and restores. Those have worked fine.

Please give detailed answers. Thank you.

 

[shoalcreek]

Here is what appeared in event viewer. It's pretty long:

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 2/27/2008
Time: 4:15:55 PM
User: NT AUTHORITY\SYSTEM
Computer: SQL2005-TEST
Description:
Event Name: OnError
Message: Failed to connect to server clustersql.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->Login failed for user 'CORP\SQL2005-TEST$'.
StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
Operator: NT AUTHORITY\SYSTEM
Source Name: clustersql_SQL2005-TEST_Transfer Objects Task
Source ID: {2924D7E4-E981-4674-AE96-6B5ED0DC3890}
Execution ID: {F6372EB5-14D4-43BC-A3BD-82B94AA63971}
Start Time: 2/27/2008 4:15:55 PM
End Time: 2/27/2008 4:15:55 PM
Data Code: 0

For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

 

[mrdenny]

The computer account for SQL2005-TEST doesn't have rights to log into your sql cluster. This is because you have the SQL 2005 machine running under the local system account. Have the services start under a domain account with rights to the production database and try running the job again.

Or simply not use the copy database wizard and backup and restore the database manually.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[shoalcreek]

Thank you. How do I make that change? How do I have the SQL 2005 run under a domain account?

 

[shoalcreek]

I made the change on the services. It is still failing. It is taking longer to fail though. Please give as much detail as possible.

I'm willing to pay you if you want to help out with this on the phone.

 

[mrdenny]

If it's taking longer to fail then something else is going on now. Edit the job and set it up on the advanced tab to write the output to a text file. This will give you a full log of what is going on.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[shoalcreek]

Thanks for your help. I should probably buy you a beer.

 

[mrdenny]

No problem. Did you get the database copied?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[shoalcreek]

Right now, we are in the process of changing servers. The department here is extremely disorganized.

 

[mrdenny]

Ah, been there, done that. Do post your end results when you are all done.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[shoalcreek]

It failed again. I looged to a text file. It is quite long.

How about you just tell me what works? I think it's permissions thing. What user should I used to do this? Should I use the "sa" acount?

 

[shoalcreek]

The text files which log the failed transfer are over 400MB so far.

 

[mrdenny]

With the wizard not working, just backup the database, move the backup file to the new server, then restore the database.

This will be much easier.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[shoalcreek]

That was definitely NOT the answer I was looking for.

 

[mrdenny]

Why not? It's the easiest, most reliable way to move databases from server to server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[shoalcreek]

It does seem to be the easiest that actually works.