Comparison of tables, columns in Databases

Ruslan_2024

Hi
I masked the database 3 months ago using DATA MASKER. Now I want to mask the same database again. How to determine if new tables and columns have been created on it. In short, I want to compare the old with the new.

TheMaskedData

Hey @Ruslan_2024 if I remember correctly, if you just want to compare the schema of a new database to what is stored in the Data Masker config you can find a schema compare option in the tools bar. 
If you want to compare the masked database to a new copy of the database though then you might want to use Redgate SQL Compare to check for schema differences. 
Hope that helps!

Ruslan_2024

Thanks @TheMaskedData
If I masked same schemas 5 times, which one will be stored in Data Masker config? And can I compare 1st and 5th schemas?

TheMaskedData

@Ruslan_2024 it depends if you've refreshed / updated the data masker controller at any point ,whenever you did this (or if you haven't it'll be the schema from the initial masking set creation)

Refreshing a masking set: Refreshing a Masking Set Controller - Advanced Operations with Data Masker for SQL Server - Redgate University (red-gate.com)

If you use the Schema Comparator it will compare the current version of the schema in Data Masker to the Live Schema of the Database you're connected to

Ruslan_2024

TheMaskedData said:

Hey @Ruslan_2024 if I remember correctly, if you just want to compare the schema of a new database to what is stored in the Data Masker config you can find a schema compare option in the tools bar. 
If you want to compare the masked database to a new copy of the database though then you might want to use Redgate SQL Compare to check for schema differences. 
Hope that helps!

Thanks @TheMaskedData

What is stored schema, if I masked 5 times same schema which one it will store?

TheMaskedData

When you set up the Data Masking set @Ruslan_2024, Data Masker copies the Schema into the Controller creating an "Offline Schema" if you haven't refreshed that controller (like in the video) at any point then Data Masker will think the schema hasn't changed, however when you run it against the database, even if the database has changed, DM should still run in most cases.

This is because DM is generating the UPDATE/MERGE statements which, if no conflicting schema change is present (like a missing column) will of course still succeed - probably hence why you were able to run the masking 5 times even if t here were changes.

If you use the Schema Comparator now and haven't ever refreshed the controller, DM will think the database looks exactly the same as it did 3 months ago and so will compare the "Offline Schema" with the schema of the live database and tell you the differences. 

If you then want to update DM so it's aware of any new tables and columns then you can refresh the controller as the video shows.