Backing up QuoteWerks SQL Server database
The following provides step-by-step screenshots to demonstrate how to set up a scheduled SQL Server backup for a QuoteWerks database (or, indeed, any other type of SQL Server database) hosted on the Express database engine.
The example scripts and screenshots demonstrate backing up a database called “QuoteWerks” to the “D:\SQL Server Data\Backup\” folder on the server hosting the SQL Server database by running the SQL Server query file “QuoteWerksBackup.sql” to a file called “QuoteWerks_YYYYMMDD.bak” using a Windows Scheduled Task. This can of course be modified to backup ‘any’ SQL Server Express database to ‘any’ file by modifying the appropriate parameters.
Content for “QuoteWerksBackup.sql” is as follows:
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = 'D:\SQL Server Data\Backup\' -- specify filename format SET @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- specify full backup filename SET @fileName = @path + 'QuoteWerks_' + @fileDate + '.bak' -- backup database BACKUP DATABASE QuoteWerks TO DISK = @fileName
This is the finalised QuoteWerks database backup task in the Windows Task Scheduler Library:
To set up this Task initially, click the Create Task option in the Windows Task Scheduler Library and set the details as follows:
Name: QuoteWerks SQL Server Backup Description: Backs up the QuoteWerks SQL Server database
Set Trigger and frequency as required. The example here shows a “Daily” backup occurring at 2am each night:
A single Action is required – details below – note that the parameters may vary depending on the version of SQL Server being executed (for the path of the SQLCMD executable) and the location of the SQL Server Query file.
Action: Start a program Program/script: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE Or: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\150\Tools\Binn\SQLCMD.EXE Add arguments: -S (local)\SQLEXPRESS -U sa -P QuoteWerks -i "D:\SQL Server Data\Backup\QuoteWerksBackup.sql"
Conditions may be left as default or modified as per your requirements:
Settings may be left as default or modified as per your requirements:
The following shows the resultant backup file saved to the specified directory and filename. One file will be created each time the backup process runs:
Looking at the Task History: a return code of 0 (zero) indicates that the process has been executed successfully: