Copy Database problems SP2

Last night I installed SQL Server 2005 SP2. The install went fine and everything seemed to be fine. However a scheduled Database Copy job failed. I deleted the current job and recreated it using the "Copy Database" wizard. I used all the default values with the following exceptions: I used the "SQL Management Object Method" instead of the detach database and I use the "drop the database on destination". I tell it to run the job immediately and the job runs fine.

I then try to run the job again from SQL Agent -> jobs and it fails. I run it again and it runs fine. The pattern repeats, succeed -> fails -> succeed -> fails....

Here is the error message I receive in the history:

Message
Executed as user: EVAASSQLDEV\sqlagent. ...00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:30:06 AM Progress: 2007-03-30 09:30:07.41 Source: EVAASSQLDEV_evaassqldev_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2007-03-30 09:30:16.14 Code: 0x00000000 Source: EVAASSQLDEV_evaassqldev_Transfer Objects Task Description: Alter failed for Database 'PathwaysNewDev_new'. StackTrace: at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl() at Microsoft.SqlServer.Management.Smo.Database.Alter(TerminationClause terminationClause) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.SwitchDatabaseAccess(Database database, DatabaseUserAccess desiredAccess) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus(Database srcDatabase, DatabaseInfo dbDetail) at Mic... The package execution fa... The step failed.
How about privileges for that SQLAgent service account on both the servers?|||It is the same server. Just copying a development db to a testing db. One more thing though, when it fails it puts the destination DB in single user mode. I'm guessing it is trying to get exclusive control over the destination.

copy database feature

Hello,
I am trying to use the copy database funtion from sql2000 to sql2005. It
fails miserably and logs only says:
step 0: The job failed. The Job was invoked by User domain\user. The last
step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
step 1: Executed as user: domain\user. The package execution failed. The
step failed.
There is no destination db already existing, the feature drop any database
on the destination server is activated, it fails at Execute SQL Server Agent
Job.
Although the database is created there is no tables created.
The user in the source and destination server is the same for the sql
service and the agent.
I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
Thanks for the help
Wilfrid
If you are copying the whole db why not just restore from a full backup of
the 2000 db on 2005?
Andrew J. Kelly SQL MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:4399b19d$0$22164$afc38c87@.news.easynet.fr...
> Hello,
> I am trying to use the copy database funtion from sql2000 to sql2005. It
> fails miserably and logs only says:
> step 0: The job failed. The Job was invoked by User domain\user. The
> last step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
> step 1: Executed as user: domain\user. The package execution failed. The
> step failed.
> There is no destination db already existing, the feature drop any database
> on the destination server is activated, it fails at Execute SQL Server
> Agent Job.
> Although the database is created there is no tables created.
> The user in the source and destination server is the same for the sql
> service and the agent.
> I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
> Thanks for the help
> Wilfrid
>
|||The process of dumping a db from one server and then restore it to another server takes longer than just using a feature
available form sql2k5.
I was using, with sql2k, the exporting objects functionnality from one server to another but this feature is not
available anymore with sql 2k5 and I believe it has been replaced with the copy db.
Andrew J. Kelly wrote:
> If you are copying the whole db why not just restore from a full backup of
> the 2000 db on 2005?
>
|||The copy db functionality was available in 200 as well and is not new. But
it is usually not faster or cleaner than a backup and restore. You should
already be doing regular backups so all you have to do is issue the restore.
You don't even have to copy the file first. As long as the folder is
accessible from the other server you can restore directly from there.
Andrew J. Kelly SQL MVP
"grille11" <grille11@.yahoo.com> wrote in message
news:439b429e$0$22164$afc38c87@.news.easynet.fr...[vbcol=seagreen]
> The process of dumping a db from one server and then restore it to another
> server takes longer than just using a feature available form sql2k5.
> I was using, with sql2k, the exporting objects functionnality from one
> server to another but this feature is not available anymore with sql 2k5
> and I believe it has been replaced with the copy db.
>
> Andrew J. Kelly wrote:

copy database feature

