Error while restoring SQL Server 2000 database backup onto 2008 database.

Normally, you cannot restore SQL Server 2000 database backup onto SQL Server 2008 database. This may be due to different collation technique or some other properties. If you try to restore 2000 database backup to 2008 database, you usually get the following error.

“System.Data.SqlClient.SqlError : The backup set  holds a backup of a database other than the existing ‘<database name>’ database. (Microsoft.SqlServer.Smo)”

The snapshot of error is shown below.

To avoid this error, you will have to restore backup (2000) as new database in SQL Server 2008 from SQL Server 2008 Management Studio. For this, Go to Object Browser of management studio. Then Right click the ‘Databases’, select ‘Restore Database’. Give a new name for the database that is going to be created. Then, select the backup file and restore. This will add a new database created from backup with the name you just typed in. If the name already exists, it will try to overwrite and backup will fail.

However, there are other many techniques to migrate database from SQL Server 2000 to SQL Server 2008.

1) Detach and Attach – You can detach database files from SQL Server 2000 and attach them to SQL Server 2008. However, detach and attach is not recommended method by experts due to chance of breaking data integrity.

2) Copy Database Wizard – It helps to copy database from selected source to destination easily.