How to create and automate test data generation for a SQL Server database


Challenge

Generating test data for SQL Server database should be a process that easily populates a SQL database with data (where needed). Adding of test data can be done by manually creating scripts or by using a specific tool. But for repeatable processes like continuous delivery (aka DLM, CI, continuous integration), automation of test data generation is an important consideration.

October 24, 2016

How to create and run SQL Server database unit tests automatically

It’s important to run SQL Server database unit tests to ensure that any changes made will not “break” the database or in other words violate the integrity, relationships or the functionality of the database itself. Ideally, many issues can be found in development, even pre-QA, if unit test coverage is created and run early and often. So even though we may understand that running unit tests against database changes is important, we may also experience the fact that creating and running SQL Server database unit tests manually can be time consuming, especially for lager databases which are under active development.

October 12, 2016

Viewing object dependencies in SQL Server

Deleting or changing objects may affect other database objects like views or procedures that depends on them and in certain instances, can “break” the depending object. An example can be that if a View queries a table and the name of that table changes. The View will no longer function.

June 9, 2016

Improving productivity with SQL snippets

SQL snippets can be a big boost to productivity when writing T-SQL. First, to use a SQL snippet in a script you do not need to know the syntax, only the purpose of that SQL snippet (e.g. Delete an object, Create a table, etc.). Using snippets also reduces of the lines of code that has to be typed, and thus decreases the potential for errors that could occur from typing.

May 5, 2016

SQL Server database object search

Developers and DBAs often perform database searches in order to find and obtain additional information related to specific objects, sometimes without knowing the full name of the object.

April 11, 2016

Retrieve unsaved SQL query scripts due to SQL Server Management Studio crash

Recovering work after SQL Server Management Studio crashes

Imagine a situation of working in SSMS, and suddenly the power goes out, the system gets crashed, SSMS gets crashed or your work session was abruptly terminated for any other reason, which causes the loss of unsaved work on various SQL scripts. In this situation SSMS provide prompt dialog window for recovered data after restarting:

March 21, 2016

How to script SQL Server database users with roles

Introduction

A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in order to simulate real time testing scenarios on it. Once the production database backup is restored to the DEV SQL instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But we still need the old DEV database logins for the DEV site connection.

February 5, 2016

How to analyze SQL Server database dependencies in client code e.g. C#

ApexSQL Clean has, among others, a useful feature that most people are not even aware of. It provides a client SQL code analysis in C#, Delphi, VB.NET, XAML, XML, ASP.NET, HTML, CSS code etc., and detects which SQL objects are actually being used and which ones aren’t in the referenced database. The SQL code analysis is performed by inspecting the code file for objects in the SQL database. This feature helps keeping SQL databases clean and organized

April 16, 2015

How to automate batch SQL object scripting and committing to source control

Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.

In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.

April 15, 2015