What is the suggested approach to Syncing/Backing up/Restoring from SQL Server 2008 to SQL Server 2005

Question

I only have SQL Server 2008 (Dev Edition) on my development machine

I only have SQL Server 2005 available with my hosting company (and I don't have direct connection access to this database)

I'm just wondering what the best approach is for:

1. Getting the initlal DB Structure & Data into production.
2. And keeping any structural changes/data changes in sync in future.

As far as I can see...

• Replication - not an option cos I can't connect to the production DB.

• Restoring a backup - not an option because as far as I can see, you cannot export a DB from 2008 that is restorable in 2005 (even with the 2008 DB set in 2005 compatibility mode) and it wouldn't make sense to be restoring production over the top of my dev version anyway.

• Dump all the scripts from my 2008 Database, Revert my Dev to machine from 2008 -> 2005, and recreate the database from the scripts, then just use backup & restore to get the initial DB into production, then run scripts through the web panel from that point onwards

• Dump all the scripts from my 2008 Database and generate the entire 2005 db from scripts in production. then run scripts through the web panel from that point onwards

• With the last 2 options, I'd probably need to script all the data inserts as well using some tool (which I presume exists on the web)

Are there any other possibile solutions that I'm not considering.

Answer 1

That is because your development deliverable is a database MDF instead of script sources. the battle is already lost. Besides the deployment initial engine version difference, you'll have more problems as soon as you try to deploy your first update of the application. You will find yourself trying to apply a delta of the schema into production, not an easy feats. You'd probably end up using tools like SQL Compare or something similar. And this is even now, when you have 1 (one) developer and 1 (one) production server. As you add more developer or more deployments into the mix, the things just get worse and worse.

But there are better ways.

One way is to use a source based development tool like Visual Studio Database Edition. The deliverable of a VSDB project is a .dbschema that can be deployed as a diff delta into production using the vsdbcmd tool.

An even better way is to version your schema and deploy everything through upgrade scripts from v. N to v. N+1. See Version Control and Your Database.

Both approaches provide, besides a manageable deployment path, source control version of your database schema changes.

It is true though that even with a script based approach you can end up with 2008 features in your script and have a surprise at deployment time, a tool like VSDB can actually catch these during build/compile. Even if you do make a mistake, it is easy to recover by modifying the scripts, as opposed to starting from scratch on a new empty database of the proper downgraded version.

Answer 2

The safest option would be to revert your development environment to SQL 2005, as that way no matter what your code will be compatible with your hosting environment. You should be able to install a separate 2005 instance on your box, which should save time (have only one of 2005/2008 active at a time for performance reasons). To get this configured, you might have to uninstall 2008, then install a 2005 instance, then install a 2008 instance.

With regards to data, you might want to look into the BCP utility for copying data in and out of the database. Once you get the hang of it, it is pretty quick and convenient.

Answer 3

You can run both 2005 and 2008 instances (and multiple instances of each one, actually) on your development machine (as you mentioned, you can also run databases in 2005 compatibility mode in 2008, which is really only useful for disabling syntax features). I would recommend this, since you said you have the ability to revert your installation to 2005 - just run both side by side.

So then you could backup and restore from your hosting to your dev machine and then use any number of database comparison tools (Red Gate SQL Compare or ApexSQLDiff, say) to compare the 2005 and 2008 database on your dev machine and generating change scripts to forst test in dev and then apply to production.