ApexSQL Diff – Exporting SQL data sources

Applies to

ApexSQL Diff

Summary

This article explains all about exporting SQL data sources in ApexSQL Diff.

Description

Snapshot

SQL compare toolApexSQL Diff, has a feature that allows you to create a SQL database snapshot, as well as using it for comparing and synchronizing with other SQL data sources. ApexSQL snapshots are binary files that represent the database structure only, not the data. Snapshots created with ApexSQL Diff contain a complete record of a database structure. Creating database snapshots will take less disk space than database scripts.

To create a snapshot using ApexSQL Diff, select one of the two ways:

  1. From the New project window, in the Data sources tab, click the Export data source button:

    ApexSQL Diff export data source option including SQL export script folder

  2. From the Home tab of the Actions group, select Export and then Export data source:

    ApexSQL Diff export data source from the Home tab

Once the Export data source window is shown, choose Snapshot as an export type and select the server and database from which the snapshot will be created, and enter SQL server credentials (if SQL Server authentication is used):

ApexSQL Diff showing Export data source window with 3 different export types

When the OK button is clicked, the Save snapshot window will be shown, where a snapshot name can be set. The snapshot default name consists of {server name}_{database name}.axsnp. Extension .axsnp is a standard ApexSQL snapshot extension.

Quick tip icon

Quick tip:

ApexSQL Diff decrypts all encrypted objects created using the WITH ENCRYPTION clause

The snapshot can be used in a comparison process with other SQL data sources, to create a deployment script, or as an input source in the build process in ApexSQL Build. Since the SQL snapshot is designed as a read-only binary file, using the snapshot as the destination can only create a synchronization script that can be used to update the database from which the snapshot was created. The snapshot file cannot be altered directly.

ApexSQL Diff setup Snapshot as a source type

If used as the source, and a database, script folder or source control as the destination, the resulting synchronization script will update the destination that is synchronized with the snapshot. If there is a need to roll back changes that have been made to a database, using the snapshot created before the changes were made as the source, and the database as the destination, will roll back any changes to the initial state. Snapshots are compatible across different versions of ApexSQL Diff. Snapshot files created with one version of ApexSQL Diff can be used with any other ApexSQL Diff version, regardless of being newer or older.

Script folder

Script folder exporting option allows you to export SQL scripts that create the database structure – DDL scripts – into a folder. For each database object, a corresponding script file is saved on the disk. If Subfolder is checked in the Mapping configuration dialog, every object type will have a subfolder where DDL files for this type are stored. If the Subfolder is unchecked, all the files will be stored in the specified folder.

To create a script folder, In the Export data source dialog, choose Script folder:

ApexSQL Diff using Script folder to export SQL script

After a destination for saving the Script folder is selected, the Mapping configuration window will appear. The Mapping configuration window defines how the script files are created and where they are saved. All settings displayed initially are inherited from the default project settings:

ApexSQL Diff Mapping configuration window to export SQL script or multiple scripts

Here, select which object types will be exported to Script folders. Check the Subfolder checkbox to save the scripts for the object types in dedicated subfolders. The subfolder names can be edited by entering the name directly in the Subfolder field:

ApexSQL Diff Mapping configuration window with changing the name of the Subfolder before exporting SQL scripts

The File name is used to define the generated name for an object. Use the supported tags {database}, {schema} or {object name}. The dialog allows export the settings defined in XML files and to import already defined settings from a saved XML file using the Export and Import buttons.

After the OK button is clicked, in the bottom right corner of the Mapping configuration window, the SQL database will be exported into a Script folder. If all checkboxes were checked, the following folder structure should be created:

ApexSQL Diff Script folder after the export is done

Source control

The last exporting option is export to a Source control project. Source control systems are commonly used in teams where different people can change the same files. A number or letter code termed the “revision number”, or simply “revision”, usually identifies changes. Each revision is associated with a timestamp and the user that made the change.

To export to a source control project, choose Source control as an export type:

ApexSQL Diff using Source control as export type

By choosing Source control as an export type and a database for exporting, click the OK button to initiate the Source control wizard, where a source control system can be selected:

ApexSQL Diff Source control wizard with setting up connection type

In the next step, specify the connection parameters for the source control repository:

ApexSQL Diff entering credentials and repository under the System login tab in the Source control wizard

Learn more about setting up a source control wizard from the article, Setting up the Source control wizard for a Git repository.

Using the Export data source option, the scripts can be directly deployed to the source control project.

Using the export functionality is not only a useful feature in ApexSQL Diff but as can be seen it was carefully designed to achieve simplicity and maximize the effectiveness.

FAQs

Q: What is the benefit of exporting the SQL database to a snapshot file?

A: The most important benefit of exporting the SQL database to a snapshot file is a smaller amount of time for the comparison process and smaller snapshot file than the database itself.

Q: Can I send the snapshot file to one of my coworkers?

A: Yes. You can send the snapshot file to your coworkers, who can use it in his version of ApexSQL Diff, as a comparison data source.

Q: What format types are supported for exporting?

A: Data source can be exported in three different formats: 1) snapshot, 2) script folder, and 3) source control.

Q: Which of these three export types (snapshot, script folder, source control) can be synchronized when used as a destination data source?

A: Script folder and source control can be synchronized when used as a destination data source. The snapshot file cannot be synchronized, as it basically represents the current state of the database when it was created and it can be only used to create a synchronization script.

Q: Is it possible to export more data sources at once?

A: No. You can only export a one-by-one data source.