what happens to data in target database when redgate sql compare syncs the two database

10raw

is there any way to retain current data and syn the structure change only in case we loose data.

Thanks

AlexYates

I'm not sure I understand exactly.

The default functionality is that SQL Source Control/Compare/SQL Change Automation will deploy the schema but not the data. So data in the target database will be safe.

Now, in some scenarios SQL Compare might not know how to do this. Table renames are the obvious example - SQL Compare has a tendency to interpret that as a drop and create - and the data gets blown away. With this in mind, make sure you are aware of any warnings about potential data loss.

To get around the data loss either use "migration scripts" in SQL Source Control or revert to a manual deployment on this occasion if SQL Compare cannot handle it. Alternatively switch to using the SQL Change Automation Visual Studio plug-in (previously known as ReadyRoll). Generally, if this is only a rare issue I'd stick with SQL Source Control, but if this is a regular problem it may make sense to switch to SQL Change Automation.

AlexYates

Are your changes in source control? If not, there probably isn't an easy way to do this through SQL Compare.

However, assuming your changes are in source control:

If using Redgate SQL Source Control, check out:
https://documentation.red-gate.com/soc5/common-tasks/working-with-migration-scripts

But this feature is a bit tricky to use.

Alternatively use the SQL Change Automation Visual Studio Plug-in (previously known as ReadyRoll). This will use a migrations driven approach to source control rather than a model driven approach, giving you more control over your deployment scripts:
https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects

For more info about state-based source control (SQL Source Control) and migration-based source control (SQL Change Automation project):
http://dlmconsultants.com/model-vs-mig

10raw

Thank you Alex, I will go through this link and try to solve it.

10raw

I am using redgate source control via git and I am wondering if I can just deploy the object change without effecting the data in target server while syncing the database.

For example, If a developer creates a table with data then I want to source control only table structure and sync it to the target database using dlm automation but I want to manully add data in the target database. I can do so for the first time but I have a doubt that if I will sync with the target database which already have some data then the sync process might delete all data in the target database.

I would like to know if it is possible to use dlm automation to syn data structure only to target database without effecting the current data in the target database and add data to new structure separately in target database itself.

Thank you Alex for the support.

10raw

Thanks Alex, I tried some scenarios and it came as exactly as you explained in the accepted answer.