I have SQL Server 2008 Developer Edition on my laptop and on this instance I have a database named "MI". This database was originally installed on a production server running SQL Server 2000 and I successfully copied the database to my laptop using backup and restore. In recent weeks, the SQL Server 2000 instance has been upgraded to 2008 R2. As a consequence of this I am no longer able to use the simple backup and restore method to get a copy of the MI database into my development environment. The following error message occurs:
Msg 3169, Level 16, State 1, Line 2
The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
I realise that the problem is that the backup file is produced by a SQL Server 2008 R2 instance and I'm trying to restore on a 2008 instance. This would appear to close off my option of the backup/restore method. I've seen the suggested solution of using the Generate and Publish Scripts wizard on the 2008 R2 instance to produce a SQL script file that I can execute on my 2008 instance. I am hesitating to do this because I want all the data from the tables of the MI database too and can't see how the scripting wizard will do anything other than result in a bunch of empty tables.
Essentially I just want an up to date copy of the MI database on my laptop. This was very easy before but now it seems hard unless I upgrade my laptop to 2008 R2. Any advice on the most efficient workaround for this issue will be most welcome.