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:
Post a Comment