SQL2008 R2 Copy Database error "Cannot find the principal SQL2000\Administrator"
Hi,
I'm trying to copy a database from a SQL 2000 box to a SQL 2008 R2 server using SA on both servers. I am not trying to copy the logons as it's easy enough to recreate them in this case.
The copy is throwing an error:
Executing the query "EXEC dbo.sp_changedbowner @loginame = N'SQL2000\Ad..." failed with the following error: "Cannot find the principal 'SQL2000\Administrator', because it does not exist or you do not have permission."
I am not sure how that login is getting in the script as I didn't specify it anywhere.
Why is it trying to use the Administrator acct on the SQL 2000 box? How do I prevent that from happening?
Thanks!
--Ben
I'm trying to copy a database from a SQL 2000 box to a SQL 2008 R2 server using SA on both servers. I am not trying to copy the logons as it's easy enough to recreate them in this case.
The copy is throwing an error:
Executing the query "EXEC dbo.sp_changedbowner @loginame = N'SQL2000\Ad..." failed with the following error: "Cannot find the principal 'SQL2000\Administrator', because it does not exist or you do not have permission."
I am not sure how that login is getting in the script as I didn't specify it anywhere.
Why is it trying to use the Administrator acct on the SQL 2000 box? How do I prevent that from happening?
Thanks!
--Ben
TempDBA
What is the syntax\What is the way you are copying?
ASKER
I was using the Copy Database wizard using the SQL Management Object method, with ti set to drop existing destination databases. Removed Login object for transfer. Summary below:
Click Finish to perform the following actions:
Source: sql2000vm SQL Server 2000, Microsoft SQL Server Standard Edition , Build 2039, Microsoft Windows NT 5.0 (2195) NT INTEL X86
Destination: sql2008r2 Other SQL Server Version, Microsoft SQL Server Enterprise Edition (64-bit) , Build 1600, Microsoft Windows NT 6.1 (7600) NT x64
Using SMO online transfer
The following databases will be moved or copied:
Copy:aafw
Destination file will be created: d:\MSSQL\DATA\aafw.mdf
Destination file will be created: d:\MSSQL\DATA\aafw_log.ldf
Drop existing database and continue transfer
Package scheduled to run immediately
--Ben
Click Finish to perform the following actions:
Source: sql2000vm SQL Server 2000, Microsoft SQL Server Standard Edition , Build 2039, Microsoft Windows NT 5.0 (2195) NT INTEL X86
Destination: sql2008r2 Other SQL Server Version, Microsoft SQL Server Enterprise Edition (64-bit) , Build 1600, Microsoft Windows NT 6.1 (7600) NT x64
Using SMO online transfer
The following databases will be moved or copied:
Copy:aafw
Destination file will be created: d:\MSSQL\DATA\aafw.mdf
Destination file will be created: d:\MSSQL\DATA\aafw_log.ldf
Drop existing database and continue transfer
Package scheduled to run immediately
--Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried that and it choked. Then your suggestion triggered something in the back of my mind and sure enough, I'd forgotten to use the overwrite option. Worked fine.
Thanks much!
BTW, it would be nice to understand why the first option didn't work. ??
--Ben
Thanks much!
BTW, it would be nice to understand why the first option didn't work. ??
--Ben
If you created the database before the restore, it may have had a problem upgrading in the process. Not sure...just a thought.
One thing you can do is not create the database at all and let the restore create it...you probably are already aware of that.
One thing you can do is not create the database at all and let the restore create it...you probably are already aware of that.
Also note that even though the users added, it would be best to remove them, then reassign access based on the users in the new servers security list. You likely will have some authority errors if you do not do this.