Monday 18 October 2010

SQL11 (Denali): How to migrate SQL Server 2000 databases to SQL Server 2012?

Having found 'SQL Server 2000 (80)' inside the compatibility levels supported by SQL Server 2012, I need to say that the upcoming version of SQL Server is not going to support it. we do know that each new version only supports compatibility levels of two below versions, which means that it is an error as this is a CTP build.

What is going on if we try to restore a SQL Server 2000 database on SQL Server 2012? well, we will get the following error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.1103. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Attaching the database neither works.

Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Northwind’. CREATE DATABASE is aborted.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Northwind’. CREATE DATABASE is aborted.
Msg 950, Level 20, State 1, Line 1
Database ‘Northwind’ cannot be upgraded because its non-release version (0) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

Therefore, SQL Server 2000 databases have to be migrated to SQL Server 2012 following the next steps: (1) restore SQL Server 2000 databases on SQL Server 2005 or SQL Server 2008; (2) take Full Backups of the new SQL Server 2005/2008 databases, and finally; (3) restore them on SQL Server 2012. In other words, there will not be direct migration from SQL Server 2000 to SQL Server 2012. That is all for now, let me know any remarks you may have. Thanks for reading again!

No comments:

Post a Comment

Let me know any remarks or questions you may have. Please write down your name.

HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE