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)?

  • You'll still want to do things like update statistics, but otherwise your process seems right to me. Commented Mar 30, 2012 at 14:30
  • Doesn't have to be 2008/R2 -- 2005 is supported on 2012.
    – OMG Ponies
    Commented Apr 2, 2012 at 4:34

3 Answers 3


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.

  • Is there an automated way to identify those discontinued and depreciated features?
    – automatic
    Commented Mar 30, 2012 at 13:27
  • As for 2000->2008, this way is to use the Upgrade Wizard. In your SQL Server 2000 you should run SQL Server 2008 setup and then select Planning -> Install upgrade adwizor. After install try to run it. But I didn't tryed to do it on windows 2000, maybe this method will not work.
    – Alex_L
    Commented Mar 30, 2012 at 13:32
  • 1
    You can't upgrade directly from 2000 to 2012. You will need to first upgrade to 2005/2008/2008 R2, update the compatibility level of your databases to 90 or 100, and then upgrade to 2012. Commented Mar 30, 2012 at 14:14
  • @AaronBertrand is it a must to change the compatibility level for the upgrade process? Commented Mar 30, 2012 at 15:13
  • 2
    Yes because SQL Server 2012 does not support 80 (2000) compatibility. You don't have to change it all the way up to 110, but you should only keep it in 90 or 100 if you have a good reason... Commented Mar 30, 2012 at 17:07

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.


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' )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.