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:

Task Scheduled 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

Task Properties - General

 

Set Trigger and frequency as required. The example here shows a “Daily” backup occurring at 2am each night:

Task Properties - Triggers

 

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.

Task Properties - Actions

 

Action: Start a program
Program/script: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
Add arguments: -S (local)\SQLEXPRESS -U sa -P QuoteWerks -i "D:\SQL Server Data\Backup\QuoteWerksBackup.sql"

Task Properties

 

Conditions may be left as default or modified as per your requirements:

Task Properties Conditions

 

Settings may be left as default or modified as per your requirements:

Task Properties Settings

 

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:

File Explorer

 

Looking at the Task History: a return code of 0 (zero) indicates that the process has been executed successfully:

Tags: , , ,

Leave a Reply

You must be logged in to post a comment.