| || |
Comparing .mdf files
Does anybody know of a quick way of comparing 2 SQL database .mdf files (other than converting them to .txt files (table by table) and then comparing them?
I have a big database application. It is a MSSQL database. I need to use SilkTest to compare the two versions of the applications for regression testing to verify that things that were working in the older version are not broken in the newer version.
Steps I am planning on following are:
* Start with the older and stable version say 1.1 of the app.
* Start with the state say 'A' of the database.
* Perform a group of tasks through SilkTest or Manually. This will change the database state from 'A' to 'B'. Store the new 'B' state of the database. (by converting the database into .txt files using the DB Tester in SilkTest)
* Again start with State A in version 1.1. Perform a different group of tasks. This will change the database state from 'A' to 'C'. Store the new 'C' state of the database.
* Repeat these steps for all the tasks and store the new database states.
* Start the next version say 1.2 of the application with database state 'A'.
* Perform the same groups of actions mentioned above (through SilkTest) and make sure that the database reaches to the appropriate states 'B' OR ‘C’ etc… by comparing the databases directly.
* Add more states or change previous states if necessary.
* Repeat these steps for the next version say 1.3.
I have around 200 tables and don't have a list of tables that change depending on the task so was thinking of comparing the databases directly otherwise I could only compare those tables and will be done.
I hope I am clear.
Is this a practical approach? OR is there any other approach you can think of ?
Thanks in advance for your help.
Re: Comparing .mdf files
Assuming you can make an odbc connection to the database(s), then one thing you can do is to have silktest read in all the data of each table (executing "select * from <tablename>" for each table), each table into a list of list of anytype. Then run through the lists, comparing each field in each row.
This will be slow. In order to speed it up a bit, I recommend that you:
- find out which tables are altered by each task. This is basic testing information that the developers should be giving you. If you know which tables are affected, you can just check the relevant ones.
- use as small a dataset as is reasonably possible.
And in the long term:
Get the developers to tell you, for each task, exactly what it does so you can test it in detail. That includes what database tables are affected, what appends/edits/deletions are made, what they depend on, how the calculations work, etc.
My aut is also a database application with hundreds of tables, and the testcases we have implemented check for the specific database changes each task is supposed to have performed; now we have such testcases, regression testing in a situation such as you describe is pretty trivial.
But, implementing scripted testcases to test each task separately, predicting the data changes and checking for them after the task has been performed by silktest, has taken us years and we're still working on it. Like I say, it's a long-term solution.
Re: Comparing .mdf files
Thanks for your reply. I totally agree with you. But, I wish I was lucky enough to get the information from the developers, since they are third pary. So, I need to find a workaround myself.