Error 0x80040E4D when copying database from 2005 to 2008

When you say that 'I know the password used for 'sa' is correct because I connect to the server via Management Studio using the same password.' is this on the local or remote end. Just wondering if possibly it is generating error on the local sa -- during installation if you didn't set a sa password it could be either disabled or different than sa for the remote ...

I have different passwords for the local and remote machines.  I know they're both correct because I set them myself and use the remote one frequently.  In order to connect to the two machines within Management Studio these would both have to be correct, and yes, I can connect to both successfully, which is puzzling!

One thing I did wonder about was whether I installed 2008 incorrectly on my development machine.  When it asked for the Windows account to use to run the services, I chose my own account (which is a Windows 7 admin account).  I see however that other SQL services use a different account.  Not sure if that might be causing the problem.

Okay. You can try setting the account of the service to use Network Service. I have copied recently database objects to my local machine from a production server with different credentials, so that should work.

Check this resource also regarding authentication chosen during installation Windows or SQL: http://msdn.microsoft.com/en-us/library/ms188670.aspx

But again shouldn't matter as I believe when I did my copy I used Windows for my local just fine. My service is running with Network Service though. Also ensure that you have the protocols like TCP/IP enabled in the SQL Configuration Manager and that your firewall is allowing traffic through on port 1433.

I've checked the firewall, and that TCP/IP is running in the configuration tools.
Strangely, within the Copy Database Wizard, when I come to select the Destination Wizard, my development machine is not shown in the Local Servers list - the list is empty.  It does appear within the Network Servers list using its machine name.   Is that normal because when I used 2005 it appeared as expected in the Local list?

I've also used the config manager to make sure all services now run under NTAUTHORITY\LocalService but it hasn't made any difference to solving the issue...

That is very peculiar. I will make a call for some other SQL heavy hitters to see what I am missing.

If you log into the SQL 2008 machine, bring up 2008 SSMS can you see the 2005 database using the copy database wizard ?

Think your error has more to do with not finding the destination more so the source (maybe)...

Will put on my thinking cap :)

Any reason to not use the backup/restore method here? Ok, it does not copy the SQL users, only database users, but I don't think that is a obstacle here.

>> I will make a call for some other SQL heavy hitters to see what I am missing.

Much appreciated, thank you.


>> If you log into the SQL 2008 machine, bring up 2008 SSMS can you see the 2005 database using the copy database wizard ?

Within SSMS on the 2008 development machine, I can see the live 2005 database within the Copy Database Wizard.  I can also alter the live 2005 db via SMSS 2008 on my dev machine.  I attach a screen grab (although blurred the names to protect my other clients privacy)
Also shown is the fact that the dev machine does not find 'itself' under the Local Services list.


>> Any reason to not use the backup/restore method here? Ok, it does not copy the SQL users, only database users, but I don't think that is a obstacle here.

Only because the Copy Database Wizard method is what I've always used, so there was never a need to change.  I'm not familiar with Backup/Restore across different servers.  Can the live database stay online while this happens?

Capture.PNG
Capture2.PNG

Hi

Thanks both for your coding samples.  Apparently according to my web host, I cannot do manual backups because it messes up their own backup software.  They say that using the Copy Database Wizard is the way to go.

