The story of cloning a Microsoft SQL Server database
Recently I was responsible for deploying a Web Application, which leverages SQL Server. For convenience reason, the Backend should automatically realize whether the database structure is expected when it first starts; if not, the database must be programmatically created before the main logic of Backend takes place.
In developing environment, I usually use Backup and Restore commands in SQL Server Management Studio (SSMS) as followed:
The backup file will be binary data (in contrast to text files). In my deployment case, the database has to be set up programmatically; thus, ruling out the use of SSMS. Although one can workaround via sqlcmd.exewith an appropriate Sql script, this executable is not always available, especially the Backend and the SQL Server are different machines.
But hey, my Backend is written in F# with .NET Core, why don’t I just make use of SqlConnection (link) and SqlCommand (link) which are already extensively used in the code, in order to restore the database?
Then, the remaining issue is to prepare the right Sql script to be executed, using the aforementioned .NET classes. One option is to restore with commandRESTORE DATABASE(link); however, a big disadvantage of this approach is storing the binary backup file somewhere, which is clearly inconvenient. So, it was decided that the database can be structured with good old commands, such as CREATE DATABASE(link) or CREATE TABLE (link).
One thing for sure: we should not go through all database/table options, understand them, and manually write CREATE DATABASE, CREATE TABLEourselves. SQL Server must somehow generate it for us. It took me a while to learn that SQL Server doesn’t support one single place (in its GUI) to produce a script that creates both the database and its tables.
Indeed, it is a 2-step process:
Executing those 2 scripts with ExecuteNonQuery()(link), and we will end up with our desired state of database.
An implied benefit of using clear-text Sql script is greater transparency as well as more flexibility and control over what will be created in the database (pay attention to the “Advanced Scripting Options” dialog). A typical use-case is in option Types of data to script, we choose Schema only because data in developing environment can be unnecessarily plentiful and mostly for testing purposes. But we still want to have some initial rows in some tables; the Sql script is plain-text, making it easy to just add more INSERT INTO SomeTable.
The binary backup file looks like a black box. It could be more compact and quicker to restore, but well, still a black box. Nevertheless, no approach outperforms the others. At the end of day, what is best is what suits our specific needs.
Version tested: SQL Server 2017, SSMS v17.9.1, F# with .NET Core v2.1.
