Convert Sql Server 2008 to Sql Server 2005 Include Data

Question

How can I Convert the SQL Server 2008 to SQL Server 2005 I need the Data of the database and not only the structure using the generate script

Answer 1

It is a manual process regardless of the questions I posed. I ran across this solution, which runs a C# console application to downgrade. Rather elegant solution if you ask me.

Answer 2

If your server are on the same network, you can also use tools like Red-Gate SQL Compare (for the structure) and SQL Data Compare to sync up two servers and move and differences from one server to the next.

Additionally: this also work between a "live" server and a backup from a second server, so you could e.g. backup your SQL Server 2008 into a *.bak file, copy that to the 2005 server, and then compare and merge the two databases with these tools.

Highly recommended - great stuff - especially if you need to do this over and over again.

Answer 3

thanks for all I found the Solution the solution is by generate the script and export Data to Access database from the sql server 2008 after that apply the script and import the data from the access database

Answer 4

Since we cant restore the backup of SQL Server 2008 to SQL Server 2005.

The options are as follows

• Generate the database object script from SQL Server 2008 (Change the compatibility to 2005)
• Run it on SQL Server 2005
• Change the Recovery model of the newly created database(on SQL Server 2005) to Simple
• Use BCP or IMPORT/EXPORT or BULK INSERT or SSIS packages to ship the data from SQL Server 2008 to SQL Server 2005(I prefer SSIS)
• Change back to Full recovery model if it really needs for your environment

Another option is that If you are not having less records on your SQL Server 2008 Database, then You can generate all the database scripts and also Insert scripts on SQL Server 2008.what you need to do is to simply run the script on SQL Server 2005. You will have all objects with data!