Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – when a specific object (table, view, stored procedure, user, etc.) needs to be recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If a database is versioned under source control, an object can be easily found there, but let’s assume that only SQL backups are available.

The most logical solution is to take a full database backup, restore it, and get what is needed and in most cases, this is the best and quickest solution.

This is a perfectly viable solution for small databases where backups take several hundred of MBs. For large databases, besides significant time needed to restore a backup, the lack of space on hard drives might be encountered as well. The time needed for a database restore depends not only on the backup size, but also on the server configuration and current load. The space needed is at least equal to the size of the full database backup.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects in live databases, backups, snapshots, script folders and source control projects. Although it is most commonly used for comparison and synchronization, its features can be used to achieve some other tasks. Here is a simple trick used for this solution: comparing a data source such as a live database, a database in a source control system, a backup, or a snapshot to a blank destination will actually create a build script that creates SQL objects that exist in the source.

This means that with ApexSQL Diff there is neither the need for additional space on hard drives, nor the need to restore a database backup; at the same time a DDL script can be created for the selected object in the database. If there are any dependent database objects, they can be scripted too and will be created in a proper order.

The process is as following:

1. Create a new folder. This is the folder where individual build scripts for each scripted object will be saved
2. Start ApexSQL Diff
3. Click the New button in the Project management window:

   

4. In the Source panel:
   • Select Backup from the Source drop-down list
   • Click the Add button and navigate to the folder where the backup file resides
   • Select the backup and click the Open button
5. In the Destination panel:
   • Select Script folder from the Destination drop-down list
   • Click the Folder button to navigate to the newly created folder from the 1^st step
   • Select the Default collation and SQL Server version for a DDL script that will be created:

     

6. For additional object filtering prior to the comparison process, check out article on this link
7. Click the Compare button in the bottom-right corner of the New project window
8. The objects read from the database backup are shown in the Results grid. As the destination script folder is empty, all objects are shown as missing:

   

9. Check the check-boxes to select the objects that need to be scripted:

   

10. On the Home tab, click the Synchronize button:

    

11. In the Synchronization direction step of the Synchronization wizard, click the Next button
12. The Dependencies step shows the objects that the objects selected for synchronization depend on. By default, they are all selected:

    

    Uncheck the Include dependent database objects checkbox to avoid updating objects other than the ones selected in the Results grid.

13. In the Output options step, select the Synchronize to script folder as an output action to have a DDL script created in the script folder for each of the selected database objects:

    

    To create a single deployment script that creates all objects selected in the Results grid, select the Create a synchronization script output action:

    

14. In the Summary and warnings step, review the order of the actions that will be made and click the Synchronize or Create script button:

    

15. Depending on which output action was chosen, two buttons can be shown in the last step and the following will be done:
    • The Synchronize button – if the Synchronize to script folder action was chosen, separate sub-folder will be created for each object type has, such as Tables, Functions, etc. The database settings are scripted in the DatabaseSettings.xml file, saved in the specified script folder:

      

• The Create script button – if the Create a synchronization script action was chosen, the created script will be created and it can be executed directly from the Integrated editor, with a click on the F5 key. If dependent objects are created, there is no need to worry which object will be created first; ApexSQL Diff determines the correct object creation order and generates the script:

  

  A database backup doesn’t have to be a black box accessible only when it’s restored to a SQL Server. Database comparison and synchronization tool – ApexSQL Diff can read its content without restoring it and thus help out to work with SQL objects stored in it.