Re: How to migrate Database from SQL Server 2000 to 2005
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 4 Apr 2006 21:49:08 +0000 (UTC)
yogee (yogee@xxxxxxxxxxxxxxxxxxxxxxxxx) 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, esquel@xxxxxxxxxxxxx
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
.
- Prev by Date: Re: <<< Connection Problem with SQL Server + C#.Net on Local Area Network >>>
- Next by Date: installing sql2005 standard problems.
- Previous by thread: Re: How to migrate Database from SQL Server 2000 to 2005
- Next by thread: Re: <<< Connection Problem with SQL Server + C#.Net on Local Area Network >>>
- Index(es):
Relevant Pages
|