Sooo, back to square one... :-(

That is why one uses "WITH COPY_ONLY" so it doesnt interfere with any other backup operations. It really is designed for copying a database more so than backing it up.

Anyway, from 2008 SSMS, using copy database wizard, you can point to the 2005 db as the source db, but cannot seem to select your own 2008 server (the one currently logged into via SSMS) as a destination server - is that correct ?

Oh, and because you want it online at the same time, then do not use the detach attach method...

Security can be a right pain in theproverbial if going from one server to another. Are they physically on the same machine, or, seperate servers / domains ?

Have you configured for remote connections ? If s specific named instance, have you got SQL Browser Service running ?  Because you can save as a SSIS package, are you running immediately, or trying to run afterwards as an SSIS package and then do you have SQL SERVER AGENT up and running (e.g. SQL Server Agent is not part of express, so must run it interactively) ?

When you log into  SSMS does it as you to connect to a server ? What server does it display there ? If connected via SSMS, you could display properties of the server you are connected to - that should tell you its name (there is also a "connections property" link down toward the bottom left of the properties page). You should be able to type in the name...

here is a pretty good step by step guide which also adresses proxy and credentials : http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard

Hi Mark

Let me answer your questions in turn....

>> you can point to the 2005 db as the source db, but cannot seem to select  your own 2008 server (the one currently logged into via SSMS) as a  destination server - is that correct ?

Yes.  It does not appear within the Local Servers list, and in Network Servers appears as the machine name.  I can type in my own value (local) and choose Use Windows Authentication and the wizard lets me proceed, but it still fails to copy.

>> Are they physically on the same machine, or, seperate servers / domains ?

They are on different servers.

>> Have you configured for remote connections ?
>> have you got SQL Browser Service running ?

My destination (2008) machine is running SQL Browser and is configured for remote connections (see image).

are you running immediately, or trying to run afterwards as an SSIS package and then do you have SQL SERVER AGENT up and running

Capture.PNG

Sorry pressed Submit by mistake.  Here is the rest of the answers...

>> are you running immediately, or trying to run afterwards as an SSIS package and then do you have SQL SERVER AGENT up and running

I am running immediately.  SQL Server Agent is running.

>> When you log into  SSMS does it as you to connect to a server ? What server does it display there ?

Yes it does.  It lists the remote server (IP address running 2005), (local), and the machine name DELL-1530.  I usually connect to (local) with Windows Authentication, but again (local) is not listed as a Local Server within the wizard, which seems very odd!

>> here is a pretty good step by step guide which also adresses proxy and credentials :
>>http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard

Tried this (creating Credential and Proxy) but the process failed (although took longer to fail after the copying started).  The error log doesn't actually show any errors for the time of failure, although one entry from earlier on says:

"Login Failed for user 'sa'. Reason: Password did not match that for the login provided [CLIENT: <local machine>]"
Error 18456 Severity 14 State 8

Yeah, sounds a bit strange....

But if they are two different servers (and different domains ?) then you might be running into authemtication / permissions issues anyway.

If you hosting company has said that CDW is the best way to go, do they have any recommendations for connecting or configuration ?

Is it possible to use the last full backup that the hosting company does (assuming it is a SQL backup command and not a hard disk style backup) ?

All the details I use to connect via SMSS and CDW were provided by the hosting company and worked perfectly for SQL 2000 and then SQL 2005 when I upgraded all my machines.  Now since putting SQL 2008 x64 on my development machine it just errors out, although nothing has changed on the live server.

I checked the Windows error log and it shows the following (attached).
Also is a grab of the error which seems to fail at the Create Package step.

If I can't get this working then I'll have to use the T-SQL approach you provided me with, but it will still annoy me that I never figured out what was wrong!

Message: ERROR : errorCode=-1073548784 description=Executing the query "sys.sp_addrolemember @rolename = N'myDbLearnerRole'..." failed with the following error: "The role 'myDbLearnerRole' does not exist 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={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}

StackTrace:
   at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
   at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: NT AUTHORITY\LOCAL SERVICE

Select allOpen in new window

Capture.PNG

The last error that you receive on OLE is because you chose the Detach / Attach method earlier on in the wizard. It requires access through a share or another way to get to the database file directly. Resolving the OLE error is easy, select "User the SQL Management Object Method" in the step named "Select the Transfer Method".

The other issue you have is harder to resolve. I encountered that too and only happens if you have custom roles in a database. Here we are in a typical chicken and egg problem: you could script the users and roles, but a role only exists as long as the database exists. When you use the Copy Database Wizard, the database, and thus the role, is deleted.

The only workaround I have for that is: copy the database locally by using backup and restore. On that second database, remove the database roles. Then proceed as you originally did.

Hi abel

>> Resolving the OLE error is easy, select "Use the SQL Management Object Method" in the step named "Select the Transfer Method".

The SMO method is selected, and always was.  The detach/attach method was only in use as a fallback plan!

>> The only workaround I have for that is: copy the database locally by using backup and restore. On that second database, remove the database roles. Then proceed as you originally did.

Right okay, I'll give that a try....

>> The only workaround I have for that is: copy the database locally by using backup and restore. On that second database, remove the database roles. Then proceed as you originally did.

No joy - same error :-(

Ummmm... if you can use backup and restore, then why arent you using that approach and forgetting the CDW altogether ???

That's a good question.

My reason for using CDW was that it gives me a secure (using VPN) and convenient way of copying the live DB to my development machine.  If I backup the DB instead, I have to find some way of being able to download it via a browser/FTP, all of which is more risky from a security perspective.

Because it used to work perfectly, I never explored alternative ways of achieving this.

Do most professional DBA's use CDW or backup/restore?  Is there a common route that people use to do this?

I'm doing exactly the same as Mark, for the same reasons. Our databases are small - 500MB to 4GB, bigger than that is a real exception and consumes much more care and time to handle them. Big DBs are always stripped down first on the source server (in a copy, of course).

Hmmm thanks for your guidance there.  That's certainly something to think about.
I'm thinking that if I can get the .NET framework to produce a passworded zip (or even encrypt the backup file) then I could potentially achieve all of the above with a single hyperlink click...

Pity my option didn't work out for you. If there aren't any roles left, why would you still receive the add_rolemember error?

That said, I went through a similar process and found out that the original (SQL 2005) database had numerous views, stored procedures and even functions that meanwhile (database is very old) became invalid: referencing wrong tables/fields. After resolving every error, I finally made Copy Database to work (from 2005 to 2008, the reverse I haven't managed to get right yet).

But, considering you used a different approach to begin with, I can only assume that your error is not related, albeit the same message.

You say you use VPN: why can't you transfer data over the VPN? I do that all the time, and it is secure. Of use SCP / SFTP.

A final consideration is to use Generate Scripts and select "With Data". But that only works well with database < 100MB or so. I used this for copying db from 2k8 to 2k5.

>> Pity my option didn't work out for you. If there aren't any roles left, why would you still receive the add_rolemember error?

Well that's the part that stumped me from the start!  The error is thrown whether I delete the DB on the destination server first, don't delete, delete roles, delete users etc etc etc.  


>> You say you use VPN: why can't you transfer data over the VPN?

I do use VPN successfully when copying 2005>2005.  Its only 2005>2008 that is failing.  I can only use VPN as that's all the host allows (VPN from fixed IP).  Anyway I don't think that's related to the problem here.


>> A final consideration is to use Generate Scripts and select "With Data". But that only works well with database < 100MB or so. I used this for copying db from 2k8 to 2k5.

Yes I think I'm down to just a couple of options now.  As you say, "With Data", and also Backup/Restore.

What absolutely baffles me is how so many DB professionals around the web have so few good things to say about the Copy Database Wizard.  I cannot believe that it continues to be included when it only works for a fraction of users who need it.

Is it possible, using T-SQL, to backup from a live server across a network to a destination machine.  For example, could I open SSMS and execute the SQL to back up my live database (located on a hosted server elsewhere) to my development machine on my business network?  Therefore, saving the problem of having to download the backup file before restoring

> whether I delete the DB on the destination server first, don't delete, delete roles, delete users etc etc etc.  

Just so that we're sure we're on the same page: I meant deleting the roles on the source server. Because that's not always possible, I suggested creating a copy on the source server (normally that easier to do) and remove any offending objects.

> I cannot believe that it continues to be included when it only works for a fraction of users who need it.                              

Same here. A similar job I had to do, where I copied the users, doesn't work when an *existing* user is to become the DBO user, whether you select to copy the logins or not. Resolution: change DBO user to a (on the target server) non-existing user and it works. Odd.

As a suggestion: you can save the task and open it in SQL Server Business Inteligence Developer Studio and set the allowed errors to a higher level and run it from there. This can have the benefit that the copying task doesn't fail when errors are encountered and that the new db is not deleted when not everything is copied. If your data is there but not the roles, this may work for you. It also makes it slightly easier to read the error message, fix it, rerun etc.

>> Just so that we're sure we're on the same page: I meant deleting the roles on the source server.

Okay sorry I thought you meant destination server.  The thing is, the role error referred to a role within the source database being copied.  If I duplicate the database on the source server and remove offending objects, they'd still exist in the original though, right?


>> Same here. A similar job I had to do, where I copied the users...

That's really surprising that CDW behaves like this.  To be honest, after reading all the input for this question I'm put off using CDW again regardless of the requirement.  I think a good old backup/restore is going to be the way forward for me now.

>> could I open SSMS and execute the SQL to back up my live database (located on a hosted server elsewhere) to my development machine on my business network

Might be possible so long as the servers can "see" each other. You can "trick" SQL Server by first setting up a backup device and then backing up to that device. It does not normally work trying to backup to another machine over a "share" and that type of thing. But if you are confident enough of the backup to a remote locations, then would still recommend local backup, then compress, then copy - the copy file should be smaller, and the less time the connection is being relied upon, then the lesser liklihood of an unexpected disconnect.

CDW is designed for the database, it is not a server replication type tool. So, aspects such as security paradigms are more server related than say isolated (and therefore portable) at the database level. Consider for example, using Windows Authentication - that is pretty much entirely dependant on how that (new) server has been set up and how AD / Security has been established. That is the main problem when using CDW - when using it as a migration tool in addition to copying data.

The backup and restore is by far (in my opinion) the single best method for copying / moving databases around...

Stupid question - CDW runs on SSIS on the source server, doesn't it? So it requires to "see" the other server from the source location, too?

Not at all a stupid question... And yep using CDW does need to see the other machine, but it is then "SQL server" to "SQL server" not server to disk...

Using backup does need a physical disk repository and there are two ways - either specify the physical name directly in the backup command, or, to specify a (logical) backup device then backup to that device. However, if that backup device is a UNC then you pretty much have to go to raw T-SQL to backup, or, specify a dump device via sp_addumpdevice and then you can refer to that when automating the backup tasks.

Now there is still a potential permissions issue... you really have to make sure that the SQL server account or SQL Agent  (ie the account used to start up the service running the backup) has been granted the "Access this computer from the network" user rights on the remote computer, and that it has Full Control permissions to the folder where the backup file is to be created, and if a file already exists, then has full ACL rights to that file (needs to potentially overwrite, remove or rename).

So, it is just a little bit different... And yes, there is still (potentially) a permission issue, but slightly different in so much as "this account" must be enabled to use "that disk" (if that made sense).

>> SQL Agent ..... has Full Control permissions to the folder where the backup file is to be created

Could that possibly be the issue from my original post?  Can you advise how I might be able to check this?

Yes it could be (well in part at least).

Need to check the SQL Server service and SQL Agent service and see what accounts are being used to start them up. Then, need to check if the other server can acknowledge those accounts. Wont really work if it is "local service" and that type of thing. Always best to create a specific account (in windows) and use that to run up the services. That way, you can use those accounts like regular users and assign various rights and permissions.

But if it is being hosted, you probably dont have that level of control. And is the reason why we really havent ventured too much down that path other than referring back to the people that host and ask them about their security between the servers.

Never found the answer to the problem in the original post, but the advice for a workaround is much appreciated - thank you :-)

FYI, I encountered the same problem as per your original post whilst using Copy Database to try to copy a database from my laptop to my PC. The laptop was running Windows 7 Home and SQL Server 2008 Developer and the PC was running Windows 7 Pro and SQL Server 2008 R2 Developer. Both machines were connected to my wireless home network. The configuration is different to yours of course but the same in terms of a source server running a lower version of SQL Server connecting via one method and a source server running a higher SQL Server version connecting via another method.

Like you, I was using SQL Server authentication on the source server and Windows authentication on the destination server. When I changed to SQL Server authentication on the destination server (e.g. sa login for both source and destination), the error disappeared, the package was built and started to execute. Unfortunately I was thwarted by another error, the description of which was "A transport-level error has occurred when receiving results from the server. The specified network name is no longer available." From reading this long series of posts I suspect problems with Copy Database are buried in the security models of the Windows and SQL Server versions being used. Perhaps if I had identical accounts running the SQL Server Agent and SSIS services on both machines it would work ok?

But like you I concluded that backup and restore is better because it's more robust and I lost patience with the troublesome Copy Database method. I'm no expert but just thought you might be interested in the bit about using SQL Server authentication on both ends in case that helps your situation.

Hi irb56

I got this error when also running from Win 7 SQL 2008 to Win 7 SQL 2008 (same on both machines).  I think to be honest the whole process is flawed.  You shouldn't need a degree in security or be an MVP to perform these simple tasks...!