Batch for automatic restore of SQL data to a backup server with 1 click restore

How it works
1

I want to make a batch or cmd file to automatically select the latest file in the directory D:\Romexis_Bilder\romexis_SQL_Backup. These are ZIP SQL backup files that are generated two times daily in the format yymmddhhmm.zip, e.g Romexis_db201805271200.zip on a server running Windows 2016 Server. A copy o the Data is transfered over VPN to a NAS, and should be restored auomatically to a server 2012R2 that is our back up server running the same database.

The latest added file to the directory (result of FOR /F) should then be used in SQL RESTORE (backup and ftp program Windows).
Thanks to MOFI from stack overflow and some modification the following batch was created:

@echo off
set “BackupFolder=D:\Romexis_Bilder\romexis_SQL_Backup”
for /F “eol=| delims=” %I in (‘dir “%BackupFolder%\Romexis_db*.zip” /A-D-H /B /O:D 2^>nul’) do set “NewestFile=%I” & goto DatabaseRestore
echo ERROR: Could not find any *.zip backup file in folder:
echo “%BackupFolder%”
echo/
pause
goto :EOF

:DatabaseRestore
cd /D “%ProgramFiles(x86)%\Pranas.NET\SQLBackupAndFTP”
SqlRestore.exe “%BackupFolder%%NewestFile%” -db Romexis_db -srv .\ROMEXIS -pwd password
echo/
pause

This Works!
Now the GUI of One-Click SQL Restore opens with the newest *zip . The only thing that I still need to get out, is the syntax in command prompt for the restore, now i still need to click on the restore button of the GUI. Another Problem what is the command to disconnect the database in One-click SQL restore?
An if the ZIP is password protected how is the syntax in One click if supported?
Original post : for loop - How to find and select newest file in a directory and restore with SQLRestore? - Stack Overflow

2

Hello,

Currently, we are working to improve the restore option in the SQLBackupAndFTP. It will supports the searching of the last backup in the directory and allows launching through the command line

3

Hi Alexander until now i could make a workaround for zip (7ZIP) protected files also in my batch. (Automated SQL DATABASE restore with oneclickSQLrestore):
@echo off
set yourZipPassword=yourpw
set yourFolderPath=yourfolderpath
for /R “%yourFolderPath%” %%I in (“ZaWin.zip”) do (
“C:\Program Files\7-Zip\7z.exe” x -p%yourZipPassword% -y -o"%%~dpI" “%%~fI”)
cd C:\Program Files (x86)\Pranas.NET\SQLRestore
SqlRestore D:\DB_SQL\Backup\ZaWin.bak -db ZaWin -srv .\SQL_ZAWIN -pwd password

But the only problem now ist to know the syntax for the RESTORE button of the GUI in command line. Now i still need to click on it, with the option -run the gui wont start but will it restore?

Thanks in advance

4

Thanks for the details. One-Click Restore is a very simple tool and we do not have any plans to improve it. We plan to add change to the restore option in the future releases of SQLBackupAndFTP.

Also, you can check our another product - SqlBak https://sqlbak.com it allows to backup SQL Server databases and restores backups on another computer.

Please find more details at https://sqlbak.com/blog/restore-sql-server-backup/

5

Hello,

We are happy to tell you that we have released SQLBackupAndFTP version 11.7.0 which has “Restore” option to recover backups from any source. Here https://sqlbackupandftp.com/blog/how-to-restore-sql-server-backups is a short tutorial on how to create a restore job.