Red Gate forums :: View topic - Roll back schema sync if data sync fails?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Roll back schema sync if data sync fails?

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
StuM



Joined: 13 Sep 2012
Posts: 5

PostPosted: Wed Oct 10, 2012 4:14 pm    Post subject: Roll back schema sync if data sync fails? Reply with quote

Hi,

I'm trying to find a full proof way to handle failures in a data sync. I am trying to sync the schema but then only sync the data of certain tables/columns between two databases.

In some scenarios I am expecting the data sync to fail due to constraint errors, etc. which rolls back the sync however the schema is already synced by that point.

So, does anyone know of any way to combine the two sync actions so that a roll back will roll back schema changes also? Or a way to roll back a schema sync once it has completed?

The best option I can come up with outside of the SDK is to backup/package the database beforehand and restoring it if necessary but I want to avoid that if possible.

Another option I can consider is to perform the syncs myself after using the SDK to do the comparisons and generate the SQL then I can combine the two sets of SQL into a single transaction. I'd much rather let the API handle running the SQL though.

Thanks
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Thu Oct 11, 2012 5:21 pm    Post subject: Reply with quote

Hi,

The two processes (data and schema) run completely separately, so you can't get the schema change to just roll-back ad-hoc should the data fail.

Rather than backing up the whole DB, you could look at creating a snapshot which contains only schema. Do this as your first operation (refer to the SDK Sample "LoadAndSaveASnapshotExample.cs" for details) then, should the data sync fail, do a schema compare from Snapshot > Database.
Back to top
View user's profile Send private message
StuM



Joined: 13 Sep 2012
Posts: 5

PostPosted: Fri Oct 12, 2012 10:17 am    Post subject: Reply with quote

Hi James,

Thanks a lot for the tip, I will look into snapshots. In my case I can't snapshot just the schema because the sync would be dropping columns/tables as well as creating them so data could potentially be lost from the target database in the initial schema sync that would not be restored in the roll back.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Fri Oct 12, 2012 10:24 am    Post subject: Reply with quote

Yeah, if you're in that scenario and need to roll back the data that was dropped; then you're looking more into backup files (or maybe schema and data script folders which the SDK should be able to automate too)
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group