Cannot Copy Database Error: "Cannot find the principal"
We're trying to upgrade a couple of SQL 2000 Databases and we're getting errors when using the Copy Database Wizard.
We've tried logging in as SA and also as a Domain Admin and get the same error when copying. Sometimes when we get the error, the table is actually copied, sometimes it's not.
I'd some help figuring out how to get these database copied to 2005.
We've tried logging in as SA and also as a Domain Admin and get the same error when copying. Sometimes when we get the error, the table is actually copied, sometimes it's not.
I'd some help figuring out how to get these database copied to 2005.
OnError,INBAVEY,NT AUTHORITY\SYSTEM,SQL1_SQL2K5_Transfer Objects Task,{6C0F9D1E-9737-43CD-9FE8-86A905C6FFBF},{2464B520-B7B1-4FD4-BF0B-D18FE8A582EF},2/7/2008 2:39:12 PM,2/7/2008 2:39:12 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_changedbowner @loginame = N'Domain\Admin', @map = false
" failed with the following error: "Cannot find the principal 'Domain\Admin', because it does not exist or you do not have permission.". 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}
ASKER
I totally understand what you're saying, but I do have one concern. If we use the detach/attach method is the underlying database actually getting converted from SQL 2000 to SQL 2005 native format? I'm wondering if this is something that's automatically done, or if the data just becomes readable and part of 2005, but it's not completely converted.
Is there any documentation regarding the use of attach/detach methods for upgrading from 2k to 2k5?
We have a manager here that's questioning the attach/detach method as to whether it's actually converting the database format, etc.
Any insight is appreciated.
Is there any documentation regarding the use of attach/detach methods for upgrading from 2k to 2k5?
We have a manager here that's questioning the attach/detach method as to whether it's actually converting the database format, etc.
Any insight is appreciated.
There are minor differences between SQL2K and SQL2K5 in the 8.0 compatibility mode. SQL2K5 takes care of upgrading it to its SQL2K 8.0 version when the DB is attached. Note that you can never take those same mdf/ldf files back to a SQL2K server -- same with backup/restore.
If after it is attached it is changed to 9.0 compatibility mode, client apps that expect to see an 8.0 DB will choke. We have an application that chokes if we set it's DB to the 9.0 mode. But it is a simple QA ALTER DATABASE or right-click properties to change it.
The biggest thing I've seen in differences of the compat mode is some SQL-92 or other ISO or M$ T-SQL commands appear to be deprecated and no longer work.
The best bet is to Detach, copy (not move) the files and then attach. That way you have a way to step back to the old server if you have to.
If after it is attached it is changed to 9.0 compatibility mode, client apps that expect to see an 8.0 DB will choke. We have an application that chokes if we set it's DB to the 9.0 mode. But it is a simple QA ALTER DATABASE or right-click properties to change it.
The biggest thing I've seen in differences of the compat mode is some SQL-92 or other ISO or M$ T-SQL commands appear to be deprecated and no longer work.
The best bet is to Detach, copy (not move) the files and then attach. That way you have a way to step back to the old server if you have to.
ASKER
We're actually moving the SQL2K data to an entirely new sever running SQL2K5, and we'll be upgrading our applications to work with native 2005 datatbases. So our primary concern is that we convert from 2000 and end up with a native 2005 database (not one in compatibility mode)
Originally we tried to just run the conversion during install to upgrade from 2000 to 2005, but on 2000, we're coming from Enterprise Edition and on 2005, we're installing Standard Edition. The install conversion process bombs out and prevents us from upgrading to a lessor edition of SQL Server.
So now we're just trying to figure out a sequence of steps to natively copy and convert our 2000 DBs to native 2005.
Once the DBs are converted, we'll do application upgrade, but we need to be on native 2005 tables and databases first.
Having said all of that, I'm not sure we can get there from simply copying, and attaching a 2000 DB. Or am I missing something.
It seems there should be an easy way to just call some migration or upgrade procedures to pull in and completely convert a database to 2005 from 2000.
Hopefully that provides a bit more background as to what we're trying to do. Let me know if you have any thoughts.
Thanks,
Bryant
Originally we tried to just run the conversion during install to upgrade from 2000 to 2005, but on 2000, we're coming from Enterprise Edition and on 2005, we're installing Standard Edition. The install conversion process bombs out and prevents us from upgrading to a lessor edition of SQL Server.
So now we're just trying to figure out a sequence of steps to natively copy and convert our 2000 DBs to native 2005.
Once the DBs are converted, we'll do application upgrade, but we need to be on native 2005 tables and databases first.
Having said all of that, I'm not sure we can get there from simply copying, and attaching a 2000 DB. Or am I missing something.
It seems there should be an easy way to just call some migration or upgrade procedures to pull in and completely convert a database to 2005 from 2000.
Hopefully that provides a bit more background as to what we're trying to do. Let me know if you have any thoughts.
Thanks,
Bryant
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to be of assistance. May all your days get brighter and brighter.
My suggestion is to either do a backup and restore or detach/attach methods. Either works fairly quickly and easily.
To move users, use the EXEC sp_help_revlogin SP from http://support.microsoft.com/kb/918992/