Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
You'll still want to do things like update statistics, but otherwise your process seems right to me. –  Aaron Bertrand Mar 30 '12 at 14:30
    
Doesn't have to be 2008/R2 -- 2005 is supported on 2012. –  OMG Ponies Apr 2 '12 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.

share|improve this answer
    
Is there an automated way to identify those discontinued and depreciated features? –  automatic Mar 30 '12 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 Mar 30 '12 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. –  Aaron Bertrand Mar 30 '12 at 14:14
    
@AaronBertrand is it a must to change the compatibility level for the upgrade process? –  StanleyJohns Mar 30 '12 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... –  Aaron Bertrand Mar 30 '12 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.

share|improve this answer

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' )
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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