Error copying SQL 2000 DB to SQL 2005

Hi all,
We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
is the error message received. We are doing this by using the 'copy
database' feature in SQL 2005, is this the correct way to migrate a DB
to 2005? The database is not in use so we are unsure why this error
would be occuring. And on top of this we did not instruct SQL to drop
the DB?
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message
ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
outcome),,The job failed. The Job was invoked by User DOMAIN\user.
The last step to run was step 1 (CDW_BUBBA_USER_Step).,
00:02:28,0,0,,,,0
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
\USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
Task Task just started the execution.: 0% complete End Progress
Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
Bubba_USER_Transfer Objects Task
Description: ERROR : errorCode=-1073548784 description=Executing the
query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
N'test_sb') DROP DATABASE [test_sb] " failed with the following
error: "Cannot drop database "test_sb" because it is currently in
use.". Possible failure reasons: Problems with the query<c/>
"ResultSet" property not set correctly<c/> parameters not set
correctly<c/> or connection not established correctly. helpFile=
helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
DA807BCDC2AC} StackTrace: at
Microsoft.SqlServer.Management.Dts.D... The package execution fa...
The step failed.,00:02:28,0,0,,,,0Hello,
That should wor, do you the destination DB already there in SQL 2005 server.
If yes then try dropping the databaase and see if it works.
Alternate methodology is
1. Backup the database in sql 2000
2. COpy the file to SQL 2005 server
3. restore the database in SQL 2005. THis will automatically upgrade to SQL
2005.
4. Run a update statistics after upgrade,
Thanks
Hari
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>|||>> "Cannot drop database "test_sb" because it is currently in use."
A database cannot be dropped if there is currently a connection open to it.
Try closing all connections on the server being copied to that are connected
to the database you are trying to copy over. sp_who2 will show you this,
and you can use the KILL command to kill off any spids that show connection
to that database. Make sure you are on the correct server when you do this!
:-)
TheSQLGuru
President
Indicium Resources, Inc.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>|||The best way is backup the database, copy the backup to destination server,
restore the database at destination server, change the compatibility to 9.0,
update statistic.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>