At the moment I have Database_One
and Database_Test
, both SQL Server databases.
Database_One
represents my User's data that I shall not be modifying.Database_Test
represents a replica ofDatabase_One
, but has test data that I edit whilst I am developing.
In the future I may have Database_Two
, Database_Three
etc as more clients use my program.
Currently if I need to add a column to a table in Database_Test
, I would need to add it to all other databases individually, manually.
I would rather have a system where all databases are replicas of a master database, but have their own data. So that if I need to add a new column to a table in the master database, this is replicated to all others but the data is individual to each database (and therefore client).
Is there a method that lets me do this with SQL Server databases?
I should also add that these are SQL Server instances running in Azure.
I would also like this mechanism to copy the database structure, but not it's data, so that I can easily setup new databases in the future.