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 the SQL Server database from a backup copy using SQLBackupAndFTP.

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

sql server backup history restore
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 the “Jobs” panel to get the job type menu:

new sqlbackupandftp job

and select “Add New Restore Job”:

new sql server 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 the “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 storage, it will be scanned and you get a chance to select any recovery point from the ones found in that particular storage:

sql server database restore points

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 the “History” panel will contain the job execution record:

run sql server database restore

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:

sql server backup 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.

26 thoughts on “How to Restore SQL Server Backups

  1. I have the following error:
    10.11.2018 12:59 Connecting to MySQL Server (phpMyAdmin): 5.7.19 – MySQL Community Server (GPL).
    10.11.2018 12:59 Restoring “smmposte_sh.bak” database from files: “***.bak”
    10.11.2018 13:04 Job execution error: System.InvalidOperationException:
    System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
    Pranas.NET.SqlBak.Job.PhpMySql.Helper.RestoreHelper.CheckExecution(PhpMyAdminToken token, List`1 queryAccum, StringBuilder currentBuilder, String dbName, Boolean force)
    Pranas.NET.SqlBak.Job.PhpMySql.Helper.RestoreHelper.ExecuteRestoreScript(PhpMyAdminToken token, String dbName, String pathToFile, CancellationToken cancellationToken). Error execution script. Computer name: “DESKTOP-ENA8NOR”
    10.11.2018 13:04 Job “Restore Job – 3” finished with “1” errors

    1. Hi,

      Thank you for the issue reporting, this issue is on our side and we’ll fix it.

      Could you please clarify what version of phpMyAdmin do you use?

      Sorry for the inconvenience.

  2. Hi. I’m trying the product, and am trying to restore a remote database to different server. I figured out how to back up the “remote” side of things, which resulted in a .sql file. But I can’t figure out how to restore it. I believe I have to use management studio, but what commands do I need to run to restore a .sql file? Thanks.

  3. This is an old post but I can’t help it but posting my question here. I’ve been searching for solution but I couldn’t find it anywhere. So this is an attempt to restore MSSQL database from Amazon S3. In the Slect databases to restore, the dropdown is empty. What could be the problem?

    1. Hi Michael,

      During the restore process SQLBackupAndFTP looks for the backups with the following mask:

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

      If the backups are not displayed in SQLBackupAndFTP, it means that there are no backups with the appropriate mask. Could you please check it?

  4. I’m having the same problem, I need to restore from a backup on another computer and the same time to restore the error with the computer name.
    I already did the procedure via the command line and the same error occurs.

    What is the solution to make this restoration on a different computer than the backup.

  5. As a developer I regularly restore many full back-ups from production servers to my development environment.

    BUT those database backup file names look like: {DatabaseName}_backup_{YYYY}_{MM}_{DD}_{hh}{mm}{ss}_######.bak

    The restore works perfect when I remove the underscores and anything past the minutes.

    I would buy your program in a heartbeat if it would have a “dumb” setting for my purposes.

    Just the confidence of knowing that I am not accidentally overwriting a production server database is worth it!

  6. I understand and agree with your naming nomenclature for the backup files.

    However if i need to give a particular file generated by me (Not a scheduled job ) a specific
    name how can I achieve this

    Do I have to rename the backupfile or is there a setting to allow give me a
    particular backup file a name i wish

    Olukay

  7. Getting an error when restoring to a new server. Tried running SqlBackupAndFtp as Administrator also.

    11/4/2020 4:59 PM Connecting to: 2017 RTM Express Edition (64-bit). .\SQLEXPRESS.
    11/4/2020 4:59 PM Uncompressing “C:\Temp\link202011040515.zip” file with Internal archiver.
    11/4/2020 4:59 PM Restoring “link” database from files: “link202011040515.bak”.
    11/4/2020 4:59 PM Failed to restore link database. Cannot open backup device ‘C:\Windows\TEMP\SYSTEM\Pranas.NET\SQLBackupAndFTP\backup\284a9826-72f6-45cc-8fc2-303039b25733\link202011040515.bak’. Operating system error 5(Access is denied.).
    RESTORE FILELIST is terminating abnormally.
    11/4/2020 4:59 PM Trying to restore the database from default SQL backup folder (C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup).
    11/4/2020 4:59 PM Job execution error: Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\link202011040515.bak’. Operating system error 5(Access is denied.).
    RESTORE FILELIST is terminating abnormally. Server name: “%SERVERNAME%”.

  8. Dear team,

    With your tool can I safe and restore the whole (user) settings of the SQL Server too? Or must I export and then import manually these settings?

    Thank you. Raimund

    1. Hi Raimund,

      Sorry, but there is no specific option to do it with SQLBackupAndFTP, but if the folder with these settings is stored on your local drive then you can try to backup that folder via the “Backup folders” options.

    1. Hi Matt,

      You can do it at your restore job settings at the “Restore Advanced Settings” window at the “Select databases to restore” section.

    1. Hi Mirelis,

      Sorry, but there is no way to restore a specific folder via SQLBackupAndFTP, but you can do it manually since this is just an archive with the files.

      Thank you!

  9. Hello,

    I get the error “sqlbackupandftp archive does not contain valid sql file” when I try to restore my from file.

    Kindly assist.

    1. Hi Reuben,

      The issue happens due to the fact that the type of connection that you use in your backup job does not correspond to the one you use in the restore job. Could you please check it?

  10. Failed to restore “Test” database. The downloaded file does not contain a valid database backup file.

    I am restoring to a remote location

  11. Do backups need to be in a zip file since directory i have has only .bak files and the application doesn’t seem to load it when i create restore job

Leave a Reply

Your email address will not be published. Required fields are marked *