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

Posted by Eoin Campbell on Stack Overflow See other posts from Stack Overflow or by Eoin Campbell
Published on 2010-03-30T15:22:17Z Indexed on 2010/03/30 15:43 UTC
Read the original article Hit count: 419

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2005