Leapwork Enterprise - SQL Database (Backup & Restore or Using Script Method)

Introduction

The backup and restore feature is an essential safeguard for protecting critical data stored in our Leapwork databases. To minimize the risk of catastrophic data loss and to preserve data configurations, we should perform a backup of our databases regularly. A well-planned backup and restoration strategy for our databases can provide timely protection against unforeseen situations like:

• Media failures
• User errors
• Hardware failures
• Natural disasters

What needs to be included in a backup?

For an enterprise license, Leapwork Controller holds all data, such as flows, sub-flows, images, schedules, and locators, etc., in the form of multiple SQL database files. The below steps are listed to demonstrate how to perform SQL database backup & restore for a SQL server using a script (bat) file.

Prerequisites: 

We will need to know the following information before proceeding.

• SQL Server name

• Service instance name for SQL Server (usually MSSQLSERVER or SQLEXPRESS)

• Name of the database to be backup/restore.

• Username and password with backup & restore privileges in SQL Server

• Path to an existing local folder to which SQL has to read/write privileges.

• Path to an existing remote folder to which the file will be moved.

Steps to conduct a Backup:

Step 1. Open Notepad (or any text editor).

Step 2. Copy the code from the backup script and paste it into the text editor.

Step 3. Save the file with an extension .bat to ensure that it is a batch file and not a plain text file. 

Note:  The icon should change to a command prompt.  If not, the file extension is probably .bat.txt, which means that we will need to display file extensions to remove the .txt extension.

Step 4. To perform the backup, double-click on the new batch file.  We will see it perform some queries and then it will export the data to a file.  

Note:  The amount of time to perform the backup may take a while depending on the size of the database.

Backup Script Snippet

@echo off

cls

rem Set these variables to the desired values

set SqlServer=SERVER_NAME (The name ofyour SQL server) 

set InstanceName=MSSQLSERVER (The name of the SQL instance) 

set Username=SQL_USERNAME (The username with backup rights) 

set Password=SQL_PASSWORD (The password for the above user) 

set Database=DATABASE_NAME (The database to be backed up) 

set LocalFolder=C:\Temp (Temporary local folder to hold backup, no quotes) 

set NetworkFolder="\\remotecomputer\folder" (URL to permanent backup location, include quotes)




echo Getting current date and time...
echo.
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select ltrim(convert(date, getdate()))" -h -1') do set CurrentDate=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(hour, getdate())), 2)" -h -1') do set CurrentHour=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(minute, getdate())), 2)" -h -1') do set CurrentMinute=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(second, getdate())), 2)" -h -1') do set CurrentSecond=%%a

echo.
echo Backing up database to %LocalFolder%
echo.
SqlCmd -S %SqlServer% -U %Username% -P %Password% -Q "Backup Database %Database% To Disk='%LocalFolder%\%Database%-%CurrentDate%_%CurrentHour%%CurrentMinute%%CurrentSecond%.bak'"

echo.
echo.
echo Copying backup to %NetworkFolder%
echo.
move /Y %LocalFolder%\%Database%-*.bak %NetworkFolder%

After making sure that the batch file works as expected, we can add it as a scheduled task in the "Windows "Task Scheduler" to have it run daily at the desired time.

Auto Backup Procedure using Task Scheduler

Step 1. Click on the Windows Start button, search and open Task Scheduler

Step 2. Click on Action - Create Task

Step 3. In General, Name the task (ex. SQL Backup).

Step 4. Click on Triggers - New and define the Schedule for executing the script.

Step 5. Click Actions-New, in action, choose to start a program, and in Program/Script browse the script we want to execute.

Step 6. This will create a task in Task Scheduler which will execute as per the scheduled defined.

 

Restoration Procedure:

 

Step 1. Open Notepad (or any text editor).

Step 2. Copy the code from the backup script and paste it into the text editor.

Step 3. Save the file with an extension .bat to ensure that it is a batch file and not a plain text file. 

Note:  The icon should change to a command prompt.  If not, the file extension is probably .bat.txt, which means that we will need to display file extensions to remove the .txt extension.

Step 4. To perform the backup, double-click on the new batch file.  We will see it perform some queries and then it will export the data to a file.  

Note:  The amount of time to perform the backup may take a while depending on the size of the database.

Restore Script Snippet:

@echo off

cls

echo.
SQLCMD -S %SQLSERVERNAME% -U %USERNAME% -P %PASSWORD% -d master -q "Restore Database %My DB% From Disk='Path to My DB'"

 

If you have any questions, contact our Priority Support.