SQL Server 2000 to SQL Server 2008: possible to restore a 2000 database to a 2008 schema?

Question

Let's say we have two SQL Server 2000 databases:

           PARTS
           CUSTOMERS

Can they be restored to SQL Server 2008 into one combined database but separated using schemas, so there would be a PARTS schema and a CUSTOMERS schema? Can a 2000 database be restored "into a 2008 schema"?

Answer 1

You cannot restore two databases into a single database. You'll need to restore each database individually and then combine them manually. Or, you can create a T-SQL script of each database, combine the two scripts appropriately and then execute the script.

Answer 2

I don't think you can achieve what you're after without manipulating the restored database on the 2008 instance leveraging schemas.

When you back up the source database (in your case two), it is contained it will be restored as is on the destination, that's the main point, avoid any discrepancies between copies. Therefore, by definition, it shouldn't allow you to have a restore result which is structurally different the backed up database.

A way to achieve this would be to handle the backup/restore process first and then write a script that takes the restored databases and combine them to the actual structure you wish to have.

I hope this was helpful.

Answer 3

Q: Is it possible to restore a 2000 database to a 2008 schema?

A: Yes. If you have a .bak backup file, you should be able to simply "restore".

Here's a link of somebody who had problems doing a similar restore, and how they were resolved:

• http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/ce5e84f9-95a2-4c8a-80ab-abaf3e261afc

You can absolutely do a "restore with replace".

Once you've restored the old MSSQL 2000 backup (e.g. into a temp database), you can do a "select into" or whatever you want to copy and/or merge the data into a different database (perhaps with a different schema).

As far as "backup" files are concerned, there are relatively few issues with using an MSSQL 2000 backup in an MSSQL 2008 environment - compatibility is pretty good.

IMHO .. PSM