Copy Database Wizard - failing on the final step

• RossRoss

  SSCommitted

  Points: 1672

  More actions

  January 16, 2014 at 4:45 am

  #288422

  I'm trying to use SQL 2012's Copy Database Wizard to take copy a SQL 2005 database from another server and load it into SQL 2012. At the moment I'm testing things, so the source database is SQL 2005 Standard Edition, and the destination is only SQL 2012 Developer Edition, but I don't see that this should be a problem?

  When it comes time to do it for the production DB, we can't have downtime, hence me testing this method and not just doing a detach/attach.

  Errors are being written to the event log and it keeps failing on the final step when I hit 'Finish'. The event log shows an error with EventID 12291 then a warning with EventID 208, but when I look online everyone seems to have a different take on what causes this, so I'll list what I'm doing and maybe someone could tell me where I'm going wrong please?

• Log on to SSMS on destination machine
• Connect to source server (where I want to pull a copy of the DB from) in the Object Explorer pane
• Right click the DB I want to copy and go to Tasks > Copy Database
• Enter IP and port of the source server, along with 'sa' username and relevant password
• Enter 'localhost' (or click '...' and select the localhost name from the list, I've tried both), and enter the 'sa' username and relevant password
• Select 'Use the SQL Management Object Method' so I can keep the database online during the move
• The database I selected for the copy is already checked in the copy column, so I leave it as-is
• I leave the Destination Database name the same, and set the destination folders for the data and log files, status is 'OK' on both, and both locations have plenty of disk space, I select 'Stop the transfer if a database or file with the same name exists at the destination' (it doesn't so this shouldn't fail)
• I only want the database and logins, so I only have 'Logins' in the 'Selected related objects' pane for the additional objects to copy
• I name the package "Test_Copy_Package_1" and check the box to 'Save transfer logs', the logging options are set to 'Windows event log'
• I leave 'Run immediately' selected, the only option for 'Integration Services Proxy account' that is available is 'SQL Server Agent Service Account'
• I'm now at the page that lists all the choices before clicking finish, they are:

Click Finish to perform the following actions:

Source: 192.168.114.22, 30127 SQL Server 2005, Microsoft SQL Server Standard Edition (64-bit) , Build 5000, Microsoft Windows NT 5.2 (3790) NT AMD64

Destination: SQL2012Dev1 Other SQL Server Version, Microsoft SQL Server Developer Edition (64-bit) , Build 3128, Microsoft Windows NT 6.2 (9200) NT x64

Using SMO online transfer

The following databases will be moved or copied:

Copy:AffiliateSystem_Temp

Destination file will be created: F:\DATA\AffiliateSystem_Temp.mdf

Destination file will be created: G:\LOG\AffiliateSystem_Temp_log.ldf

Stop transfer if duplicate database name exists at destination

Package scheduled to run immediately

After clicking the Finish button, it runs through the first 4 actions successfully then fails on the final one, 'Execute SQL Server Agent job'

The Event Log shows an error on EventID 12291 with the description:

Package "Test_Copy_Package_1" failed.

... and EventID 208 with the description:

SQL Server Scheduled Job 'Test_Copy_Package_1' (0xEE07A7B432F2FF46BE4E33A4BA92FBFD) - Status: Failed - Invoked on: 2014-01-16 05:06:20 - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (Test_Copy_Package_1_Step).

I've checked the Configuration Manager and 'Integration Services' and 'Agent' are running on both the source and destination servers.

Thanks in advance for any help with this, it's probably some tiny little thing I'm missing or haven't considered, which is usually the way!! Any help is much appreciated 🙂