|
5.14 Backup and Restore SQL Database (Business Edition ONLY) |
  
|
|
5.14 Backup and Restore SQL Database (Business Edition ONLY) |
|
5.14.1 Overview
Bundled with Latitude are database backup scripts, instructions for automating database backups and database restoration.
The Latitude installer installs the free Express editions of SQL Server and SQL Server Management Studio if you currently do not have them.
5.14.2 Backup
Latitude features its own convenient database backup tool that creates a backup file (<your_database_name>.bak) without the need of using SQL Server Management Studio or any extra software component.
It stores the database backup in <system_drive>:\<Program Files>\Microsoft SQL Server\<your_MSSQL>\MSSQL\Data\<your_database_name>.bak where:
<system_drive> - Where "WINDOWS" folder is installed
<Program Files> - If you are on a 64-bit machine, this will be "Program Files (x86)"
<your_MSSQL> - This varies depending on the SQL Server version you are running. Typically, this is "MSSQL.1".
<your_database_name> - Normally, "LatidataSQL".
When you run this from a workstation, it saves the <your_database_name>.bak on the server's file system and NOT in the workstation.
How to Backup
From the Ribbon -> Click Administration -> Upgrade / Backup
Enter DBSuser password -> click Backup
Click OK
Once the backup is done -> click OK
WARNING: This tool does not keep previous database backup versions, it always refreshes the database backup.
If you need to automate database backups, see "Automated Database Backup".
5.14.3 Automated Database Backup
The following are steps to configure the "Windows Task Scheduler" to backup your Latitude database on a scheduled basis:
Create a batch file named "SQLbackup.bat" and copy the text below up to the line "REM ==== END OF SCRIPT ====" into the batch file:
@echo off
REM ==== BEGIN OF SCRIPT ====
@echo off
set SERVERNAME=<server_instance>
set DATABASENAME=<database_name>
set BACKUPLOCATION=<backup_path>
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c%%a%%b)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set DATESTAMP=%mydate%_%mytime%
set BACKUPFILENAME=%DATABASENAME%_%DATESTAMP%.bak
echo.
sqlcmd -E -S %SERVERNAME% -d master -Q ^
"BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPLOCATION%%BACKUPFILENAME%' WITH INIT, STATS = 10"
REM ==== END OF SCRIPT ====
Replace the following variables and save the batch file. Do not enclose the variable value with single or double quotes:
<server_instance> = Your SQL Server instance, eg. (local)\sqlexpress
<database_name> = Your Latitude database name, eg. LatidataSQL
<backup_path> = The full path to your backup folder. Append backslash (\) at the end of the path if there's none
Run "Task Scheduler"
Open Task Scheduler
Windows2012 - Press "Windows" logo key (bottom-left of the keyboard) -> it should show the "Start" tile screen, if not, press "Windows" key again -> just type "Task Scheduler" -> select "Task Scheduler"
Windows8 - Press "Windows" logo key (bottom-left of the keyboard) -> it should show the "Start" tile screen, if not, press "Windows" key again -> just type "Task Scheduler" -> select "Schedule tasks"
Windows2008 & Windows7 - Go to Orb icon -> in the "Search and program files", type "Task Scheduler" and press Enter
Go to Action -> Create Basic Task...
Enter name = "Backup database" and click "Next"
In the "When do you want the task to start?", select your frequency "Daily, Weekly, etc."
Enter the start time & day recurrence and click "Next".
In the "What action do you want the task to perform?"
Select "Start a program"
Browse to where the "SQLbackup.bat" is located.
Click Next, and then click Finish.
Troubleshooting
The folder for the SQLCMD executable is generally in the Path variables for the server after SQL Server is installed, but if the Path variable does not list this folder, you can find it under <Install location>\90\Tools\Binn (For example: C:\Program Files\Microsoft SQL Server\90\Tools\Binn).
The Windows Task Scheduler service must be running at the time that the job is scheduled to run. Set the startup type for this service as Automatic so that the service will be running even on a restart.
There should be lots of space on the drive to which the backups are being written. We recommend that you clean the old files in the backup folder regularly to make sure that you do not run out of disk space. The script does not contain the logic to clean up old files.
If you are using either the Standard or Enterprise edition, use the SQL Server's "Back Up Database Task (Maintenance Plan)" instead of the "Windows Task Scheduler". See https://msdn.microsoft.com/en-us/library/ms189647(v=sql.90).aspx for more information.
5.14.4 Restore
Latitude runs on a cloud-server (or on-premise client-server) architecture model where database restoration requires exclusive control of the database. The following steps below require the use of SQL Server Management Studio to restore a full database backup.
Before you start, all Latitude users must close all Latitude softwares on their workstations, LRC clients, tablet RDP clients, mobile RDP clients, EmailNotifier server component and LatiWeb. Note, you must perform the database restoration on the database server.
Note, SQL Server Management Studio 2008 R2 and below have slightly different screens compared to SQL Server Management Studio 2012 and later. The steps will be prefixed with SQL2008 and SQL2012 respectively.
1. Run SQL Server Management Studio and select/enter
Server type = Database Engine
Server name = <server_instance>
Authentication = SQL Server Authentication
Login = DBSuser
Password = <DBSuser_password>
Note, Latitude provides you the server credentials: <server_instance>, <database_name> and <DBSuser_password> after the installation done. If you have lost them, contact Latitude support.
2. Right-click "Databases" and select "Restore Database..."
3. Source
SQL2008 - Under "Source for restore" -> Click "From device" -> Click the ellipsis button -> Click Add
SQL2012 - Under "Source" -> Tick "Device" -> Click the ellipsis button -> Click Add
4. Locate the database
Navigate to where the backup file (*.bak) is located -> click the file to select -> click OK -> click OK again
5. Destination
SQL2008 - Under "Destination for restore" - "To database" -> Enter or click the dropdown arrow to select the destination database to restore to
SQL2012 - Under "Destination" - "Database" is automatically filled with the database name from the backup after you've selected the database backup. If necessary, enter or click the dropdown arrow to select the correct destination database to restore to
6. Select backup sets to restore
SQL2008 - Under "Select the backup sets to restore" -> Tick "Restore" column to select the database
SQL2012 - Under "Restore plan" - "Backup sets to restore" -> "Restore" column is automatically ticked
7. Restore options
Go to "Select a page" -> "Options" -> "Restore options" -> Tick "Overwrite the existing database (WITH_REPLACE)"
8. Optionally, relocate the database & log files (*.mdf, *.ldf)
SQL2008 - Under "Restore the database files as" -> Click the ellipsis button -> Select the folder location -> In the "File name", enter "<your_file_name>.<extension>"
<extension> - Use "mdf" if "File Type" column is "Rows Data"
Use "ldf" if "File Type" column is "Log"
SQL2012 - Go to "Select a page" -> "Files" -> Under "Restore database files as" -> Tick "Relocate all files to folder"
Data file folder - Tick the ellipsis button and select the folder where to restore the database file
Log file folder - Tick the ellipsis button and select the folder where to restore the log file
9. Click OK to start database restoration
10. Click OK to finish