Red Gate forums :: View topic - Comparing backup file with live db: no TableDifferences!
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

Comparing backup file with live db: no TableDifferences!

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



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Tue Jul 09, 2013 3:33 pm    Post subject: Comparing backup file with live db: no TableDifferences! Reply with quote

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/viewtopic.php?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:

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Thu Jul 11, 2013 10:12 am    Post subject: Reply with quote

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:
Code:
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_Comparison_SDK/SQL_Comparison_SDK_10.0.0.111.zip
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Thu Jul 11, 2013 10:25 am    Post subject: Reply with quote

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:
Code:
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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Mon Jul 15, 2013 2:26 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Jul 17, 2013 7:37 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Wed Jul 17, 2013 4:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Thu Jul 18, 2013 9:03 am    Post subject: Reply with quote

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/viewtopic.php?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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Mon Jul 22, 2013 10:11 am    Post subject: Reply with quote

Please email to support@red-gate.com and put F074879 somewhere in the subject line.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Mon Jul 22, 2013 10:34 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Mon Jul 22, 2013 10:38 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Mon Aug 05, 2013 1:47 pm    Post subject: Reply with quote

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.
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