asked
database revert back from sql server 2008 to sql server 2005
hi team, as we have upgraded from sql server 2005 to sql server 2008.there was a need from my client to have database in sql server 2005, then i have generated script for the database and it was working fine,but now he placed me a request to convert a database from 2008 to 2005, problem is the db size is 6GB,when script generated its size increased upto 8 GB, my issue is how to execute the query now.
The only way to go back a version is to export the data from SQL 2008 and import it into SQL 2005. You say that the DB is 6GB and the script is 8GB. Did you export the data as well as the structure? This could account for the size difference. You have a few possible routes to do this. As it is only 8GB, have you thought about using replication? You could setup a snapshot replication to do all the work for you. 1. Setup a SQL 2005 box 2. Setup replication distribution on the SQL 2008 box 3. Setup snapshot replication on SQL 2008 box (include all indexes, permissions etc.) 4. Setup push subscription on SQL 2008 box to SQL 2005 box 5. Run snapshot agent This will export all the data, create the objects with correct permissions, FKs etc on target system. You then only have to go through to ensure the objects are as you need them. The hard work of exporting/importing is done for you. Another option is to export create scripts, run those on the 2005 box, then import the data using a linked server/SSIS/bcp. It basically does the same thing as replication, but you are doing most of the work by hand.
+1 You were to quick for me and your solution is excellent. :) I will keep that in mind IF I ever need to revert a database. I hope i never will revert to an older version that is about to pass the mainstream support period.
Can you specify your problem a little bit more? Don't you have enough space for your database? What is stopping you from executing the script? What does the script contain, only DDL statments (CREATE, DROP etc.), or do you also have DML statements (INSERT, DELETE etc.)? As you probably already noticed, it is not possible to use a SQL 2008 backup and restore in SQL 2005. Personally, I would use Red-gate compare to create the SQL 2005 database and then use SSIS or OPENQUERY with a linked server to transfer any data. If the you have problems with the database size, I would increase the diskspace and not decrease the database size. If you shrink the database, the indexes will be fragmented and you need to defragment them and that may increase the database size again if you don't use SORT_IN_TEMPDB.
Pass it in as a command-line parameter to SQLCmd (or, for older versions, oSQL)?
How is it possible to create a file that is 6GB in size? :) Have you scripted all the data from the database? I wouldn't recommend that solution, use SSIS or openquery over a linked server instead. If it is only "create scripts", I would recommend you to download Red-gate compare to syncronize the SQL2008 and the SQL2005 database.
I agree with the Håkan Winther. Generate the Script of all objects from your sql Server 2008, execute that script on your sql server 2005 to create new database. change the database recovery model to simple Use bcp or SSIS or import/export to transfer the data from SQL Server 10 to SQL 9 Change back to full recovery
