Twitter Updates

    follow me on Twitter

    Monday, December 11, 2006

    The Cowboy Way of upgrading to SQL Server 2005

    One of the scenarios I showed during my presentation on upgrading to SQL Sever 2005 was "The Cowboy Way of upgrading". Here's the situation. A SQL 2000 database containing a user called sys, cannot be upgraded to SQL Server 2005. If you run the Upgrade Advisor, you will get a warning on this show stopper. So, the best practice here is to rename the user on SQL Server 2000 and afterwards upgrade to SQL 2005.

    In "The Cowboy Way of upgrading" I restored the SQL Server 2000 database with the sys user on my SQL 2005. The restore generated an error because of the sys users and the database was marked suspect.

    Remember that is definitely not the best way of upgrading. But since quite a few people asked about the script I decided to post it. Have fun but be careful.
    Connecting to localhost\sql2005...
    15 percent processed.
    23 percent processed.
    30 percent processed.
    46 percent processed.
    53 percent processed.
    61 percent processed.
    76 percent processed.
    84 percent processed.
    92 percent processed.
    100 percent processed.
    Processed 104 pages for database 'sys_database', file 'sys_database_Data' on file 1.
    Processed 1 pages for database 'sys_database', file 'sys_database_Log' on file 1.
    Converting database 'sys_database' from version 539 to the current version 611.
    Database 'sys_database' running the upgrade step from version 539 to version 551.
    Warning: User "sys" (principal_id = 5) in database "sys_database" has been renamed
    to "_ID_0x00000005_RENAMED_FROM_sys". "sys" is a reserved user or schema name
    in this version of SQL Server.
    Warning: Database "sys_database" was marked suspect because of actions taken during
    upgrade. See errorlog or eventlog for more information.
    Use ALTER DATABASE to bring the database online. The database will come online in
    restricted_user state.
    Database 'sys_database' running the upgrade step from version 551 to version 552.
    Database 'sys_database' running the upgrade step from version 552 to version 553.
    Database 'sys_database' running the upgrade step from version 553 to version 554.
    Database 'sys_database' running the upgrade step from version 554 to version 589.
    Database 'sys_database' running the upgrade step from version 589 to version 590.
    Database 'sys_database' running the upgrade step from version 590 to version 593.
    Database 'sys_database' running the upgrade step from version 593 to version 597.
    Database 'sys_database' running the upgrade step from version 597 to version 604.
    Database 'sys_database' running the upgrade step from version 604 to version 605.
    Database 'sys_database' running the upgrade step from version 605 to version 606.
    Database 'sys_database' running the upgrade step from version 606 to version 607.
    Database 'sys_database' running the upgrade step from version 607 to version 608.
    Database 'sys_database' running the upgrade step from version 608 to version 609.
    Database 'sys_database' running the upgrade step from version 609 to version 610.
    Database 'sys_database' running the upgrade step from version 610 to version 611.
    Msg 3167, Level 16, State 3, Line 1
    RESTORE could not start database 'sys_database'.
    Msg 3167, Level 16, State 1, Line 1
    RESTORE could not start database 'sys_database'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Here's how I fixed this one. First of all I forced the database online (ALTER DATABASE sys_database SET ONLINE). SQL Server will put the database in single user. In the next step I checked the system catalogs to find out that SQL Server created a new schema called _ID_0x00000005_RENAMED_FROM_sys. My table was moved from the sys schema to this new schema.

    In the next step I created a new schema and used ALTER SCHEMA to move the table from _ID_0x00000005_RENAMED_FROM_sys to my new schema.
    CREATE SCHEMA system

    ALTER SCHEMA system
    TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

    In the last step I put the database in multi user mode.
    ALTER DATABASE sys_database
    SET MULTI_USER

    Here's the full script.
    USE master

    --Force online
    ALTER DATABASE sys_database
    SET ONLINE

    --Now in single user, show object explorer first
    USE sys_database


    SELECT name, SCHEMA_NAME(schema_id) as schema_name, *
    FROM sys.objects
    WHERE NAME = 'myTable'

    SELECT * FROM sys.schemas

    SELECT * FROM sys.database_principals

    SELECT * FROM sys.server_principals

    CREATE SCHEMA system

    ALTER SCHEMA system
    TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

    SELECT * FROM system.myTable

    INSERT INTO system.mYTable (col1) VALUES ('Frederik')

    ALTER DATABASE sys_database
    SET MULTI_USER

    No comments: