migrate sql7 dbs to sql2005 on new hardware (sprocs, tables views)

[jeffry]

I cant seem to find the documented process on moving sql7 dbs from old hardware to new sql2005 install on new hardware. I dont believe the correct thing to do is to do detach and attach on the new server. I am unable to upgrade the db compatability when using this method. Database diagrams dont upgrade properly advising me that a valid db owner has not been created and I believe that it has..

I have a db with about 75 sprocs and 225 views and I looking for the approved documented process of migrating..

Thank you..

[rmiao]

Tried backup/restore?

[jeffry]

Yes.. have since tried the following..

Tried to follow the other threads here on adding database diagram support to dbs that are restored from backups from an earlier version, and am receving different responses. Steps taken.

1. Created a sql2005 db with the same name of the DB to be restored.
2. Restored sql7 backup to sql2005 with the same name with overwrite
existing options checked. Received the following error, which goes along with
other posts I have read:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
------------------------------

3. Then proceeded to change the compatability to sql2005 (90), changed the
owner to 'sa', hit ok..

4. Receive the following error when then trying to install database diagram
support.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Invalid column name 'uvalue'.
Invalid column name 'uvalue'.
Could not find stored procedure 'dbo.sp_upgraddiagrams'.
Object is invalid. Extended properties are not permitted on
'dbo.sysdiagrams', or the object does not exist.
Object is invalid. Extended properties are not permitted on
'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL Server,
Error: 207)

-

[rmiao]

I didn't move db from sql7 to sql2k5, but did from sql2k to sql2k5 without issue. Possible to move to sql2k first?

[jeffry]

to me.. that doesnt seem like the MS documented method. I would think there are stiil some SQL7 DBs out there that are now interested in making the investment and upgrading..

[rmiao]

Just tested. I restored the db from sql7's backup in sql2k5, changed compatibility level to 90, and able to create diagram in SSMS.

[skhanal]

I think database diagram has compatibility problem, remove diagrams from SQL 7.0, backup and restore.

[jeffry]

ok I was able to resolve my own problem by doing the following:

Log on as the database owner. Then, run the following code in the database where the problem occurs.

ALTER TABLE dbo.dtproperties ADD uvalue NVARCHAR(255) NULL
IF EXISTS (SELECT * FROM dbo.dtproperties) EXEC ('UPDATE dbo.dtproperties SET uvalue = CONVERT(NVARCHAR(255), value)')