How to upgrade a MSSQL database from one version to another
Published:
Browse All Articles > How to upgrade a MSSQL database from one version to another
This is a common question, so this article will illustrate how to go about upgrading a database from one version of MS SQL Server to another.
There are two ways to upgrade the database: Detach/Attach and Backup/Restore. The way these work are explained below.
Inside Management Studio, or Enterprise Manager (for 2000) you can right click on the database and choose Detach. Alternatively you can use sp_detach_db 'database name'. What this does is to remove the definition of the database from that SQL Server and then cleanly shuts down the database so that it can be attached to another server. If the database is not cleanly shut down from the SQL Server it *may* not reattach to another server without some work. There is a caveat to this method. Once you attach it to a newer version the file itself will be changed and you will not be able go back unless you made a backup. So be careful.
Any backup of the database (full) or copy (2005 and above) can be used to restore the database into the newer version of the SQL Server. When using this method, you only have to make sure that you have the last transactions that were made in the database before you took the backup (which is beyond the scope of this article). You would initiate a Restore from that backup and when it is restored and recovered to the new SQL Server, it will automatically upgrade the database to the new version. It will not change any characteristics (database settings), it will only update the internal structure of the file to work with the new version. The caveat here is that if you put new transactions into this upgraded database and want to go back, you cannot. The same applies as applied for the attach/detach, the database has been upgraded, so you cannot take a backup of the current, newer version database and restore it to a previous version. So be careful.
If you have any version of database, from SQL 2000, SQL 2005, SQL 2008, SQL 2008R2, there are files that make up that database. There will be a minimum of 2 files, a mdf and an ldf. Literally, you can have the extension anything you want, but the short side of it is that there is at least 1 data file (mdf) and 1 log file (ldf).
These files have an internal version that tells SQL Server which version of SQL it can be attached to.
Here is how it works. You can always go up the chain to a newer version, but once the file has been attached or a backup restored to a newer version, you cannot take the file or a backup of the database from the newer version back down to the eariler version. Let me illustrate.
SQL 2000 database, 2 files, dbaduck_db.mdf and dbaduck_log.ldf. I attach these files to a SQL Server 2008 server. But I find out that I want to go back to SQL 2000 because of problems. I cannot do this because the "files" have been attached to a newer version and detaching them and trying to reattach them to a lower version will not let me.
The reason why this is so, is because the internal version is incremented and the structure changes (potentially) so the older version does not recognize that structure of the file.
The same goes for the Backup/Restore method, backups from a newer version cannot be restored to a previous version.
SQL 2008 database, 2 files, dbaduck_db.mdf and dbaduck_log.ldf. I wish to go back to SQL 2005, so I attempt to attach these files and get an error that the format is not recognized.
The last piece of upgrading a database is that there are settings like Recovery Model, Compatibility Mode, and Page Verification that will not be made for you in the upgrade. You need to right click on the database and choose Properties and change them yourself if you want them to be upgraded. The Compatibility Mode is the most common one to change, and that is simply changing 80 (SQL 2000) to 90 (SQL 2005) or 100 (SQL 2008/R2) or 110 (SQL 2012) to suit which version of SQL Server you upgraded to. The only other one I would point you to is the Page Verify. In SQL 2000 the default was Torn Page Detection. In SQL 2005 and above, the value should be changed to CHECKSUM.
You can use either Backup/Restore method or the Detach/Attach method to upgrade a database from any previous version to a newer version by following the above guidance. In normal circumstances, either will work and you will have an upgraded database. You should also pay attention to the Database Options section to ensure that you give yourself access to the new features of the newer version of the SQL Engine by updating the Compatibility Mode to the newer version of SQL Server.
There are two ways to upgrade the database: Detach/Attach and Backup/Restore. The way these work are explained below.
Detach/Attach Method
Inside Management Studio, or Enterprise Manager (for 2000) you can right click on the database and choose Detach. Alternatively you can use sp_detach_db 'database name'. What this does is to remove the definition of the database from that SQL Server and then cleanly shuts down the database so that it can be attached to another server. If the database is not cleanly shut down from the SQL Server it *may* not reattach to another server without some work. There is a caveat to this method. Once you attach it to a newer version the file itself will be changed and you will not be able go back unless you made a backup. So be careful.
Backup/Restore Method
Any backup of the database (full) or copy (2005 and above) can be used to restore the database into the newer version of the SQL Server. When using this method, you only have to make sure that you have the last transactions that were made in the database before you took the backup (which is beyond the scope of this article). You would initiate a Restore from that backup and when it is restored and recovered to the new SQL Server, it will automatically upgrade the database to the new version. It will not change any characteristics (database settings), it will only update the internal structure of the file to work with the new version. The caveat here is that if you put new transactions into this upgraded database and want to go back, you cannot. The same applies as applied for the attach/detach, the database has been upgraded, so you cannot take a backup of the current, newer version database and restore it to a previous version. So be careful.
How do I upgrade a database
If you have any version of database, from SQL 2000, SQL 2005, SQL 2008, SQL 2008R2, there are files that make up that database. There will be a minimum of 2 files, a mdf and an ldf. Literally, you can have the extension anything you want, but the short side of it is that there is at least 1 data file (mdf) and 1 log file (ldf).
These files have an internal version that tells SQL Server which version of SQL it can be attached to.
Here is how it works. You can always go up the chain to a newer version, but once the file has been attached or a backup restored to a newer version, you cannot take the file or a backup of the database from the newer version back down to the eariler version. Let me illustrate.
SQL 2000 database, 2 files, dbaduck_db.mdf and dbaduck_log.ldf. I attach these files to a SQL Server 2008 server. But I find out that I want to go back to SQL 2000 because of problems. I cannot do this because the "files" have been attached to a newer version and detaching them and trying to reattach them to a lower version will not let me.
The reason why this is so, is because the internal version is incremented and the structure changes (potentially) so the older version does not recognize that structure of the file.
The same goes for the Backup/Restore method, backups from a newer version cannot be restored to a previous version.
SQL 2008 database, 2 files, dbaduck_db.mdf and dbaduck_log.ldf. I wish to go back to SQL 2005, so I attempt to attach these files and get an error that the format is not recognized.
Database Options
The last piece of upgrading a database is that there are settings like Recovery Model, Compatibility Mode, and Page Verification that will not be made for you in the upgrade. You need to right click on the database and choose Properties and change them yourself if you want them to be upgraded. The Compatibility Mode is the most common one to change, and that is simply changing 80 (SQL 2000) to 90 (SQL 2005) or 100 (SQL 2008/R2) or 110 (SQL 2012) to suit which version of SQL Server you upgraded to. The only other one I would point you to is the Page Verify. In SQL 2000 the default was Torn Page Detection. In SQL 2005 and above, the value should be changed to CHECKSUM.
Summary
You can use either Backup/Restore method or the Detach/Attach method to upgrade a database from any previous version to a newer version by following the above guidance. In normal circumstances, either will work and you will have an upgraded database. You should also pay attention to the Database Options section to ensure that you give yourself access to the new features of the newer version of the SQL Engine by updating the Compatibility Mode to the newer version of SQL Server.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)