Hello,
I am trying to use the copy database funtion from sql2000 to sql2005. It
fails miserably and logs only says:
step 0: The job failed. The Job was invoked by User domain\user. The last
step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
step 1: Executed as user: domain\user. The package execution failed. The
step failed.
There is no destination db already existing, the feature drop any database
on the destination server is activated, it fails at Execute SQL Server Agent
Job.
Although the database is created there is no tables created.
The user in the source and destination server is the same for the sql
service and the agent.
I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
Thanks for the help
WilfridIf you are copying the whole db why not just restore from a full backup of
the 2000 db on 2005?
Andrew J. Kelly SQL MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:4399b19d$0$22164$afc38c87@.news.easynet.fr...
> Hello,
> I am trying to use the copy database funtion from sql2000 to sql2005. It
> fails miserably and logs only says:
> step 0: The job failed. The Job was invoked by User domain\user. The
> last step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
> step 1: Executed as user: domain\user. The package execution failed. The
> step failed.
> There is no destination db already existing, the feature drop any database
> on the destination server is activated, it fails at Execute SQL Server
> Agent Job.
> Although the database is created there is no tables created.
> The user in the source and destination server is the same for the sql
> service and the agent.
> I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
> Thanks for the help
> Wilfrid
>|||The process of dumping a db from one server and then restore it to another s
erver takes longer than just using a feature
available form sql2k5.
I was using, with sql2k, the exporting objects functionnality from one serve
r to another but this feature is not
available anymore with sql 2k5 and I believe it has been replaced with the c
opy db.
Andrew J. Kelly wrote:
> If you are copying the whole db why not just restore from a full backup of
> the 2000 db on 2005?
>|||The copy db functionality was available in 200 as well and is not new. But
it is usually not faster or cleaner than a backup and restore. You should
already be doing regular backups so all you have to do is issue the restore.
You don't even have to copy the file first. As long as the folder is
accessible from the other server you can restore directly from there.
Andrew J. Kelly SQL MVP
"grille11" <grille11@.yahoo.com> wrote in message
news:439b429e$0$22164$afc38c87@.news.easynet.fr...[vbcol=seagreen]
> The process of dumping a db from one server and then restore it to another
> server takes longer than just using a feature available form sql2k5.
> I was using, with sql2k, the exporting objects functionnality from one
> server to another but this feature is not available anymore with sql 2k5
> and I believe it has been replaced with the copy db.
>
> Andrew J. Kelly wrote:

copy database feature

Hello,
I am trying to use the copy database funtion from sql2000 to sql2005. It
fails miserably and logs only says:
step 0: The job failed. The Job was invoked by User domain\user. The last
step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
step 1: Executed as user: domain\user. The package execution failed. The
step failed.
There is no destination db already existing, the feature drop any database
on the destination server is activated, it fails at Execute SQL Server Agent
Job.
Although the database is created there is no tables created.
The user in the source and destination server is the same for the sql
service and the agent.
I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
Thanks for the help
WilfridIf you are copying the whole db why not just restore from a full backup of
the 2000 db on 2005?
--
Andrew J. Kelly SQL MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:4399b19d$0$22164$afc38c87@.news.easynet.fr...
> Hello,
> I am trying to use the copy database funtion from sql2000 to sql2005. It
> fails miserably and logs only says:
> step 0: The job failed. The Job was invoked by User domain\user. The
> last step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
> step 1: Executed as user: domain\user. The package execution failed. The
> step failed.
> There is no destination db already existing, the feature drop any database
> on the destination server is activated, it fails at Execute SQL Server
> Agent Job.
> Although the database is created there is no tables created.
> The user in the source and destination server is the same for the sql
> service and the agent.
> I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
> Thanks for the help
> Wilfrid
>|||The process of dumping a db from one server and then restore it to another server takes longer than just using a feature
available form sql2k5.
I was using, with sql2k, the exporting objects functionnality from one server to another but this feature is not
available anymore with sql 2k5 and I believe it has been replaced with the copy db.
Andrew J. Kelly wrote:
> If you are copying the whole db why not just restore from a full backup of
> the 2000 db on 2005?
>|||The copy db functionality was available in 200 as well and is not new. But
it is usually not faster or cleaner than a backup and restore. You should
already be doing regular backups so all you have to do is issue the restore.
You don't even have to copy the file first. As long as the folder is
accessible from the other server you can restore directly from there.
--
Andrew J. Kelly SQL MVP
"grille11" <grille11@.yahoo.com> wrote in message
news:439b429e$0$22164$afc38c87@.news.easynet.fr...
> The process of dumping a db from one server and then restore it to another
> server takes longer than just using a feature available form sql2k5.
> I was using, with sql2k, the exporting objects functionnality from one
> server to another but this feature is not available anymore with sql 2k5
> and I believe it has been replaced with the copy db.
>
> Andrew J. Kelly wrote:
>> If you are copying the whole db why not just restore from a full backup
>> of the 2000 db on 2005?

Copy Content from Db1 (inconsistent) to Db2

Hi,
the consistency check on my Db1 failed. Though the database seems to be
intact, how ever all tools fail e.g. DTS with error messages.
Does anybody know how to copy the content from Db1 to DB2 (schema is the
same)?
Thanks for any advice
Graham
Graham SmithHi
What is the error you are getting?
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:eJvV8gu3FHA.2640@.TK2MSFTNGP09.phx.gbl...
> Hi,
> the consistency check on my Db1 failed. Though the database seems to be
> intact, how ever all tools fail e.g. DTS with error messages.
> Does anybody know how to copy the content from Db1 to DB2 (schema is the
> same)?
> Thanks for any advice
> Graham
> --
> Graham Smith
>|||insert into db2.owner.tablename
select * from db1.owner.tablename
Or DTS
or backup DB1 and restore on DB2
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Hi,

> insert into db2.owner.tablename
> select * from db1.owner.tablename
thanks a lot. That works great. I have about 30 tables. Do you know how to
script that this statement loops through all user tables?
Thanks in advance
Graham