Scheduling SQL Backup & Shrinking Using the Windows Task Scheduler
This article explains how to schedule SQL database backups and log file shrinking via script using the Windows Task Scheduler.
Overview
It is highly recommended that users regularly backup their Netaphor SiteAudit™ database and shrink the associated log file. Backing up the database ensures that it can be restored in the event of data loss or corruption. In fact, it is recommended to keep a backup of the database on another medium in the event of a hardware failure. Shrinking the log file reduces its size.
There are chiefly two ways to create a SiteAudit database backup and shrink a log file. The preferred method is via Microsoft SQL Management Studio to schedule the task; however, the SQL Agent must be installed and running in order to run the scripts that do the backups. The SQL Agent is not available if using Microsoft SQL Express, therefore the second way to automate a backup is needed. This requires one to create a batch file that is periodically executed to perform the backup and shrinking. This document explains how to create and execute these scripts.
The scripts used in this article are provided as samples and may not be suitable for all situations. These samples are intended to run on the same machine as SQL EXPRESS and assumes the logged-in user has permission to perform backups and log file shrinking. For more information on the SQLCMD command line options, visit http://msdn.microsoft.com/en-us/library/ms162773.aspx
Scheduling the Database Backup & Log File Shrinking Using Windows Task Scheduler
The following MSDN article explains how to use the Windows Task Scheduler to schedule tasks. The goal is to schedule the a batch file to run periodically.This batch file in turn runs a SQL script that will backup the database and then shrink the log file. It is recommended that periodic database backups and shrinking of the log file is performed. Depending on your environment and the number of printers in your Inventory, you may want to schedule this to occur daily or weekly.
Content of Backupshrink.SQL
A SQL script is needed to backup the SiteAudit database and to subsequently shrink the log file. SQLCMD is used to run the script so it is necessary that this file be run on the same machine where SQL Express is installed.
The script below does two things:
- Backs up the database- For this, it needs to know the name of the database to backup and full path and file name where the backup will be stored.
- Shrinks the log file- For this, it needs to know the size in MB that you want to shrink the file to and it tries to shrink to this size if it can.
The SQL script has been commented on to show where one can replace data with information to backup a database. This script is included at the bottom of this page and can be downloaded to use or modify. Note that the script is saved with the .sql extension indicating that it is a SQL script.
-- This script is used to backup a SQL database and shrink the log file
-- You must modify this file to indicate which database you want to back up
-- and where you want the backup to be placed. Look for *** below for
-- places where changes are required
-- NOTE: This script must run on the same computer as the SQL server
-- *** Replace YourDatabaseName with the name of your SiteAudit database ***
USE YourDatabaseName
DECLARE @backuplocation AS NVARCHAR(256)
DECLARE @logfile AS NVARCHAR(256)
DECLARE @shrinktosize AS INT
-- *** Enter the name and location for the database backup. Verify the path exists. ***
SET @backuplocation = 'C:\Databases\Backups\SiteAudit_Backup.bak'
-- Tries to shrink the database to 20 MB
SET @shrinktosize = 20
SELECT @logfile=name from sys.database_files WHERE type = 1
-- *** Replace YourDatabaseName with the name of your SiteAudit database ***
BACKUP DATABASE YourDatabaseName TO DISK = @backuplocation WITH INIT;
-- Shrink the log file
EXECUTE('DBCC SHRINKFILE(' + @logfile + ', ' + @shrinktosize + ')')
Content of Backupshrink.BAT
Once the SQL script file has been created, then it is a simple matter of calling the script from a batch file. The batch file script below shows how to run a SQL script such as the one created in the step above. The script includes a comment and one line that executes SQLCMD.
REM Run SQL file to backup database and shrink log file
SQLCMD -S . -i "backupshrink.sql"
For more information about using SQLCMD, visit http://msdn.microsoft.com/en-us/library/ms162773.aspx
