How to migrate Database from SQL Server 2000 to 2005

4 Apr 2006 10:07 AM

yogee

Hello Friends,
These are the methods I have used to migrate my database tables (I don't
want data):
1. Generate SQL Query by Right Click on Database and Generate Query from SQL
Server 2000 (selecting all options i.e. All Tables, Views etc...). Run that
SQL query in SQL Server 2005 and all tables are generated.
Problem: Database tables are imported but foreign key relationship has gone
!! :(

2. Take back up from SQL Server 2000 and restore that in SQL Server 2005.
All database tables are comming correctly with primary and foreign key
relations.
Problem: When I click on diagram I get these weird errors.
Error1: Failed to retrieve data for this request.
(Microsoft.SqlServer.SmoEnum)
Error2: An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
{Cannot execute as the database principal because the principal "dbo" does
not exist, this type of principal cannot be impersonated, or you do not have
permission. (Microsoft SQL Server, Error: 15517)}

I have also tried by giving same database name.
Please help me in removing above problems or if you know any other method
then give me.
Thanks in advance !!

-Yogee

4 Apr 2006 8:56 PM

Keith Kratochvil

I would go with option 2 and suffer through the diagrams not being of any
use.

--
Keith Kratochvil

Show quote

"yogee" <yo***@discussions.microsoft.com> wrote in message
news:892E451E-4F82-4460-B26E-06ACE299AC5B@microsoft.com...
> Hello Friends,
> These are the methods I have used to migrate my database tables (I don't
> want data):
> 1. Generate SQL Query by Right Click on Database and Generate Query from
> SQL
> Server 2000 (selecting all options i.e. All Tables, Views etc...). Run
> that
> SQL query in SQL Server 2005 and all tables are generated.
> Problem: Database tables are imported but foreign key relationship has
> gone
> !! :(
>
> 2. Take back up from SQL Server 2000 and restore that in SQL Server 2005.
> All database tables are comming correctly with primary and foreign key
> relations.
> Problem: When I click on diagram I get these weird errors.
> Error1: Failed to retrieve data for this request.
> (Microsoft.SqlServer.SmoEnum)
> Error2: An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> {Cannot execute as the database principal because the principal "dbo" does
> not exist, this type of principal cannot be impersonated, or you do not
> have
> permission. (Microsoft SQL Server, Error: 15517)}
>
> I have also tried by giving same database name.
> Please help me in removing above problems or if you know any other method
> then give me.
> Thanks in advance !!
>
> -Yogee

4 Apr 2006 9:49 PM

Erland Sommarskog

yogee (yo***@discussions.microsoft.com) writes:

> These are the methods I have used to migrate my database tables (I don't
> want data):
> 1. Generate SQL Query by Right Click on Database and Generate Query from
> SQL Server 2000 (selecting all options i.e. All Tables, Views etc...).
> Run that SQL query in SQL Server 2005 and all tables are generated.
> Problem: Database tables are imported but foreign key relationship has
> gone !! :(

Sounds strange. I ran Generate Scripts from Mgmt Studio on an database
on SQL 2000. I checked "Script all objects". I retained the default
settings, except that I changed "Continue Scripting on Error" to True, since
that database has a multi-statement UDF, and a bug prevents Mgmt Studio from
scripting such function on SQL 2000. I did not actually run the script,
but I searched the scripts, and my foreign keys were there.

> 2. Take back up from SQL Server 2000 and restore that in SQL Server 2005.
> All database tables are comming correctly with primary and foreign key
> relations.
> Problem: When I click on diagram I get these weird errors.
> Error1: Failed to retrieve data for this request.
> (Microsoft.SqlServer.SmoEnum)
> Error2: An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> {Cannot execute as the database principal because the principal "dbo" does
> not exist, this type of principal cannot be impersonated, or you do not
> have permission. (Microsoft SQL Server, Error: 15517)}

This is because when you move the database from one server to another,
dbo within the database no longer maps to an existing login on the server.
This is required for diagramming, because some of the procedures has EXECUTE
AS "dbo" in them.

As I recall, you should be able to fix this with changing the database
owner with ALTER AUTHORIZATION. (Or sp_changedbobjectowner, but why not
exercise on the new syntax.)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx