Can't copy Sql Server 2000 Database to SQL Server 2008 Help!!!!

[bmann]

What does this mean below. I can't copy a database from sql server 2000 to sql server 2008. I would appreciate anyone's help.

The “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’” that occurs when copying a database can be solved by ensuring the ID running the “SQL Server Agent (MSSQLSERVER)” service has access to the source and target database.

 

[simian336]

What method are you using to copy the database?

And is this a one time thing or something your automating?

Simi

 

[bmann]

Thanks for Responding. I right click Managment tab in SQL Server Management Studio. Then I go through step and at the end it fails due to The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" that occurs when copying a database can be solved by ensuring the ID running the "SQL Server Agent (MSSQLSERVER)" service has access to the source and target database.

 

[simian336]

I have never used that wizard but I imagine it has something with the current used trying to access the 2000 server.

If it were me I would backup the database on the 2000 server and just use RESTORE on the 2008 server.

Simi

 

[gmmastros]

I agree with Simi. Backup and restore is the easiest way to do it.

The reason you are getting the error is because you are trying to do it completely within SSMS. More specifically, because you are trying to connect to a different computer. SQL Server uses a windows account for the service to start. This can be changed by going to services, right clicking on the SQL Server service, and then changing the Log On account. If you only want to do this one, then backup, manually copy the backup file to the other computer, and then restore. If you need to do this regularly, then you may want to consider setting up a linked server or some other means of copying the data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom