With several SQL Server versions in use -- 4.2, 6.0, 6.5, 7.0, 2000 and now 2005 -- you will likely need to restore databases from a previous version to a later version.
Microsoft made some of its most dramatic changes in version 7.0 in terms of how the database engine works and how data is stored. And that makes the restore path far from straightforward for SQL Server versions released prior to 7.0.
Version 7.0, 2000 and 2005 allow you to restore databases to later SQL Server versions. Anything prior to 7.0 requires a data migration, during which you must physically move data out of the older SQL Server version and into one of the newer SQL Server versions.
Since the latest version is 2005, I will just discuss how to move older versions of SQL Server to this newest DBMS release. However, these same techniques could work with 7.0 or 2000.
Restoring SQL Server versions prior to 7.0
I already mentioned there is no direct path to just back up and restore your databases for versions earlier then 7.0. To move data into SQL Server 2005, some type of data migration must be done, possibly by creating a SQL Server Integration Services (SSIS) package and setting up an ODBC source to your older database versions. Or, you could use BCP (bulk copy program) to move data out of the older version and into a SQL Server 2005 database.
There is no way to extract data directly from the backup file. So if you only have a backup copy of your database, you will need to find an older version of SQL Server or get installation disks to set up that version of SQL Server to restore the data.
Restoring SQL Server versions 7.0 and later
Simply run a database restore from your backups. This works just like any other restore you would do, either by using GUI tools or T-SQL commands. Take a look at these other articles on how to perform a restore:
As with versions prior to 7.0, you can use the data migration process to move your data from an older version of SQL Server to a newer version. Both databases must be online to take this approach.
You can also use attach and detach options to migrate your database from an older version to a newer version.
Restoring system databases
One caveat is that you can restore user databases, but you cannot restore system databases from older to newer SQL Server versions. As SQL Server improves functionally, most changes are stored in the system databases. New tables are created and existing tables are modified (the reason why you should not use the system tables directly), making it impossible to restore the system databases.
You can use the SSIS or BCP approach to migrate data or new objects you have created in these databases, but you cannot move the entire database.
Updating user and login information
One thing to keep in mind when restoring databases onto a totally different instance of SQL Server is that the user and login information will need to be updated to ensure database authentication works as planned. Refer to Restoring a database from another SQL Server to learn how.
As you can see, ever since SQL Server 7.0, Microsoft has made it pretty easy for data base administrators to migrate data from older versions of SQL Server to newer versions. The idea is to always include some backward compatibility. But how long will this functionality exist? It does exist in SQL Server 2005, but only time will tell if the next release of SQL Server, code-named Katmai, will be backward compatible.
About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
