How to work with SQL database source control labels

A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.

October 27, 2015

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

4 techniques for recovering lost tables due to DROP Table operation

An accidentally dropped table may be recovered in several ways. The choice of technique depends on the resources available for the recovery. The first choice is usually a database backup. But, even if you don’t have one, the dropped table may still be recovered (in some cases even quicker than if you had a backup).

August 15, 2013

How to compare and synchronize databases programmatically

ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations

But, what can be done when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable API

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

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

How to recover a specific dropped object

Even with all precautions taken, mishaps with databases are still a possibility. Although it is good to be prepared for a disaster – the database is in the full recovery model and transaction log and database backups are created regularly, there are still some situations when a problem cannot be solved using an out-of-the-box solution.

One of these issues is restoring a single object. It’s not unusual to delete a SQL database object (e.g. a stored procedure) that is still needed, or modify it when the old version might be required later.

April 4, 2013

Compare SQL Server database schemas automatically

It’s common knowledge that running database changes directly in production, without thorough testing first, should be avoided.

If there are enough resources in the environment, there would be at least one testing and one production SQL Server instance. However, that introduces another challenge. When everything is set up in the test, and runs smoothly and as expected, how can it easily be applied to the production instance?

April 4, 2013