0

I need to move a database from SQL Server 2008 to 2005, did the backup format changed or can I import the exported DB in the SQL Server 2005?

The database has tables, views and stored procedures, and it is not using any 2008 specific features.

If it's not possible, would setting the compatibility mode to SQL-Server 2005 (90) help in this case?

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
  • Have you tried it and received errors? – CD Smith Jun 20 '12 at 11:47
  • 1
    You won't be able to Backup a SQL 2008 R2 database and restore in SQL Server 2005. – Neil Knight Jun 20 '12 at 11:51
  • @Neil question is about 'sql server 2008' to 2005.... – Tirumudi Jun 20 '12 at 11:57
  • @Tirumudi. I'm aware of that, but its definitely something worth mentioning for others who read this question. – Neil Knight Jun 20 '12 at 11:59
  • No, you **cannot** restore a backup from 2008 on a 2005 instance, and **no**, setting the compatibility mode doesn't help either. Going "back" in time just isn't supported in SQL Server - never has been, and most likely never will be. You'll need to script out the database objects into SQL scripts and run those on your 2005 machine. – marc_s Jun 20 '12 at 12:08
  • @NeilKnight +1 for tat.. Even, i got to generate script and run when tried to export to 2008 R2 from 2008 itself.. – Tirumudi Jun 20 '12 at 12:09
  • Thank you all for answering. i just notices that even scripting is not helping a lot, datatypes like date doesnt exist in 2005, and these have to be manually changed to datetime. – CloudyMarble Jun 20 '12 at 12:28

2 Answers2

3

Select your database in SSMS, right-click, select Tasks, select Generate Scripts. In the dialog box that pops-up, enable all options, including script data and make sure you select "Script for SQL Server 2005". Execute the generated scripts on your SQL Server 2005 machine. This is workable only for a relatively small database, of course. Else, you'll have to export/import data via bcp.

David Brabant
  • 41,623
  • 16
  • 83
  • 111
  • I couldnt find the option "Script for SQL Server 2005" i tested this on Express edition. – CloudyMarble Jun 20 '12 at 12:45
  • Don't have SQLServer Express at hand, but on my installed copy (Developer Edition), that option appears in the Script Wizard "Options" pane, in the list of options you can set ("Script for Server Version") – David Brabant Jun 20 '12 at 12:55
1

You won't be able to do this - the backup file contains a version number, and SQL 2005 will refuse to restore the backup. Similarly, you won't be able to detach and reattach the raw data files.

The only option you have, short of upgrading SQL 2005 to 2008, is to export the SQL schema and sprocs using SSMS's ability to generate scripts, and then migrate the data using (for example) BCP or an SSIS package.

There will be third party tools that might be able to help with some of this (for example, a combination of Redgate's SQL Compare and SQL Data Compare). However this is something you will have to research.

Chris J
  • 30,688
  • 6
  • 69
  • 111
  • Thank you Chris, upgrading is not an oprtion in my case, and tha dtaa itself is not the problem (yet), i assume a csv files of data would be importable but the tablestructure is the issue, guess ill have to generate scripts, fix issues like datatypes and the run them on 2005, although i expect problems because of referenial integrity whcih makes the order i create teh tables critical – CloudyMarble Jun 20 '12 at 12:35