Copy dbo to a new Schema in MSSQL

Question

I got a database in SQL where everything is in the dbo schema. Now we want to copy that schema to have two schemas in the same database with the exact same content. Is there any easy way to do this in an Azure Database?

(We want to separate our Development and UAT but still only use one database)

Answer 1

While the other answer posted here, using the SSIS to transfer the SQL objects, will work, I feel compelled to point out that your approach raises a lot of other concerns.

Using a single database for two environments is not a good practice. The first big issue with is how do you handle deployments? Let's say UAT is in the uat schema and development is in development is the dev schema. You make a change to the Customers table, how do you deploy the table change to both schemas? If you use SSIS, you will need an on-premise SSIS server that handles copying the changes to the various shcemas in the target database. This will create a large maintenance headache and likely lead to important changes being wiped out.

Another issue this results in how does your application target a specific schema? You can have a login defaulted to a specific schema when it runs, but many ORM tools will want to the schema ahead of time. This will force to write the code in way that could potentially force to deploy different code to different environments. This opens up the possibility that parts of the code won't get test until production.

The last concern I have is with this approach, versioning your database becomes difficult and many of the tools that are out there, won't support what you are doing. This means you will likely be creating custom processes and tools to deploy a database instead of leveraging tools built by vendors like Microsoft or Red Gate. This puts you in a position where you need to support not only the application you make for your customers, but also an application to do your job (basically doubling your work).

My suggestion is think about the need to run two environments in a single database. I'm assuming this is likely due to cost, in which case you might find this to be false. Azure has many pricing tiers to support customers with various budgets. Depending on your application workload for both environments, you will likely find you need a large DTU database to support both. You might find that by having two databases, you can leverage smaller DTUs tiers which may end up being cheaper.

Answer 2

Please use the CopySchema option of the Transfer SQL Server Objects Task in SSIS as explained here.