Monday, March 19, 2012

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.

No comments:

Post a Comment