Copying database using "Copy Database" - still failing after SP2

I am struggling with the "Copy Database Wizard" in SQL Server 2005. I am used to working with DTS to copy data from one server to another, but with the new tools, I am having serious problems (in fact, it's unusable)

1. When copying a database using the "SQL Management Object" method, I always get errors concerning logins. If I select not to copy logins, I get errors "sp_grantdbaccess failed". If I do select to copy logins, the logins can't be created because the machine names are different (one the source machine, the login is called MACHINE1/mylogin, but on the destination machine, such a login cannot be created)

2. For small databases, I ALWAYS get the error "CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database". There is no way to specify a larger size for the database in the wizard.

Note: copying databases using detach/attach is not an option (the servers cannot see eachother's shares)

I must say that in general I hate the new management tools. Enterprise Manager had its flaws too, but the quality of the "SQL Server Management Studio" seems very low IMHO.

Regards,
Philippe Leybaert

I can get the copy to work but it will not copy any of my stored procedures or functions.|||did you ever get a resolution on this. I am having the same issue.|||Yeah - this sucks. Has anyone seen where SP's can be copied? Sure we can script them and run in teh new DB - but it's a pain in the butt, especially if you can see shared drivespace.|||

Blake05 wrote:

I can get the copy to work but it will not copy any of my stored procedures or functions.

This is actually very simple, but the error messages are of no help. The problem is that there is a bug in at least one stored procedure or function. Go through them all until you find the ones that you can't save/update, and repair or delete (if obsolete) them. Once they are all functioning, the copy will work without a hitch.

|||I notice that no one from Microsoft has answered this problem.

It seems to me the Copy Database Wizard is completely ignoring the fact that I am always trying to deselect the logins. It just happily tries to copy them even though it's going to a development machine that will only see 4 of the 100 logins on the production server.

I struggled with this problem for days and was crossing my fingers that it would be solved in SP1, sadly it's not.

Detaching is not an option for production databases!!!

Fix this Microsoft!!!|||

We are aware of many issues in the Copy Database Wizard. We're doing a lot of work on the wizard in SP2 to address these.

If you have a specific scenario you would like to see addressed, please file a defect report here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx. Defects reports entered at the feedback center are used to prioritize work for future versions and service packs.

Thanks,
Steve

|||

Hi Philippe,

You can now install the latest version of the Copy Database Wizard from the following location:

http://www.microsoft.com/sql/ctp.mspx

This CTP version includes many improvements and hopefully they should address most of your current issues.

Thanks,
Oscar.

|||

Hello Oscar

I have a problem with Copy Database Wizard to.

Have agent account as sysadmin in both source and target database

Target server has CTP Dec 2006 installed. Bat I can’t install it to the source “production” system

Managed to transfer five databases fixing the error found at the error log file

At the sixth database I have an error that I can not understand

The error appears when trying to create a view at the target system.

errorCode=-1073548784 description=Executing the query "create view [dbo].[VShift_Cost]

as

select sf.ShiftID, SFD.UnitID, sum(sfd.charge) as Shift_Charge

from shiftcostdetail sfd inner join shiftcost sf on sfd.shiftcostid = sf.shiftcostid

group by sf.shiftid, SFD.unitID

" failed with the following error: "Invalid object name 'shiftcostdetail'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The shiftcostdetail is an existing table and the view works fine at the source system.

Can you help?

Thanks

|||

Hi Panzin,

Could you please tell me where are you launching CDW from? You should launch CDW from the target machine is this machine is the one that has the latest version installed. From here, you can select the source the database and perform the copy. Could you please confirm that this is what you are doing? You could also select the "Save Transfer Logs" option before clicking "Finish" and send me the corresponding TransferDump*.sql files generated in :

C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\Smo

It would be useful too to select "Write log to a file" before performing the copy and check for any other errors.

I will investigate the error that you described above, but it seems that it could be a dependency issue. I will get back to you once I find more information.

Thanks,
Oscar.

|||

Hi Oscar

Thank you for your reply

The CDW is running from the target system.

The target system is the one with the CTP installed

The posted error is from the log file

I can't find the "Save Transfer Logs" option

Thank's again

|||

Hi Panzin,

Are you using the SMO Transfer method to copy the database or Detach/Attach? I believe you are using the SMO Transfer method...

The "Save transfer logs?" checkbox is in the "Configure the Package" page of the wizard.

By the way, are you also copying any extra objects apart from the database? If so, which ones? Are you referencing any objects that are outside this view that might require the selection of some particular extra objects?

Cheers,
Oscar.

|||

Hello Oscar

Thank you for your help

I'm using the SMO transfer method and the related objects that i'm copying are the "logins"

At the configure the Pacage page I'm using

Logging options "Text File" and the posted error is from the text file ...

also show that

1. db created at target server is at compatibility level 90 (source level=80)

2. when I changed the view script and add "dbo." in front of table names the error move to next view creation.

!!! this does not work for all views

Cheers

Nick

|||

Hi Nick,

I would really need to see the full log files (i.e. TransferDump*.sql and SMO Tranfer log) in order to be able to help more. You could send them directly to me at oscar.naim@.microsoft.com.

By the way, are you defining extended properties for this particular database?

Thanks,
Oscar.

|||

Hello oscar

I do not have any extended properties at my database.

I realy can;t find any files named TransferDump*.* in my disk

I found a directory SMO but it is empty

the directory is created under the SQLAgent acount documents and settings...

I monitor the directory as the CDW is executed and a file was created

but when the execution stops with or with out an error the file is deleted

so I can;t postit to you

Thank you