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.

Save a SQL database schema directly from your database backup

Firstly, it is required to specify a script folder as your destination data source in order to synchronize it with a SQL backup as a source. ApexSQL Diff Pro can use only native and natively compressed SQL backups for SQL comparison, but it is possible to use the differential database backups and select the specific point in time to synchronize with the script folder.

Choosing between the different database backups

As seen in the image, precise information about the creation time and date were included for each differential backup as well as the full backup. If you check a differential backup, the full database backup will be automatically loaded. You can check only one differential backup as every one of them contains all changes made since the last full backup.

Selecting the SQL Server version

For a script folder as the destination source, you can set up the targeted SQL Server version that you want the resulting scripts to be compatible with. If your source and destination SQL server versions are different, you might see warning dialogs with information about unsupported items during the synchronization process:

Synchronization wizard - Warnings

A default collation can be set up and used for the database that will be created by the scripts. You can select the preferred one from the dropdown list:

Selecting the preferred collation

When working with a script folder, ApexSQL Diff Pro can even create a SQL database object creation scripts for the following:

  • Objects encrypted using WITH ENCRYPTION
  • Numbered stored procedures
  • Comments
  • CLR assemblies
  • Mapping owners (you can map owners when synchronizing to a script folder)

As you can see, you can keep a record of all the scripts that you run during development from a SQL database backup, simply and easily, without having to be connected to a live database. Using a scripts folder in a comparison process is possible only with ApexSQL Diff Pro version.

Useful links:

Create script to copy database schema and all the objects
Create a Full Database Backup
Batch Script to Backup All Your SQL Server Databases

March 2, 2015