Wednesday, November 6, 2013

Upgrade SQL Server 2000 Database to SQL Server 2012

Even though we are now into SQL Server 2012, 2008R2 etc, but still many organizations have their SQL Server running on 2000 and may want to Move some databases to SQL Server 2012.

This may seem a bit normal, yes but there is a little Trick.

When you Try to Take a Backup of a Database which is running on SQL Server 2000 version and try to Restore on to SQL Server 2012 Version you may encounter below error.

The database was backed up on a server running version 8.XX.XXXX. That version is incompatible
 with this server, which is running version 11.XX.XXXX. Either restore the 
database on a server that supports the backup, or use a backup that is compatible with this server.

This is not allowed as compatibility 80 is not supported on SQL Server 2012

But its not the end.
A quick solution to this is,

1. Backup the SQL Server 2000 Database ex. DB2000
2. Restore the SQL Server 2000 Database DB2000 on SQL Server 2005, 2008 or 2008R2 Server DB200X(X can be 5,8,8R2)
3. Backup the Newly Restored SQL Server DB200X(X can be 5,8,8R2) database
4. Now Restore the DB200X(X can be 5,8,8R2) database on SQL Server 2012.

Note: Any deprecated feature existing on the database may not work.

This works. But its always good to run Upgrade Advisor as this will highlight any Deprecated Features.

No comments:

Post a Comment