How to Restore SQL Server Backups

The conventional wisdom says: “Do not care about the backup of the database but about its recovery.” True, backups are created to be recovered, if necessary. In this article, we are going to see how to recover SQL Server database from a backup copy using SqlBackupAndFtp.

If you create backups with SqlBackupAndFtp, perhaps, you are already familiar with “History & restore” panel, which contains the entire backup history and you can download any backup or recover them from it:

All that works like a clock if the program was set to create backups on its own and is aware of the availability of such. But what if you need to recover an arbitrary backup that was created in another location? Previously that was impossible, but starting with version 11.7.0 there’s an option to create the so-called “restore jobs”, which allow recovering backups from any source.

Let’s see how it works.

To create such a job just click “+” from “Jobs” panel to get job type menu:

and select “Add New Restore Job”:

As it is seen in the picture, you can restore a backup from any storage and recover it to any SQL Server and/or database. This provides more flexibility in backup recovery in contrast to the standard method described at the beginning.

In addition, at the top of the drop-down list from “Take backups from” you will notice “File” option, which allows recovering the database from a specific file on a local computer (no need to connect to any storage).

As soon as you connect to a storage, it will be scanned and you get a chance to select any recovery point from the ones found in that particular storage:

The program takes into account all types of backups that have been found (Full, Differential and Transaction Log) and are listed with date and time in reverse chronological order. When selecting a recovery point SqlBackupAndFtp fully adheres to the recovery sequence of different types of backups: first the full backup is going to be recovered,  then the last differential backup, and then all transaction log backups up to the selected recovery point.

It should further be noted that in a single restore job you can recover multiple databases simultaneously by adding them to “Select databases to restore” (or clicking “add all” to add all databases found in the storage).

After you’ve completed the setup process you can run the job by clicking “Run Now”. A detailed Log of Restore Job will be written and “History” panel will contain the job execution record:

Finally, I would like to mention that when you select a source of backup files, the program scans through the files looking for the following file types:

{DatabaseName}{YYYY}{MM}{DD}{hh}{mm}[log|diff].[sql|bacpac|7z|zip|bak]

Subsequently, these files become recovery points. For instance, the recovery points in the above example came from the following files:

This means SqlBackupAndFtp can recover any backup created through “CREATE BACKUP” command, SQL script or a .bacpac container, as soon as it’s file name comply with the above format. This makes the utility flexible enough to recover backups created manually or by other software.