I came upon a strange situation, that I had to really work with, to get it done. My website databases were all done in 2008, and backups would not work across hosts in my case (using .bak or detaching and getting .mdf files). So I went about learning to downgrade, since my new host used 2005 and 2008. The big bummer was that there was no direct publishing that could happen with 2008 servers.


It’s fairly easy once you get there, but at one time, I was really scratching my head about it. First, right click on the database and choose Tasks/Generate Scripts. Yes, this is great for scripting all the objects, but it can grab the data. In 2008, it’s more straightforward than in 2008 R2. You will need to click the ‘Advanced’ button either way, but in 2008, one of the items is ‘Script Data’ and you can directly tell it to use 2005. In 2008 R2, you must look closer – look for the attribute and not the property. Look for ‘Schema Only’. It’s a dropdown, and you will need to choose ‘Schema and data’.

If you don’t have a database already created, you can ‘Script Database’ also. I created it locally, by the way, so I could run the script on my local dev sql server.

From there, it was just a matter of publishing the database. I found out from my new host what the web service was (on their end). It was easy, because I could use it and use my already created FTP username and password. Then, I created a dummy database on their 2005 server with the same name I wanted. Finally, I set up the publishing from my local sql server to the remote 2005 sql serverl. It’s a whole lot like the import/export wizards you have built in, but with the extra publishing web service in the mix.

Once I scripted my 2008 database as a 2005 script, locally, I opened it up, and ran it on my 2005 server. After that, I ran the Publishing wizard and before I knew it, the whole thing was done. You can also run it directly on your new hosting servers, if you have external Create permissions, and you create the script with ‘Create Database’. Thsi is just the way I decided to do it, for my specific case.

If you want some more info on the scripting of the database, please go here:
http://www.sqlservermart.com/HowTo/MSSQL_Convert_2008_To_2005.aspx

Also, if you need to download the Publishing Wizard, go here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

And there, you go – you can change your 2008 databases back to 2005 databases when you change hosts