When I try to copy or move a DB from a sql server 2000 to a sql srvr 2005 using sql 2005 "copy database wizard", I get an error in the SQL Server Agent Job execution.
The two databases are on different PC, and same problem occurs if I try to copy/move a DB from sql 2005 to an other sql 2005 server.
Wizard does not fail if the copy is done on the same machine.
Following is the log generated:
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
Any Idea to help?
thanks
The easiest way to move a database from SQL Server 2000 to SQL Server 2005 is to detach the database files from SQL Server 2000 and attach them to your SQL Server 2005 instance. To do that:
1) In SQL Server 2000, make a complete backup of your database (better safe than sorry!)
2) Detach the database from SQL Server 2000. You can use SQL Server Management Studio 2005 to do this if you like. Start SSMS, connect to your SQL Server 2000 server, expand the databases folder, right click on your database and select "Tasks > Detach" from the context menu.
3) Copy all the .mdf, .ldf, and .ndf files for your database to a location accessible to your SQL Server 2005 instance. You can use Windows Explorer to do this.
4) Attach the copied files to your SQL Server 2005 instance. You can do this SSMS as well. Connect to your SQL Server 2005 instance, right click on the databases folder and select "Attach" in the context menu. In the Attach Database dialog, you just need to add the .mdf file - the dialog will find the other files if you have copied all of them to the same directory - and click OK.
You can also move your database by restoring a full backup of the SQL Server 2000 database on the SQL Server 2005 server.
Once your database has been attached (or restored), make sure it has a valid owner. If the SQL Server 2000 database was owned by a SQL Authentication login, the new server won't know about the owner on the old server. You can change the database owner to a login the SQL Server 2005 server knows about using the database properties dialog in SSMS - just right click on the new database and select properties. The owner is defined on the "Files" page of the dialog.
Hope this helps,
Steve
