How to create a SQL database build script directly from a backup (4293042)

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.

Also, it’s possible to export the complete database to a script folder with a click on the Export data source button in the New project window:

Firstly, it is required to specify a script folder as a destination data source in order to synchronize it with a SQL backup as a source. ApexSQL Diff 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:

As seen in the screen shot above, precise information about the creation time and date were included for each differential backup as well as the full backup. If a differential backup is checked, 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.

For a script folder as the destination source, the targeted SQL Server version can be set in order to make the resulting scripts to be compatible with:

If source and destination SQL Server versions are different, a list of warnings with information about unsupported items can be seen during the synchronization process.

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:

When working with a script folder, ApexSQL Diff 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)

Once the data sources and everything else is set, click the Compare button. Once the comparison process is finished, compared objects will be shown in the Results grid. If a database backup is compared against an empty script folder, all objects will be shown as missing – they exist only in source, not in destination data source:

If scripts need to be created for all objects check all of them in the Results grid and click the Synchronize button under the Home tab:

The first two steps in the Synchronization wizard can be skipped, since the synchronization direction won’t be changed and there won’t be any dependent objects since all objects will be synchronized.

In the third step, the Output options step, choose the Synchronize to a script folder action:

In case when a script folder already contains scripts that might be changed during the synchronization process, several pre-sync options can be checked such as Create a snapshot file or to Backup script folder. Also, ApexSQL Diff can Create a differential snapshot that will contain differences only.

In the last step, review Summary and warnings and click the Synchronize button:

As shown above, record of all the scripts can be kept that are 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 Professional edition.

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