How to build a SQL database directly from scripts

In a SQL database lifecycle two major deployment tasks are: the building of a new SQL database and an upgrade of the existing one. ApexSQL Diff is a SQL database comparison tool that can help for both of them; it allows deploying SQL scripts saved in a script folder to a new or an existing SQL database. In various “real world” scenarios oversights and omissions are plausible, leading to many errors or database structure/objects corruption. In cases like these, it’s good to have a script ready so that a SQL database with test objects can be easily rebuilt even if something goes wrong.

March 2, 2015

How to create a SQL database build script directly from a backup

ApexSQL Diff Pro has the ability to compare and synchronize a database backup against a script folder, which could be of great help during the development process. You might need to use the backup instead of the live database to create a SQL database build scripts, and this is where ApexSQL Diff Pro can help. There is no need to waste time and resources on restoring the SQL Server database backup. ApexSQL Diff Pro enables you to save a database schema as a set of object creation scripts directly from your backup.

March 2, 2015

Transfer SQL logins for users with a large number of SQL-authenticated logins

SQL Server logins are the credentials that enable users to connect to the Database Engine instance. SQL logins are distinguished based on the type of authentication method: Windows-authenticated, SQL Server–authenticated, Certificate, and Asymmetric key. One SQL login can be mapped to only one user in each database.

April 28, 2014

How to make complex SQL database deployments easier

Application deployment is not an easy task. For more complex updates, besides a new version of a database that should be deployed, a new version of the application should also be deployed, and even the environment configuration changes (e.g. IIS settings for a web application or some other server settings) and post-deployment testing might be necessary.

Problems with complex deployments

A complex deployment often involves a lot of manual work, which makes it slow and error prone. While these characteristics are not a big problem in a test or development environment, they are certainly unacceptable in production.

April 30, 2013

How to check whether you can safely deploy to production database

The time when source control systems were used only for code development is gone. Today, database versioning is quite common, even in small companies.

As there are many different source control systems, with different features and options, there are no strictly defined rules how to use them. Each company can create its own standards and rules for using a source control system for database versioning, following the best practices and recommendations.

April 11, 2013

How to script encrypted SQL database objects

One of the ways to keep stored procedure, function or view schema hidden is using the WITH ENCRYPTION statements. In this way, access to objects’ DDL script can be locked, so that unauthorized users cannot see them:

CREATE FUNCTION [dbo].[ufnGetAccountingEndDateEnc]()
RETURNS [datetime] 
with encryption 
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));

April 8, 2013

Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – you might need a specific object (table, view, stored procedure, user, etc.) recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If your database is versioned under source control, you can easily find object there but let’s assume you only have SQL backups.

April 5, 2013

Script a database for specific DML records only

Sometimes the best test data is the data you have in your live, production SQL Server database. Since using production data is most often not an acceptable option, this requires retrieving the data from a live database table and inserting it into a new table. The challenge arises if you don’t want to insert all records from the original table but only the records that comply with certain conditions.

How to create INSERT INTO script that moves only specific data from one table to another.

April 5, 2013