Restore Database from SQL Server 2008 to SQL Server 2005

views:

answers:

+1 Q:

Restore Database from SQL Server 2008 to SQL Server 2005

I have created a set of tables (around 20) in SQL Server 2008 and entered around 1000 records to appropriate tables.

But the issue is that I want that same tables with all the entered data into SQL Server 2005 (SQLEXPRESS).

Obviously it won't work by taking a backup and restore it into SQL Server 2005 as it won't support backward compatibility.

Any suggestion would be appreciated....

+2 A:

If you've got Management Studio (including Express version) then you can export data from it (and target an SQL Server version). Try this:

Right-click the database you wish to export and select Tasks > Generate Scripts
Follow the wizard prompts and tick 'Export all objects'
In the 'Script Options' dialogue look through all the options and change 'Script for Server Version' to 'SQL Server 2005' and also change 'Script Data' option to 'True' (this exports the DB content as well as the schema).

You can then save the export as an SQL script in a text-file that can then be run against the database you wish to import it into.

Dan Diplo 2010-04-22 10:40:05

@Dan Thanks for your reply... But how it can handle the auto generated ID fields in each table ? And how it will resolve the foreign keys ?

Nirmal 2010-04-22 10:46:53

Consider that `Generate SQL Server Scripts Wizard` is crappy f** sh**!! It's seems that it has no changes since SQL 2000 and doesn't work in most of cases returning different errors like `The specified network name is no longer available`

abatishchev 2010-04-22 10:48:59

I think its just generating the script for table structure.. But what if I want all the inserted records also into my MSSql 2005? Thanks..

Nirmal 2010-04-22 10:53:37

@Nimal: Like I said, in the Options you need to select 'Script Data' to 'True'. @abatishchev: I've used the SQL Server scripts export wizard for many databases, including so very complex ones, and it has always worked flawlessly.

Dan Diplo 2010-04-22 11:02:59

@Dan Thanks buddy... It works for me !

Nirmal 2010-04-22 11:20:58

@Dan Diplo: You're lucky! I tried to use it several times for different databases - and every time it fails. I don't know why..

abatishchev 2010-04-22 11:24:22

ansaurus

tags:

views:

answers:

Restore Database from SQL Server 2008 to SQL Server 2005

related questions