Copy Database Problem

irb56
irb56 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The simplest work around I can think of is to

0) stop the SQL Server in your source environment

1) go to the data directory under SQL Server in your source environment

2) copy the DB MDF & LDF files the destination env (your laptop)

3) in case that source and destination has same names , backup your DB first (you will have to stop here the SQL Server services as well)

* after that you will be able to start teh SQL Server services on your laptop and see the data
Adam MenkesC# ASP.NET Developer
Top Expert 2010
Commented:
You might try the SQL Server Import and Export Wizard to move from one server to another, if you can connect them via the network.
SQL Expert/Infrastructure Architect
Commented:
elimesika:
Copy of ldf and mdb files will fail since the target is a previous version of sql.

amenkes way would work, in the same spirit you could create a replication etc.

But... The simplest way is to generate script using SSMS 2008, there you have the option to include DATA. This will generate the script for creation of the database and all inserts thats needed. It works if it's not to huge, otherwise it still works but takes a long long time.

Regards Marten

Author

Commented:
Thanks all. If I had network connectivity I would probably go for the Copy Database wizard as suggested by amenkes but as I don't the best solution appears to be the scripting suggested by Marten. As Marten said this isn't the most efficient when there's a lot of data. I started doing this and the SQL script file got up to over 3.5GB before I cancelled it. I will need to be more selective with the data (as most of it isn't needed) and create two scripts I think, one schema only and one data only that is selective with the table objects needed.

In case anyone reading this finds it useful, the scripting option in 2008 R2 is done as follows:
1) Right click DB in SSMS and choose Tasks>Generate Scripts (starts the wizard)
2) Choose whether to script entire database or selected objects
3) On the Scripting Options page, click Advanced button and set Script For Server Version to match the destination server (SQL Server 2008 in my case), and Types Of Data To Script to Schema Only or Data Only (or a combination)
4) Save to file (this file can then be run on destination server)

I suppose that other solutions worth mentioning are to upgrade my local SQL Server 2008 Developer Edition to 2008 R2 (worth mentioning because the Developer Edition doesn't cost much and the backup/restore method is very easy), or alternatively use a third party backup utility for the backup and restore.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
FYI, the Express edition (2008 R2) is free. Are there features you need on your laptop with the Developer? If so, check out http://www.microsoft.com/bizspark/

Get a lot of software free for 3 years, then it is $100.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial