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

Static Data and Database Builds

Whichever way you wish to ensure that a database, when built, has all the data that will enable it to function properly, there are reasonably simple ways of doing it. Phil Factor explains the alternatives.

Often, we can't build a fully functional SQL Server database just from the DDL code. Most databases also require what is often referred to as 'static' or 'reference' data, which will include such things as error messages, names of geographical locations, currency names, or tax information. This data must be in place before the database can be used in any effective way. The static data needs to be in source control because, like the code, if it changes, you need to know why and when.

Posted on

Refactoring Databases With SQL Prompt

Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table).

Many of the tools that SQL Prompt provides are ones that you'll use more or less every single day you write T-SQL code. The refactoring tools in SQL Prompt are more like the ones in the snakebite kit that you take on a long hike in the desert. You hope not to have to use them too often, but when you do, they are extremely valuable. My previous article focused on small scale code refactoring, limited to altering the 'internals' of a single batch or object. A less frequent but harder requirement is to change the 'public interface' of an object, such as by changing the name of an object, or column, or even by splitting tables to achieve a better design.