Deploy Source control folder to remote database

cata_sun

Hello,

Here is my scenario:
- a SQL server database saved on a SVN server using Redgate SQL source control (schema and static data)
- i would like to publish schema and the static data modifications on other databases, each database can have different versions of schema and static data - some kind of publishing from Sql Source Control folder to a live database.

Simply put, I would like to do what I do now manually with Sql Source control
- get remote databases and restore locally
- register each database with the production branch on svn
- obtain latest version, I use a filter to exclude some objetcs, for all the others accept all "theirs"
- backup databases and restore remotely

Theses operations takes time, I need a more automatic way to do it.

Is this scenario supported by SQL Comparison SDK? I already looked a bit in the documentation and in the Wiki, I found some clues but I'm not sure all is supported.

Thank you.

Mike U

Have you considered using Powershell, with the Powershell cmdlets from DLM Automation to automate the synchronization between a scripts folder and a database? You can specify custom filter files as one of the arguments to the New-DlmDatabaseRelease cmdlet (documented here).

I suggest this because you can also use Powershell to automate SQL Server backup and restore operations: see MSDN for details.

Using the DLM Automation products is probably simpler than trying to use the SDK. You can find full documentation for the entire set of automation products here, and whitepapers on database lifecycle management here.

Edited to add link to Microsoft documentation for Powershell SQL Server backup

cata_sun

Thank for your quick reply, I will look into it.

cata_sun

I managed to do it, but I still have an issue. What I do now:
- use sql compare sdk to compare and synchronize live database to a folder
I use ADO.NET to execute scripts one by one in an ADO.NET transaction
- use sql data compare sdk to compare and synchronize database to a folder
I use ADO.NET to execute data scripts one by one in an ADO.NET transaction

What I would like to do:
- compare scripts and get differences
- open an ADO.NET transaction
- synchronize script differences using ADO.NET
- compare static data and get differences - here I have a problem, the tables are locked by previous ADO.NET transaction, I would like to pass the transaction to sql data compare but I didn't find how to do it
- synchronize data differences using ADO.NET
- commit ADO.NET transactions if all good

The whole purpose of this is to have a single transaction for scripts and static data, now I have two and if static data synchronization fails, the database is not coherent anymore

Thank you