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.
November 10, 2018 @ 7:57 am
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
November 12, 2018 @ 8:01 am
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.
September 23, 2019 @ 2:10 pm
Do you plan to add the option to schedule Restore jobs as you can for Backups?
September 25, 2019 @ 4:54 am
Thank you for the feature request, we’ve forwarded it to our dev team for consideration. Currently, you can run a restore job via Command Prompt using Windows Scheduler. Here are more details on how to run a backup job via Command Prompt https://sqlbackupandftp.com/blog/run-sqlbackupandftp-v11-command-line
To run a restore job just use the following command:
SqlBak.Job.Cli.exe -runJob -jobName “Job”
September 25, 2019 @ 1:46 pm
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.
April 14, 2020 @ 10:31 pm
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?
April 15, 2020 @ 7:49 am
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?
April 20, 2020 @ 9:36 am
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.
July 11, 2020 @ 2:20 pm
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!
July 14, 2020 @ 5:25 am
Hi Peter,
Thanks for the request, we’ll consider it.
September 22, 2020 @ 6:26 am
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
September 23, 2020 @ 3:11 am
Hi Olukay,
Sure, after a backup is created you can rename it. Note, there can be an issue during a restore because of the file name, the name should be like:
{DatabaseName}{YYYY}{MM}{DD}{hh}{mm}[log|diff].[sql|bacpac|7z|zip|bak]
Also, you can set a customer backup file name during a backup job settings https://sqlbackupandftp.com/blog/customize-backup-file-name
Thank you.
November 4, 2020 @ 12:05 pm
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%”.
November 5, 2020 @ 7:12 am
Hi, please find more details on how to resolve the issue at https://sqlbackupandftp.com/blog/cannot-open-backup-device-operating-system-error-5access-is-denied