What is the suggested approach to Syncing/Backing up/Restoring from SQL Server 2008 to SQL Server 20
- by Eoin Campbell
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:
Getting the initlal DB Structure & Data into production.
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.