Results 1 to 8 of 8

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

  1. #1
    Join Date
    Feb 2006
    Posts
    4

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

    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..

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried backup/restore?

  3. #3
    Join Date
    Feb 2006
    Posts
    4

    Sql7 ~ Sql 2005 Db

    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)

    -

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    I didn't move db from sql7 to sql2k5, but did from sql2k to sql2k5 without issue. Possible to move to sql2k first?

  5. #5
    Join Date
    Feb 2006
    Posts
    4
    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..

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Just tested. I restored the db from sql7's backup in sql2k5, changed compatibility level to 90, and able to create diagram in SSMS.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    I think database diagram has compatibility problem, remove diagrams from SQL 7.0, backup and restore.

  8. #8
    Join Date
    Feb 2006
    Posts
    4
    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)')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •