How to automatically pull SQL Server database objects from a shared script folder to a local database

Challenge

As it was explained in article on How to automatically compare and synchronize SQL Server database objects with a shared script folder, this article will explain the solution for the reverse process, when changes needs to be pulled from a shared script folder to a local database. This might be helpful if a developer returns from vacation and wants to catch up to the team with all changes or if a build has been tweaked, as part of a recent test/delivery and the latest version needs to be re-propagated directly to all developers via their local development database.

July 4, 2017

How to automatically compare and synchronize multiple databases on different SQL Server instances

Challenge

It’s often quite a challenge to keep all SQL databases located on different SQL Servers in sync. As time goes by, a lot of schema and data changes are made on QA databases on daily basis that require to be in sync with Production databases.

To keep everything in sync, there should a system that would be either triggered or scheduled to run the comparison of all SQL databases and synchronize the ones were changes are detected. This system should be also aware of any dependencies during the synchronization in order to keep the SQL database integrity.

April 5, 2017

Automatically comparing and synchronizing SQL Server database schema changes

If only schema changes are made in the source control repository, a 3rd party tool – ApexSQL Diff can be used to perform the schema synchronization. ApexSQL Diff is a tool that can be used to compare and synchronize schema differences between live SQL database, source control repositories, database backups, script folders and ApexSQL snapshots. It can handle any dependencies, triggers, etc. and create an error-free synchronization script, while ensuring a database integrity.

January 13, 2017

How to automatically monitor a SQL Server database for schema changes and create an audit log in source control

The challenge

As a DBA considering version controlling a database, there are a lot of challenges to setting up the team with software to check in changes, to manage the development environment (shared or dedicated models) and set the rules of the game – locking vs not locking etc. But before committing to this fully, a potentially preliminary stage would be to set up a centralized system, where the database was automatically committed to source control each night. This could be done by a single person and wouldn’t require developer participation or even knowledge.

January 10, 2017

How to build a “smart” SQL Server Data dictionary

In the article, “What is a data dictionary and why would I want to build one?” a data dictionary was described and compared to other alternatives to documenting, auditing and versioning a database. The article also described the difference between a Dumb and a Smart data dictionary.

Now that we’ve whetted your appetite, this article will explain how to create a smart data dictionary using XML schema change exports from ApexSQL Diff.

January 10, 2017

How to automatically keep two SQL Server database schemas in sync

Challenge

When working on a SQL database development, there is sometimes a requirement to keep two databases in sync. For example, in a development environment there is a need to automatically synchronize changes with a QA database frequently, so that tests can always be run on the most recent version. The implemented mechanism should handle this by detecting a specific schema change in the DEV database and automatically synchronizing with the QA database, fully unattended and on a schedule. The whole process should be run unattended and to be fully automated and the databases will be updated in near real time as we’ll schedule the process to run every 15 minutes.

January 9, 2017

SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script

In this article, the last step of SQL Server database continuous integration (CI) workflow, or the first step of a continuous delivery (CD) workflow) the Sync step.

In the previous article, the Test step described how to create and run unit tests against a database. If all tests are passed, the tested database can be compared to a final QA environment or even Production databases, and a synchronization script will be created to publish the changes.

July 4, 2016

How to deploy changes directly to a SQL database from a source control repository

One of the challenges these days is how to pull the latest changes (of SQL objects) from the source control repository and deploy them into a SQL database. This process is particularly helpful in the CI workflow Build step, when developers want to build a SQL database from the committed changes in the source control repository, so they can test if their changes compromised SQL objects from the source control repository or they will be built successfully.

April 27, 2016

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 compare SQL databases in SSMS

ApexSQL Diff, a SQL Server comparison and synchronization tool includes the ability to work from within SQL Server Management Studio. In addition of comparing and synchronizing SQL databases directly from SSMS, it can also compare and synchronize backups, script folders, snapshots and versions from source control projects. This should improve productivity and speed up common tasks.

March 2, 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.

How can ApexSQL Diff help with these SQL script deployment and database SQL database building tasks?

To build a SQL database directly from the script folder used as the source simply create a new SQL database, leave it blank and use it as the comparison and synchronization destination. ApexSQL Diff will synchronize it by creating all objects from the script folder used as the source.

The process is similar when upgrading a SQL database, except that the destination will be a SQL database that already has some objects. When starting ApexSQL Diff and opening a new project, the available options are shown in the New project window:

By selecting the Script folder as a data source type and setting up the script folder location, the script source is set up and all that remains is to select the destination data source. Additional options are available under the Advanced options: Schema mapping, Object filter, Package and Additional scripts.

Schema mapping enables specifying how owners/schemas are handled during the SQL databases comparison. By default, schemas of the same name are automatically mapped to each other:

The Object filter allows users comparison and synchronization of only specific SQL objects, a useful option when updating a live SQL database from a backup or test script on a daily basis. It is an invaluable feature when it comes to saving time, especially in cases of a large number of database objects:

