transfer database task error
hi,
I have created a SSIS package that does nothing more than loop through all DBs and copies the userDBs to another server. However, I keep getting an error after the task has created the database during its execution of "Create Role" statements. Here is the error:
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [aspnet_WebEvent_FullAccess] " failed with the following error: "User, group, or role 'aspnet_WebEvent_FullAccess' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter.
Now it appears to me that the Transfer DB task keeps using master as the current database even after it has created the new DB? Why would it does this when at the source the database role is under the usersDB?
thanks,
Derek
I was able to work around this by using a Backup Database Task paired with a Execute SQL Task. You use the Backup Database task to backup your entire database into a .bak file on a specific disk location and name. You would then use the Execute SQL Task to drop and then restore the database that would be your destination. Below is the SQL for just that...
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'destinationDB'
GO
USE [master]
GO
/****** Object: Database [destinationDB] Script Date: 08/30/2006 20:05:15 ******/
DROP DATABASE [destinationDB]
GO
RESTORE DATABASE [destinationDB]
FROM DISK = N'C:\DatabaseBackups\destinationDB.bak' WITH FILE = 1,
MOVE N'destinationDB_dat' TO N'E:\MSSQL2005\destinationDB.mdf',
MOVE N'whd_log' TO N'E:\MSSQL2005\destinationDB.ldf',
MOVE N'sysft_whdCatalog' TO N'E:\MSSQL2005\destinationDB',
NOUNLOAD,
REPLACE,
STATS = 10
GO
6SC at 2007-8-30 >
Hello Microsoft,
I still have the problem that has been described by other messages in this forum: that if the transfer property "DatabaseOnline" is "True" and the Source and Destination Databases are the same, then an error is thrown by SSIS because it attempts to create database instance user accounts that already exist.
I've applied SQL Server 2005 Service Pack 1 + the post Service Pack 1 hotfixes: Cumulative hotfix package (build 2153) for SQL Server 2005 (http://support.microsoft.com/kb/918222).
The problem still exists after the hotfix package has been installed.
An alternative workaround is to transfer the database whilst it is offline - but this is a problem because SQL Server detaches the database, copies the MDF and LDF files, then reattaches the database and attaches the new destination database. This process requires that nobody can be connected to the Source database.
Is there an estimate as to when Microsoft will fix the issue?
Kind regards,
Jonathan.
