Errors when copying db from 2000 to 2005

[jeffmoore64]

Hi,
I'm tring to use the copy db wisard to move a db from 2000 to 2005. I keeps failing and giving me no real clue as to why.
This is what I see in the event viewer:

There is a reference to a db called "salesmart". This is not the db I'm trying to transfer. But it is used in some of the view in the db I'm transfering. Could that be my issue?
If so, how do i transfer (and upgrade) my dbs?
TIA

Event Type:	Error
Event Source:	SQLISPackage
Event Category:	None
Event ID:	12550
Date:		5/18/2007
Time:		1:57:11 PM
User:		NT AUTHORITY\SYSTEM
Computer:	WTLBOBSQLTEST
Description:
  Event Name: OnError
 Message: An exception occurred while executing a Transact-SQL statement or batch.
StackTrace:    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands)
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CreateObject(String database, StringCollection script)
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferJobs(DatabaseObject dbObject)
InnerException-->The specified @database_name ('SalesMart') does not exist.
StackTrace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
 Operator: NT AUTHORITY\SYSTEM
 Source Name: CDW_WATERLOO18_WTLBOBSQLTEST_BOB2005_3
 Source ID: {AD04B7A4-7278-4958-8BAE-60AFFF28E399}
 Execution ID: {BD312B31-4D4D-4BE3-BA7F-382E003BEF21}
 Start Time: 5/18/2007 1:57:11 PM
 End Time: 5/18/2007 1:57:11 PM
 Data Code: 0

For more information, see Help and Support Center at [URL unfurl="true"]http://go.microsoft.com/fwlink/events.asp.[/URL]

 

[mrdenny]

You would either need to move all the databases to the new SQL 2005 server, or change any views and procedures which are looking for the other database(s) to use linked servers back to the SQL 2000 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)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jeffmoore64]

The "other" dbs currently reside on the same server as the db I'm moving.
Okay, so if I try using the wisard to copy ALL the dbs at once it will work? Thats a big, BIG move...
I was hoping to move the dbs piece by piece over the next 2 weeks...
Jeff

 

[SQLBill]

You have two options.

1. Move all of the databases at one time.
2. Move them singly and when you get an error like the one you posted, restore the database that it says is missing, then re-restore the one you were doing.

#2 can be a real pain as one restore fails and you have to restore another db before you can restore the one you were working on. And then what happens if that restore fails because of a missing db? For example, let's say you try to restore SalesMart and it gives you an error that database X is missing? Now you have to restore X before you restore SalesMart, before you restore the one you first tried to restore. That could take more man-hours than the other option.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

If you have the time to do all the needed testing I would recommend moving everything in one shot.

You may have many databases, but they appear to all be tied together so it should probably be thought of as a single application and should all be done at once.

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)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jeffmoore64]

Any thoughts on how long it will take to move 1/2 terabyte of data using the copy method. I have gigabit NICs.
Jeff

 

[mrdenny]

Gig NICs mean nothing. It's all about disk speed. Create a new 1 Gig database file, then detach the file, and copy it accross. Take that number *500 and you have the amount of time.

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)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com