Comparing backup file with live db: no TableDifferences!

Bastiaan Molsbeck

Hi,

I would like to ask help about a weird issue I am having when trying to compare a database backup file with a live database.

First a little introduction is needed, to explain the method I am using:
I need to get the migration SQL script per table, not for the entire database at once.
To accomplish this, I am using the by your development team suggested method:
"The correct way to do the thing they are trying to do is probably to unmap everything but the table they want to generate the script for, generate the whole script for the database with only that table mapped, then change the mappings and generate another whole-database script for the next table like that."
See for the whole story: http://www.red-gate.com/messageboard/vi ... hp?t=16372

Now, when using this method to compare a live database with another live database, this works perfectly.
But when using a backup file as source, the TableDifferences property of the ComparisonSession is always "0" in length!

Here is a part of my code:

            'initialize source database from backup
            Dim objSourceBackup As New SQLCompare.Engine.ReadFromBackup.BackupDatabaseSource()
            objSourceBackup.Files.Add(strBackupFilename)
            Using objSourceDB As New SQLCompare.Engine.ReadFromBackup.BackupSetDatabase()
                objSourceDB.RegisterForDataCompare(objSourceBackup.ToConnectionProperties(), SQLCompare.Engine.Options.Default)

                'initialize target database
                Dim objTargetConnectionProperties As New SQLCompare.Engine.ConnectionProperties()
                objTargetConnectionProperties.ServerName = Configuration.SQLServer_Target
                objTargetConnectionProperties.DatabaseName = objDatabase.Target
                If Configuration.TrustedConnection_Target Then
                    objTargetConnectionProperties.IntegratedSecurity = True
                Else
                    objTargetConnectionProperties.IntegratedSecurity = False
                    objTargetConnectionProperties.UserName = Configuration.UserName_Target
                    objTargetConnectionProperties.Password = Configuration.Password_Target
                End If
                Using objTargetDB As New SQLCompare.Engine.Database()
                    objTargetDB.RegisterForDataCompare(objTargetConnectionProperties, SQLCompare.Engine.Options.Default)

                    ...

                    'create mappings between source and target database
                    Dim objSchemaMappings As New SQLDataCompare.Engine.SchemaMappings()
                    objSchemaMappings.Options = objOptions
                    objSchemaMappings.CreateMappings(objSourceDB, objTargetDB)

                    'loop through table mappings
                    For Each objTableMapping As SQLDataCompare.Engine.TableMapping In objSchemaMappings.TableMappings

                        'create new schema mappings object, but only for the current table
                        Dim objMappings As New SQLDataCompare.Engine.SchemaMappings(objSchemaMappings)
                        objMappings.TableMappings.Clear()
                        objMappings.TableMappings.Add(objTableMapping)

                        'compare databases (actually, just one table)
                        Using objComparisonSession As New SQLDataCompare.Engine.ComparisonSession()
                            objComparisonSession.Options = objOptions
                            objComparisonSession.CompareDatabases(objSourceDB, objTargetDB, objMappings)

'Now, at this point the "objComparisonSession.TableDifferences.Count" is always "0", regardless whether the two tables have differences!!!

I would like to know whether this is a bug or a limitation in the SQLDataCompare Engine.

PS: when comparing all tables at once, the TableDifferences is correctly filled, but I need the migration script per table...

PSS: I am using version 10.0.1.69 of the RedGate.SQLDataCompare.Engine.dll, but I also tried using version 10.2.4.113 and the problem still occurs.

Regards,
Bastiaan Molsbeck.

Brian Donahue

Sorry to say, I can't reproduce your issue. The tablemappings count is "1" at the end of the code. I think it must be something in your code. If you want to send the whole project, I can have a look, but this kind of consultancy can take some time.

I had to clean up your example to put it into a VB console app, so this is what I used:

Imports RedGate
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine

Module Module1

    Sub Main()
        'initialize source database from backup 
        Dim objSourceBackup As New SQLCompare.Engine.ReadFromBackup.BackupDatabaseSource()
        objSourceBackup.Files.Add("c:\AWBackup.sqb")
        Dim objSourceDB As New SQLCompare.Engine.ReadFromBackup.BackupSetDatabase()

        objSourceDB.RegisterForDataCompare(objSourceBackup.ToConnectionProperties(), SQLCompare.Engine.Options.Default)

        'initialize target database 
        Dim objTargetConnectionProperties As New SQLCompare.Engine.ConnectionProperties()
        objTargetConnectionProperties.ServerName = "PS-BRIAND\SQL2008R2"
        objTargetConnectionProperties.DatabaseName = "74879"
        'If Configuration.TrustedConnection_Target Then
        objTargetConnectionProperties.IntegratedSecurity = True
        ' Else
        ' objTargetConnectionProperties.IntegratedSecurity = False
        ' objTargetConnectionProperties.UserName = Configuration.UserName_Target
        '  objTargetConnectionProperties.Password = Configuration.Password_Target
        ' End If
        Dim objTargetDB As New SQLCompare.Engine.Database()


        objTargetDB.RegisterForDataCompare(objTargetConnectionProperties, SQLCompare.Engine.Options.Default)

        'create mappings between source and target database 
        Dim objOptions As New EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, SqlOptions.Default)
        Dim objSchemaMappings As New SQLDataCompare.Engine.SchemaMappings()
        objSchemaMappings.Options = objOptions
        objSchemaMappings.CreateMappings(objSourceDB, objTargetDB)
        Dim objMappings As New SQLDataCompare.Engine.SchemaMappings(objSchemaMappings)
        'loop through table mappings 
        For Each objTableMapping As SQLDataCompare.Engine.TableMapping In objSchemaMappings.TableMappings

            'create new schema mappings object, but only for the current table 
            objMappings.TableMappings.Clear()
            objMappings.TableMappings.Add(objTableMapping)
        Next
        'compare databases (actually, just one table) 
        Using objComparisonSession As New ComparisonSession()
            objComparisonSession.Options = objOptions
            objComparisonSession.CompareDatabases(objSourceDB, objTargetDB, objMappings)
        End Using
        'Now, at this point the "objComparisonSession.TableDifferences.Count" is always "0", regardless whether the two tables have differences!!! 

    End Sub

