It is essential that a competent IT professional sets up and monitors regular automated backups of your server's database and data files.

Setting up an off-site backup at least once a day and keeping each day's backup separately (non incremental) is highly recommended.

The licence agreement clearly states that Palace IT cannot be held liable for loss of data, especially if there are no regular backups.

Palace IT does offer a full backup to DropBox Business service which includes at least 3 backups per day, unlimited cloud space (for backups only!), backup monitoring, HIPAA comliance and 120 day version retention of all Stock Tracker files and database. Prices start at £80 + VAT per month (depends on licence count). Permanent installation of TeamViewer Host to allow Palace IT unattended access is required on your server.

Backing up Palace Stock Tracker 5 is completely different to Stock Tracker 4. Both the SQL Server database and server files (operator logs and attachments) must be backed up.

Example of Automated Backup of SQL Server Database, Attachment Files and Operator Logs

SQL Server database files cannot be copied while the database is active/online.
The easiest approach (if using SQL Server Express) is to create a batch file which executes a stored procedure in SQL Server.
The batch file can be executed on a regular basis using Windows Task Scheduler and the resulting file can be copied to a cloud synced folder (e.g. DropBox or OneDrive) where the cloud subscription has version control.

Stored procedures are precompiled scripts that run within SQL Server. They can take parameters, return data, write backups and be called externally.

Stored procedures can be written and added to databases using SQL Server Management Studio.

The following example stored procedure will take a full (non-incremental) backup of the PST5 database and save it to "F:\Dropbox\PST5_Backup\PST5_FULL.BAK". You are welcome to use and modify this code for your own needs. The example output folder and filename are at the bottom (obviously these need to be changed to suit your needs).

-- use master database
USE [master]
GO

-- remove old copy of the stored procedure (if exists)
IF OBJECT_ID('[sp_backupPST5Database]', 'P') IS NOT NULL
DROP PROC [sp_backupPST5Database]
GO

/*
Execute from SSMS using:
EXEC sp_backupPST5Database
*/

-- create the stored procedure
CREATE PROCEDURE [dbo].[sp_backupPST5Database]
AS
BACKUP DATABASE [PST5] TO DISK = N'F:\Dropbox\PST5_Backup\PST5_FULL.BAK' WITH NOFORMAT, INIT, NAME = N'PST5-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Example stored procedure to backup PST5 database

The following example batch file (which must be run on the server running SQL Server) will backup the SQL database and attachment files. As with the stored procedure you are welcome to copy and modify this for your neeeds.

Warning: This example uses Microsoft Robocopy with the /mir parameter which can delete files as well as copy them!

:: Backup SQL Server database directly to F:\Dropbox\PST5_Backup\PST5_FULL.BAK (specified in stored procedure)
sqlcmd.exe -S .\SQLEXPRESS -E -Q "EXEC sp_backupPST5Database"

:: Create destination folder for PST5 files
md "F:\Dropbox\PST5_Backup\"
md "F:\Dropbox\PST5_Backup\ServerFiles"

:: Backup PST5 files using Robocopy
robocopy.exe "C:\ProgramData\Palace IT\Palace Stock Tracker Server" "F:\Dropbox\PST5_Backup\ServerFiles" /mir /tee /v /w:0 /r:0 /log:logs\PST.log

Example batch file to backup database via stored procedure and all data files using Robocopy

Contents

Top