Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
No 3rd party tools necessary. SQL Server 2008 Management Studio gave us a very powerful tool to down-convert a database because they added a "Script data" option to the "Script database" wizard.
Just right-click the DB in SQL2008 SSMS, and go to Tasks, then "Generate Scripts"
Walk through the wizard, make sure to chose "True" for "Script Data" under the table/view options. Choose all objects, then run the script it creates on the 2005 server. (Please keep in mind that the script created could be massive if the original database is very large!)
Note that you can even run the wizard against a SQL2005 server to down-convert a SQL2005 database to SQL2000 (you would need the 2008 tools installed on your workstation, of course).
You could BCP the data from one SQL Server instance to another instance. That would be the fastest way to copy the data from one version to another. Depending on the volume of data, it might take a long time.
There is no direct way, that I know of, to downgrade a DB from 2008's format to 2005's unfortunately.
The way I have done this in the past (actually with older versions of SQL server, but the process will be the same) is:
It may be more efficient to just copy the data first and add all the other structure (indexes, procs, triggers, ...) after step 3. This avoids row insert ordering issues caused by constraints and triggers, and building the indexes at the end should in theory be quicker than building them as all the data is added - though if you have any clustered indexes on your tables create these before adding the data as they would not be faster to create after the fact.
Of course this all assumes that none of your objects use SQL 2008 specific features - if they do you will hopefully find out and fix such things when things error upon rebuilding the schema. If any of your code relies on officially undefined behaviour that happens to have varied between SQL Server versions you may have some much more subtle and elusive bugs to hunt down and iron out later.
I faced similar situation and used Database Publishing Wizard to script schema and data for SQL Server 2005 from SQL Server 2008 database.
You first have to script the database and make sure on the version you specify the type you want to down grade to. And to copy the data from the higher version to the lower version SQL Data compare will do you the trick for you.
protected by Paul White♦ Aug 19 '15 at 10:27
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?