Once all the options are set, click the Compare button and the process leads to the main grid window containing the comparison result:

Here, ApexSQL Diff allows users further synchronization tuning by selecting SQL objects needed to update a live SQL database from a script folder as a source. It is even possible to track differences per individual object on a script level by using the Script difference view panel:

After finishing the SQL objects synchronization fine tuning, the synchronization of the scripts and the live SQL database can commence with the click on the Synchronize button from the Home tab:

After the first step, in which direction of synchronization is set, in the second step, ApexSQL Diff will check dependencies:

The next step before a synchronization script is generated offers a possibility to save a copy of the synchronization script. Saving a copy of the synchronization script is a good way to keep track of updates and deployments over a SQL database, and post-synchronization analysis, if necessary:

In the last step, ApexSQL Diff generates Summary and warnings, a step showing synchronization summary and potential problems or objects that cannot be scripted and/or synchronized, and allowing to preview the impact of the synchronization script before executing it:

The process described here is a synchronization using a script folder as a data source and a live SQL database as a destination, offering an efficient solution to build a SQL database and synchronize its structure and objects to another database through the highly customizable synchronization procedure.

Useful resources:

How to: Generate a Script (SQL Server Management Studio)
Generate and Publish Scripts Wizard
How to: Deploy Changes to New or Existing Databases

March 2, 2015

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

ApexSQL Diff has the ability to compare and synchronize a database backup against a script folder, which could be of great help during the development process. It might be needed to use the backup instead of the live database to create a SQL database build scripts, and this is where ApexSQL Diff can help. There is no need to waste time and resources on restoring the SQL Server database backup. ApexSQL Diff enables users 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 the one doesn’t exist, the dropped table may still be recovered (in some cases even quicker than with 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.

Depending on which source control system is used, and what the usage schema and environment flow are, the goals of using source control systems are safety, easier team-based database development, and deployment automation.

In a multi-environment system, each environment represents a state through which a database must pass, until it reaches its final destination – production. For example, development of new features and fixing of existing issues is carried out in Development, then they are tested in Integration (or Testing) and Production is where your live databases are.

One of the common rules is that all deployments come from source control. In other words, all database changes must go into source control first and then be deployed to production.

Does it always have to be so?

It should, but unfortunately, it isn’t. Despite all the benefits that a source control system brings, the key factor is still human. If there’s no discipline, developers can skip a step or two, ignore a rule, or do something that doesn’t comply with the standards. A developer in a time crunch might have applied a change directly to production, skipping both the development and testing environments, and never adding the change to source control.

The good news is – in a versioned environment, this can be easily fixed. Furthermore, possible problems caused by this undisciplined approach can be prevented by checking the production database before deployment.

No matter how small the change is, and how small the difference between the production and the source control version is – it’s still a difference. Even just changing a column data type from int to e.g. nvarchar(50) in production, makes a difference that can cause headaches when deploying a new version from source control. Next time the new version is deployed to production, either scheduled, or on demand, deployment can fail.

If lucky, all that will happen is that the latest version cannot be deployed. In a worse scenario, even some data can be lost.

How can be made sure there were no direct modifications of your production database?

Before the deployment starts, check whether there are any differences between the production database and the source control system version that was deployed last. Another option is to compare the live database to a full database backup created immediately after the last deployment if they are available.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects in live databases, full and differential database backups, databases versioned in source control systems, script folders and database snapshots. It generates comprehensive reports for the differences and can automate the synchronization process.

  1. Start ApexSQL Diff
  2. Once the New project window is shown set the following in the Data sources tab:

    • Select Database as a source type
    • Select the production database as the source
    • Select Backup as a destination type
    • Select the full database backup taken immediately after the last deployment as the destination

  3. Click the Compare button
  4. To switch how to view differences, on the View tab, click one of the buttons under the Grouping section. By default, the Group by difference type option is selected:

  5. All objects in the Results grid will be grouped as equal, different, in source or in destination only.
    If there are no differences, all objects will be shown as equal.

    If there are any different objects, check out their differences in the Script difference view panel:

  6. To create a synchronization script, check the objects in the Results grid that should be synchronized
  7. Click the Synchronize button from the Home tab to initiate the Synchronization wizard:

  8. In the first step of the Synchronization wizard, the synchronization direction will be shown, while in the second step, any dependent objects will be automatically checked and added in the synchronization script:

  9. Under the Output options step, leave the Create a synchronization script action and choose the Save script to a file option:

  10. In the last step, review the Summary and warnings and click the Create script button to finish the process:

    Get back to developers, investigate the differences, and decide the best course of action in this specific situation. The generated script can be used in desired sandbox to synchronize the testing database with the production one.

    To avoid deployment problems, caused by changes in production that didn’t come from source control, use SQL server comparison tool ApexSQL Diff. If there are any differences detected in production, it’s not recommended to make any further changes to the production database without testing them first.

April 11, 2013