I had to move a database to Microsoft SQL 2005 today. I managed to get both the servers to attach in SQL Management Studio (2005) and use the Copy Database Wizard (CDW) (Right click database, tasks, copy database) to set up a job (SQL Server Agent -> Jobs in the management studio) but it was failing. Event logs indicated it was because it was trying to log into the remote server as ‘DOMAIN\ANONYMOUS LOGIN’ or something stupid.

I tried with both the “detach and attach method’ where you have to provide a share path to the data files (I just used the administrative share on the source server) and the ‘sql management object method’. While I had domain admin credentials on both servers, they were in different domains (part of the reason of the migration). I think the failure was the attempt to use the “Integration Services Proxy Account” which probably is not setup at all, or obviously correctly.

Event log shows Source: SQLISPackage, Event ID: 12550:

Event Name: OnError
Message: Failed to connect to server SOURCESERVER.
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 ‘NT AUTHORITY\ANONYMOUS LOGON’.

I ended up detaching the database via the management stupid and copying the ldf and mdf files to the new servers data directory. Then running a query to attach it (one could probably do this visually). I got the query template from here rather than trying to remember how to type it. the “FOR ATTACH” part is important, heh.

‘Converting database ‘blah’ from version 539 to the current version 611.’ Showed up the query results, so it looks like it “upgraded” on it’s own.