Upgrade process from SQL Server 2000 to SQL Server 2012

Question

I have a SQL Server 2000 database that I would like to upgrade to SQL Server 2012 (running on a different server).

I don't think there is a direct upgrade path, but if there is, what is it?

My current plan is:

1. Backup SQL Server 2000 database
2. Restore on server with SQL Server 2008 installed
3. Set compatibility level to 100 (SQL Server 2008 / SQL Server 2008 R2)
4. Backup database from SQL Server 2008 server
5. Restore on server with SQL Serevr 2012 installed
6. Set compatibility level to 110 (SQL Server 2012)

Am I doing unneeded steps? Am I missing anything (like running utilities to identify usage of unsupported Transact-SQL)?

Answer 1

No, as I see it, you didn't miss anything. I have upgraded one of my databases from SQL Server 2000 to SQL Server 2012.

Another way is to upgrade the entire server – in this case you can use the upgrade wizard.

UPDATE: my upgrade process was successful, but be careful about discontinued and deprecated features in SQL Server 2008 and 2012.

Answer 2

Pay attention to columns containing blog posts, for example.

The text, ntext and image data types frequently used in SQL Server 2000, are deprecated in SQL Server 2012 (although they are still available), and should be replaced with varchar(max), nvarchar(max) and varbinary(max) data types, respectively.

Answer 3

Here's a quick query to see if any of your tables are using deprecated data types.

SELECT  sys.objects.NAME AS [table_name] ,
        sys.columns.NAME AS [column_name] ,
        sys.types.NAME AS [data_type]
FROM    ( sys.columns
          INNER JOIN sys.objects ON sys.objects.object_id = sys.columns.object_id
        ) INNER JOIN sys.types ON sys.types.system_type_id = sys.columns.system_type_id
WHERE   sys.types.name IN ( 'text', 'ntext', 'image' )