updating scripts from live db

toddwhitehead

I'm using sdk 8 and trying to compare previously generated scripts with a live db and then update the scripts as needed.

I've been through a sample on the wiki (http://labs.red-gate.com/index.php/Synchronizing_Schema_-_Live_Database%2C_Scripts_folder_VB) but although the comments say it compares a db and scripts folder, the code actually seems to compare two script folders. Also the variable names dont match the description and is very confusing.

Below is my attempt to convert the sample but its complaining i'm using a folder compare api when i shouldnt.

Public Sub SynchroniseScripts(ByVal SrcScriptsFolder As String)
        Using DestScripts As New Database(), MasterDB As New Database()
            ' Read the schema for the WidgetStaging database 
            MasterDB.Register(New ConnectionProperties("xxxxx\sqlexpress", "xxxxx", "sa", "xxxxxxxxx"), Options.Default)

            ' Establish the schema from the scripts stored in the WidgetProduction scripts folder 
            ' Passing in null for the database information parameter causes SQL Compare to read the 
            ' XML file supplied in the folder. 
            DestScripts.Register(SrcScriptsFolder, Nothing, Options.[Default])

            ' Compare the database against the scripts. 
            ' Comparing in this order makes the WidgetProduction scripts folder the second database 
            Dim DBDifferences As Differences = DestScripts.CompareWith(MasterDB, Options.[Default])

            ' Select all of the differences for synchronization 
            For Each difference As Difference In DBDifferences
                difference.Selected = True
            Next

            ' Use the default folder structure for any new files 
            Dim folderOptions As New WriteToFileOptions()

            ' Calculate the work to do using sensible default options 
            ' The WidgetProductionScripts folder is to be updated, so the runOnTwo parameter is true 
            Dim work As New Work()
            work.BuildFromDifferences(DBDifferences, Options.[Default], True, folderOptions)

            ' We can now access the messages and warnings 
            Form1.txtMessages.Text &= "Messages:" & vbCrLf

            For Each message As Message In work.Messages
                Form1.txtMessages.Text &= message.Text & vbCrLf
            Next

            Form1.txtMessages.Text &= "Warnings:" & vbCrLf

            For Each message As Message In work.Warnings
                Form1.txtMessages.Text &= message.Text & vbCrLf
            Next

            ' Disposing the execution block when it's not needed any more is important to ensure 
            ' that all the temporary files are cleaned up 
            Using block As ExecutionBlock = work.ExecutionBlock
                ' Display the SQL used to synchronize 
                Form1.txtMessages.Text &= "SQL to synchronize:" & vbCrLf

                ' Finally, use a BlockExecutor to run the SQL against the WidgetProduction database 
                ' BlockExecutor executor = new BlockExecutor(); 
                ' executor.ExecuteBlock(block, ".", "WidgetLive"); 
                Form1.txtMessages.Text &= block.GetString() & vbCrLf
            End Using
            ' and we can also perform the synchronization now 

            ' Retrieve the list of actions to perform to synchronize the database against the scripts folder 
            Dim scriptActions As ScriptSynchronizationActions = work.ScriptSynchronizationActions

            ' Display the files that will be modified during the synchronization 
            Form1.txtMessages.Text &= "Script files that will be modified:" & vbCrLf

            For Each action As ScriptSynchronizationAction In scriptActions.SynchronizationActions.Values
                Form1.txtMessages.Text &= action.FileName & vbCrLf
            Next

            ' Check for any read-only files 
            Try
                scriptActions.Check()
            Catch generatedExceptionName As SqlCompareException
                ' An exception here means that there are read-only files in the scripts folder. 
                ' We can continue, but they will be forcibly overwritten. For the purposes of 
                ' this demonstration we choose to replace any read-only files, so we ignore 
                ' the exception. 
            End Try
            ' Synchronize the scripts folder, overwriting any read-only files 
            scriptActions.Execute()
        End Using

    End Sub

Chris Auckland

Thanks for your post.

It seems the examples on the labs site need a little attention.

You should find the example in the SQL Compare API sample project works a lot better. You can download it from here:
http://www.red-gate.com/sqltoolkit/late ... ompare_API

I just tested this example, and it worked fine for me.

I hope this helps.