Today I spent much of my coding time on building a second database to run parallel copies of a new feature so that I could make sure the overlapping areas of output were remaining the same from the original process to the modified process that could do more.

Although this is a great case for writing automated tests, my customer wanted the ability to look at both systems and compare them. So what was my approach?

Initially, I wasn’t sure what kind of access I had to the SQL server in terms of creating new schemas or new databases. It seemed that maybe creating a new schema might be a way to go and since that seemed less invasive than creating a whole new database on the SQL Server I started there.

It turns out there are not really decent ways to copy one schema fully to another. It just seems like they were not intended to be used that way (especially considering they are in the Security section of the database), it seems like they are more designed to separate security concerns to different data silos.

Ok, well thankfully I did have access to the server to create another database and this turned out to be the simplest solution. I already had a process I use for copying databases. I like to use the Task -> Generate Scripts… menu and use that to create large SQL scripts to DROP and CREATE objects automatically. It can take a long time with a big database, but it seems more dependable and forgiving of others being connected to the database. Once I have the script, I can have the same database on a different machine and then run the script on that machine. I use the SQLCMD command line utility to import the database because SSMS does not handle the ginormous files typically created by Generate Scripts…

I also can use Notepad++ (my goto windows Notepad replacement) to open the giant file and replace the name of the database with a different database name, then run the script to create the same database under a different name.

So I created a script using the live database, loaded it up on my dev database using the same name, then changed the database name in the sql file, and loaded it up with the second name on my dev database. I then was able to run my update script against the database with the new name and create two instances of my application each using one of the different databases. Easy peasy!