Upgrading Older Databases

A new version of SQL Server is released every few years. This year was no different with the launch of SQL Server 2012 in March. It is important to migrate older, unsupported databases to a current version. I am going to examine 3 different techniques to upgrade the [PUBS] sample database from SQL Server 2000 to 2008 R2.

The first two techniques require the database files to be copied from the older server to the new one. Use the ALTER DATABASE command to kick off the users and set the database off-line. Then copy the files to the target server.

For simplicity sake, the MSSQL directory on the C drive will hold both the data and log files. The first technique uses the CREATE DATABASE command with the FOR ATTACH clause.

The second technique uses the sp_attach_db command to add the database to the default instance.

The third and last technique depends upon a backup from the SQL Server 2000 server. I am using the RESTORE FILELISTONLY to get details on the backup.

The restore technique requires a blank database to exist.

The RESTORE DATABASE command will overwrite the empty database and upgrade the internal structures.

With all techniques, make sure the capatibility level is set to SQL Server 2008 R2 (100). It will be at SQL Server 2000 (80) after upgrading the database.

The system databases should never be moved from an older server to a newer one. If you need objects such as jobs from msdb, script them out and install them on the target server by executing the script. In summary, keep up to date with server versions so that you are not left in the dust when support is discontinued with your software.

Related posts

Leave a Comment