Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – you might need a specific object (table, view, stored procedure, user, etc.) 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 your database is versioned under source control, you can easily find object there but let’s assume you only have SQL backups.

The most logical solution is to take a full database backup, restore it, and get what you need 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, you might be faced with the lack of space on your hard drives. 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 and versioned databases, backups, snapshots, and script folders. 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 your hard drives, nor the need to restore a database backup; at the same time you can create a DDL script 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.

  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. Create a new project

    New project

  4. As a Source data source type, select Backup
  5. Click the Add button and select the full database backup that contains the SQL objects you want to script:

    Choose database backup

  6. Click the Open button
  7. The selected backup, where specific objects are stored, will be automatically checked
  8. As a Destination data source type, select Script folder
  9. Click the Folder button to navigate to the created folder

  10. Select the blank script folder created in step #1
  11. Select the collation and SQL Server version for a DDL script that will be created

    Select SQL Server collation

  12. Click the Compare button

    The objects read from the database backup are shown in the Main grid. As the destination script folder is empty, all objects are shown as missing.

    Compare backup to script folder

  13. Check the check-boxes to select the objects you want to script:

    Select objects for scripting

  14. On the Home tab, click the Synchronize button

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

    Synchronization wizard

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

  17. In the Summary and warnings step, review the order of the actions that will be made and click the Next button:

    Synchronization wizard summary

  18. In the Output options step, select Update scripts in the script folder as an output action to have a DDL script created in the script folder for each of the selected database objects. Each object type has its own sub-folder such as Tables, Functions, etc.

    Select update scripts option

    The database settings are scripted in the DatabaseSettings.xml file, saved in the specified script folder:

    Generated object scripts

    To create a single deployment script that creates all objects selected in the Main grid, select the Create a synchronization script option:

    Create single script
    The script can be executed directly from the Internal editor by clicking F5. If dependent objects are created, you don’t have to worry which object to create 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 you work with SQL objects stored in it.

April 5, 2013