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
Ben ConnerCTO, SAS developerAsked:
Who is Participating?
 
twolCommented:
Just do a backup from the 2000 and restore to the 2008 box. That will work fine.
0
 
TempDBACommented:
What is the syntax\What is the way you are copying?
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ben ConnerCTO, SAS developerAuthor Commented:
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
0
 
twolCommented:
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.
0
 
twolCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.