Posted on

How to Create and Refresh Development and Test Databases Automatically Using SQL Clone and SQL Toolbelt

SQL Toolbelt

Phil Factor shows how a set of Redgate tools can be used together via PowerShell to build a database from object-level source, stock it with data, document it, and then provision any number of test and development servers. Before tearing down and rebuilding a database to a new version, we take care to save any DDL changes made to the existing copy.

You may also like:  Database Continuous Integration With SQL Clone and SQL Change Automation

In order to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build when there is a proliferation of copies and the database is big. This article provides a PowerShell automation script that creates and refreshes the various databases on one or more servers that are required for test and development work. It uses Redgate's SQL Clone (a component of SQL Provision), SQL Compare and a few other optional tools from the SQL Toolbelt.

Posted on

Safely Deleting Clones and Images During Database Development and Testing

This is the third article in a series that explains how to use SQL Clone, part of SQL Provision, plus a collection of PowerShell scripts, all with a shared configuration data file to deploy, revert, customize, delete, and refresh clones for database development and testing work.

The first article, Deploying and Reverting Clones for Database Development and Testing, mapped out a Clone installation consisting of a source database, an image, and some clones. It presented a shared configuration data file and then a PowerShell clone installation script that used this config file to create a suite of clones for testing or development work. A second PowerShell script showed how to revert or roll back a clone to its original state, ready for the next set of tests or development work to begin, first ensuring that any changes made since the clone was created were saved to source control.

Posted on

Deploying and Reverting Clones for Database Development and Testing

SQL Clone is a very handy device for database developers. The main thing stopping me from initially using it more widely was culture shock. It is a new and strange experience to be able to have several local copies of the database I'm developing without worrying about disk space and without having to fuss about keeping them up to date. This means that I can create a series of cloned databases and then migrate each one to a different version without requiring enormous amounts of storage.

Even more of a shock is being able to make radical changes to the data or schema while testing in the knowledge that it takes only a few seconds to revert the database back to its original state, ready for the next test run. I'll show how to use SQL Clone, the database provisioning component of SQL Provision, to achieve all this. With a bit of additional scripting, you can even revert a copy of the database without worrying too much about losing any schema changes by using SQL Compare to compare the altered database with an unadulterated clone and save any changes as scripts in files.

Posted on

Create and Refresh Development and Test Databases Automatically

In order to be able to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build when there is a proliferation of copies and when the database is big.

This article illustrates a solution, showing how a set of Redgate tools can be used together via PowerShell to build a database from object-level source, stock it with data, document it, and then provision any number of test and development servers. Before tearing down and rebuilding a database to a new version, we take care to save any DDL changes made to the existing copy.

Posted on

SQL Provision and Azure SQL Database: Creating Local Development and Test Databases

Your organization, like many others, is probably moving towards the use of cloud-hosted database platforms, such as the Azure SQL Database. However, even if some of your production databases are on the Azure SQL Database, you still need to provide development and test copies of that database, whether they are hosted on a local, physical, or virtual machine, on a virtual machine in the Azure cloud, or in the Azure SQL Database. The problem with hosting lots of copies of the database in the Azure SQL Database is that it gets cost prohibitive, it places more restrictions on developers than they often like, and sometimes, network connections and bandwidth cause problems. This means you’ll sometimes need to move a copy of the database from Azure SQL Database to the local system for development and test work.

This article will describe how you can create a local copy of the database from a BACPAC of your Azure SQL Database production database and then use SQL Provision to deploy as many development and test copies (clones) of that database as you need. If your database contains any non-public data, you can also use the Data Masker component of SQL provision to ensure this data is obfuscated before deploying the clones.