# 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 the SQL Server instance used by Leapwork Enterprise, for example `MSSQLSERVER` for a default instance, or the configured named instance.
- 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 of your 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 schedule 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.

**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](mailto:prioritysupport@leapwork.com).**