How to recover only objects from SQL Server database backups

Every production database requires maintenance and improvements in order to meet the ever-changing demands. The easiest way to test the new requirements, such as new functionalities and improvements, is to try them on a test database.

So, creating the test base from the backup of the production database would be the easiest way… at least that’s what it looks like. But, is this really the case?

Next question, just how big is this database anyway? A database backup of a production database with a lot of BLOBs could easily reach over 500GB, and it is not unusual for them to swell up well over 1TB. Restoring a database backup that big is time-demanding. Now, we also have to look for free disk space. What can we delete to free up some space for restoring the backup? OK, so we have to back up and archive the data we are going to delete, to free up enough storage space. And we are going to assume that the restoring of production database backup is tested and is hopefully going to work. So we’ve wasted the entire day to make the test database, and we are hoping that nobody will call us to ask for the data we’ve removed to gain enough storage space. But usually, somebody always makes that call.

That’s a very stressful experience that we would like to avoid. And all that we need is an empty database which will be used to test stored procedures, user-defined functions, and triggers.

Back to the future

Today, there are tools available which can be used to get rid of all those time and space-demanding processes. For this scenario, the database comparison tool ApexSQL Diff will be used.

Using ApexSQL Diff will wipe away most of the problems that are encountered using the classical way of restoring backups. In the first place, there is no need to squeeze out the last KBs from our limited disk space. Second, there is no need to wait for a 10-hour long backup restoring process… if we assume that it works. Instead, an empty test database and ApexSQL Diff are going to be used, to easily identify the pertinent objects and their dependencies in the backup, and then synchronize them against the test database. This way, there will be no pressure to look for disk space and no time will be wasted waiting for the backup to recover.

To restore objects from a backup into a test database:

  1. Start ApexSQL Diff
  2. In the Project management dialog click New
  3. To select database backups as the data source:
    • In the Source drop down menu, select Backup
    • Click Add to load database backups into the Backup files list
    • Select the backup or backups that need to be used
  4. To select a database as the data destination:
    • In the Destination drop down menu, select Database
    • Connect to the SQL server
    • Select the database

      Select data source types and details

ApexSQL Diff offers useful options, including the powerful – Object filter

  1. Click Show advanced options and select Object filter
  2. In the list of objects in the Project dialog, exclude individual objects from the comparison by deselecting them

    Exclude individual objects from the comparison by deselecting them

Additionally, it is possible to set up a more advanced filtering:

  • Check-in Use filter to add node for the selected object type
  • Select the object type node. A list of the available objects for the selected object type will be displayed in the grid
  • Select objects to include it in the comparison
  • Click Compare

    Advanced filtering - ApexSQL Diff

Analyzed objects and differences results will be listed in the application’s results grid. It will show each compared object, as well as the DDL script differences details of an object selected in the Results grid, in the Script difference pane.

Results grid - ApexSQL Diff

To select what should be synchronized:

  1. Choose the objects for synchronization
  2. The Object filter in the left pane can be used to additionally eliminate unneeded objects.

Once the project is set up and the objects for synchronization are selected, start the synchronization.

During the synchronization process, in the Dependencies dialog, it can be chosen whether to include dependent objects into the synchronization script.

The Dependencies dialog - ApexSQL Diff

The Summary and warnings step provides a detailed list of the actions that will be taken to synchronize the data sources, along with any potential problems that might occur during synchronization.

Summary and warnings - ApexSQL Diff

After a successful synchronization, the recovery of the objects into our Test database is completed.

Use ApexSQL Diff in order not to waste time on restoring the huge production database backup while desperately trying to find enough free space on hard drives.

April 4, 2013