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 tool ApexSQL 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:

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

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):

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.

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 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:

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:

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

https://blog.apexsql.com/wp-content/uploads/2013/08/SubfolderName1.png

By pressing the three dots button, in the Subfolder name field, a new folder or a completely new folder structure can be created under the main script folder:

https://blog.apexsql.com/wp-content/uploads/2013/08/SelectFolder1x.png

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 to 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 check boxes were checked, the following folder structure should be created:

https://blog.apexsql.com/wp-content/uploads/2013/08/DataTypes1x.png

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:

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:

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

Learn more about setting up source control wizard from 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 SQL database to a snapshot file?

A: The most important benefit of exporting SQL database to a snapshot file is smaller amount of time for the comparison process and smaller snapshot file than 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 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 one-by-one data source.