[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL2008 R2 Copy Database error "Cannot find the principal SQL2000\Administrator"

Posted on 2011-10-09
6
Medium Priority
?
814 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Ben Conner
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 36940376
What is the syntax\What is the way you are copying?
0
 

Author Comment

by:Ben Conner
ID: 36940383
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
 
LVL 7

Accepted Solution

by:
twol earned 2000 total points
ID: 36940400
Just do a backup from the 2000 and restore to the 2008 box. That will work fine.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Ben Conner
ID: 36940429
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
 
LVL 7

Expert Comment

by:twol
ID: 36940436
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
 
LVL 7

Expert Comment

by:twol
ID: 36940439
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question