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:
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:
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 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:
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:
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.
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
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.
Do you plan to add the option to schedule Restore jobs as you can for Backups?
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://blog2.sqlbackupandftp.com/run-sqlbackupandftp-v11-command-line
To run a restore job just use the following command:
SqlBak.Job.Cli.exe -runJob -jobName “Job”
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.
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?
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?
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.
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!
Hi Peter,
Thanks for the request, we’ll consider it.
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
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://blog2.sqlbackupandftp.com/customize-backup-file-name
Thank you.
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%”.
Hi, please find more details on how to resolve the issue at https://blog2.sqlbackupandftp.com/cannot-open-backup-device-operating-system-error-5access-is-denied
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
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.
How can we restore to a different database name on the same server?
Hi Matt,
You can do it at your restore job settings at the “Restore Advanced Settings” window at the “Select databases to restore” section.
How I can restore to specific folder?
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!
Hello,
I get the error “sqlbackupandftp archive does not contain valid sql file” when I try to restore my from file.
Kindly assist.
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?
Failed to restore “Test” database. The downloaded file does not contain a valid database backup file.
I am restoring to a remote location
Hi Tolu,
Could you please make sure you use the correct server type during the restore?
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
Hello,
Could you kindly get in touch with our support team for further details at https://sqlbackupandftp.com/support?
Thank you, and we apologize for any inconvenience.