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 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
GO

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 – when a specific object (table, view, stored procedure, user, etc.) needs to be 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 a database is versioned under source control, an object can be easily found there, but let’s assume that only SQL backups are available.

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

How to deploy SQL database on several different SQL Server instances and databases

When work on a database in a development environment is completed, developers are required to send the new version in for testing. If the testing is done on one or two machines – this is not a problem. However, if there is a need to test the new version on 10 or 15 machines (with different operating systems, SQL Server versions, or service packs installed), this can be tedious.

Another situation where scripts need to be executed on multiple servers is if there are many databases for different clients that were all derived from the same database and share many common objects. Any changes made in the development must be pushed to all users.

April 4, 2013

How to build a SQL database from source control without dependency problems


One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.

April 4, 2013