End Module

Version of SDK used was this one: ftp://support.red-gate.com/patches/SQL_ ... .0.111.zip

Bastiaan Molsbeck

Thank you for your reply.

I see in your code that you use a *.sqb file as backup source.
What type of file is that?
I am using a back-up file which is generated by SQL Server when executing the following query on the database:

BACKUP DATABASE MyDatabase TO DISK = 'c:\mydatabase.bak' WITH CHECKSUM

If the file you are using is different, could you please test it using the same type of file I am using?
Or should I change my code to generate a *.sqb file, and how do I do that?

Thank you for your reply.

Regards,
Bastiaan.

Brian Donahue

SQB Files are SQL Backup files. The Data Compare/SQL Compare engines can compare from native SQL Server and Red Gate SQL backup files. Functionally there is no difference. You could put a .bak file where I put the .sqb.

Bastiaan Molsbeck

Hi Brian,

I did some further investigation, and found out why you couldn't reproduce my problem.
There are two reasons for it:

1. Your code structure is not exactly the same as my code: when you move the last statement "Next" lower, to after the "End Using" (so the "Using objComparisonSession" stuff is done within the loop), the problem does occur. I tried it using your code in a new console app.

2. The problem does not occur for the first table in the database, but for all other tables.
Could it be that your test database has only one table? Or more tables, but only the first has differences?

Even when I use my original code, the first table is compared correctly, but all subsequent tables the "TableDifferences" count is 0.
The same goes for your code (after moving the "Next" statement).

First I did not notice this, because my first table did not have any differences between source and target. But after modifying the contents of this table, I found out that it did work for the first table.

Could you now reproduce my issue, after reading this?
(By changing your code like I stated in point 1, and testing a database with more than one table)

Kind regards,
Bastiaan.

Brian Donahue

Hello,

I tested against a real database. I can't work out what it is you are trying to do. The way the loop is set up, you are only ever going to have one table mapping: whichever was the last one to go through the loop (because you are clearing all mappings and adding one). I had to use a lot of interpretation, as the example originally posted did not compile.

If you want us to try to fix this, I think we are going to need all of the code and the databases and book a few days consultancy time to try to sort it out.

Bastiaan Molsbeck

Hi Brian,

What I am trying to do is retrieving the migration script to synchronize the database PER TABLE.
And because of an earlier reported issue (http://www.red-gate.com/messageboard/vi ... hp?t=16372) I use the by your development team suggested method to loop through the TableMappings and compare the database with only one table mapping at a time.

Using the loop I suggested (moving the "Next" to a lower location), the objComparisonSession is done per table mapping. And because the objMappings object is created each time in the loop for the current table mapping, the comparsion is done each time for the next table per iteration.

When I set a breakpoint on the "CompareDatabases" call and step throug it, I notice that only the first time the "objComparisonSession.TableDifferences.Count" is larger than zero. For all next tables, there are no differences found, even when there are differences.

I can send you a code sample and two databases, but the code sample is exactly the same as your console app, except that the last "Next" is moved lower to after the "End Using" of the objComparisonSession.
The databases really do not matter; I tested with different databases: all will give this result, as long as they contain more than one table and they all have differences.

If you do want this code sample and the databases, please let me know how I can send them to you.

Kind regards,
Bastiaan.

Brian Donahue

Please email to support@red-gate.com and put F074879 somewhere in the subject line.

Brian Donahue

Just to confirm... all you want is to have the data scripts for each table, one table at a time, comparing a backup file to a live database? Ignoring the discussion you referenced at the top of this topic, which is talking about a bug that needed to be coded around. I'm not sure that is relevant to the solution you are looking for. It was all about "DropConstraintsAndIndexes" not being applied to the migration script (bug CSD-173).

I think it would be better to look at this problem from scratch.

Bastiaan Molsbeck

Yes, that is exactly what I am trying to achieve.
But because of that bug, the script your component generated did not include the "DROP INDEX..." scripts.
Therefore, I needed the workaround I currently use.

(And when using a live database as source, it does work correctly. But when using a backup as source, no differences are found, as stated in the original post of this topic).

With this info, do you still need a code sample and databases from me?

Bastiaan Molsbeck

Hi Brian,

I have just sent an e-mail to support@red-gate.com with "F074879" in the subject line, containing a sample project and two databases (total 274KB in size).
Please let me know if you did not receive this e-mail.

Looking forward to your response.

Kind regards,
Bastiaan.