Thursday, February 26, 2009

Back porting SQL Server 2005 to 2000

Background

We had a SQL Server 2005 DotNetNuke database full of proprietary data that we needed to back-port to SQL Server 2000, due to a limitation on the eventual destination hosting site.


The Problem

SQL Server 2000 will not natively restore databases from backups made on SQL Server 2005, and will not attach SQL Server 2005 data/log files. So we needed to find an alternative way to move our data and schema objects from the SQL 2005 instance back to 2000. Something like Redgate, but perhaps a little cheaper and simpler if possible.


The Solution

Microsoft offers a free download called the SQL Server 2005 Database Publishing Wizard. This tool presents a wizard-style interface that allows you to script out your database in its entirety (all data and objects) into a single SQL script. This script can be targeted at either SQL Server 2000 or 2005.

With just a few mouse clicks we had generated a single SQL script that contained our entire database. The script ran up perfectly first time under SQL Server 2000, giving us the desired result of our DotNetNuke database being back-ported successfully to SQL Server 2000.

The tool can be found at:

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en