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: