SQL Zone is brought to you in partnership with:

I was born in Bulgaria. My immediate family and I relocated to Syracuse, NY in 1995. I completed high school in Syracuse, and then continued my education at Alfred University. My major at Alfred University was Computer Science. I also obtained a minor in Management Information Systems (MIS) to bridge the gap between technology and the business world. One of my future goals is to extend that bridge by obtaining a Master's degree in Business Administration. Boyan is a DZone MVB and is not an employee of DZone and has posted 27 posts at DZone. You can read more from them at their website. View Full User Profile

SQL Server Scheduled Backups with NAnt

11.07.2008
| 6711 views |
  • submit to reddit

When installing and using production web applications, backup is always a must. I have written about SQL Server Backup before and this post would expend on that. The main goal here is to create a solution that periodically backs up your database. To do this, we’ll use the backup script from the previous post, employ NAnt to execute the script and zip the created backup. Last, we’ll schedule the execution of the NAnt build script through the Windows Task Scheduler or the “at” command. This procedure can backup a local or a remote SQL Server (as long as the remote server is on the same network).

Prerequisites

  • Installed and configured version of NAnt (see Getting Started with NAnt - .NET Build Tool)
  • Installed NAntContrib tasks (for the SQL task, see NAntContrib on SourceForge)
  • Task Scheduler Service enabled in Control Panel/Admin Tools/Services
  • SQL login with “dbo” rights to the database (to install the backupDatabase procedure)
  • SQL login that has “public” rights to the database (so it can execute the backup)

Backup SQL Procedure

The script below with create the SQL server stored procedure to create a backup file of a given database. It takes two parameters:

  1. databaseName – The SQL server database to be backed up
  2. backupDirectory – The directory where the backup file will be created

You need to execute it against the database that you will be backing up and give the “public” role execution permissions to the procedure.

Code

exec dbo.sp_executesql @statement = N'
/*
Created:
07.18.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
None

Usage:
exec dbo.backupDatabase ''ensembleVideo'', ''C:\Temp''

Parameters:
@databaseName - varchar
- The database to backup

@backupDirectory - varchar
- The path to where the database should be
backed up. This should be an existing directory on
the SQL Server where the database is located

Description:
Backsup a given database to the specified directory
*/
create procedure dbo.backupDatabase
@databaseName varchar(100),
@backupDirectory varchar(1000)
as
declare @backupFileName varchar(100),
@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100),
@execSql varchar(1000)

-- If the backup directory does not end with ''\'', append one
if charindex(''\'', reverse(@backupDirectory)) > 1
set @backupDirectory = @backupDirectory + ''\''

-- Create the backup file name based on the backup directory, the database name and today''s date
set @backupFileName = @backupDirectory + @databaseName + ''-'' + replace(convert(varchar, getdate(), 110), ''-'', ''.'') + ''.bak''

set @execSql = ''
backup database ['' + @databaseName + '']
to disk = '''''' + @backupFileName + ''''''
with
noformat,
noinit,
name = '''''' + @databaseName + '' backup'''',
norewind,
nounload,
skip''

exec(@execSql)'
go

NAnt Backup Script

The script is somewhat complex. Here are the list of features:

  • Backup a local SQL Server instance or a SQL Server instance on the same network. The key is that the file system of the networked instance must be available through UNC shares to the machine executing the backup script.
  • Use a specified connection string or read the connection string from a .NET (or other type of XML)configuration file.

I will not dive into the script itself since it's properties and flow is documented fairly well.

Code

<project name="backupDatabase" default="run" xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
<!-- Set the name of the backup file that will be created -->
<property name="zipFileName" value="databaseBackup" overwrite="false" />

<!-- Set the path where the backup file will be finally stored -->
<property name="localBackupDirectory" value="C:\Temp" overwrite="false" />

<!-- Set the local path (relative to the SQL Server instance) where SQL Server will write the backup file -->
<!-- This has to always be a local path since SQL server can't write to network paths -->
<property name="sqlServerLocalBackupDirectory" value="C:\Temp" overwrite="false" />

<!-- Set the UNC path to the above "sqlServerLocalBackupDirectory" local path -->
<!-- This is only needed if you are backing up SQL server on the network -->
<property name="sqlServerUNCBackupDirectory" value="\\beehive\windows$\Temp" overwrite="false" />
<!-- For backing up a local SQL Server instance, commented the first "sqlServerUNCBackupDirectory" property -->
<!-- and uncommented the one below this line, or set the "sqlServerUNCBackupDirectory" property to "" -->
<!--<property name="sqlServerUNCBackupDirectory" value="" overwrite="false" />-->

<!-- Set the .NET connection string for connecting to the database -->
<!-- This setting always takes precedence over the config file below -->
<!-- To use a config file instead, set this property to "" (like so value="") -->
<property name="connectionString" value="Data Source=beehive\sql2005;Initial Catalog=ensembleVideo;User ID=test;Password=test;" overwrite="false" />

<!-- Alternatively set the path to where the build script should get the connection string from -->
<!-- This is usually a app.config or connectionString.config file -->
<property name="connectionStringConfigFilePath" value="C:\Inetpub\wwwroot\myApp\config\connectionStrings.config" overwrite="false" />

<!-- Set the XPath expression that will be used to grab the connection string from the config file -->
<property name="connectionStringXPath" value="/connectionStrings/add[@name = 'sqlServerConnection']/@connectionString" overwrite="false" />

<!-- Set the regular expression that's needed to get the database name from the connection string -->
<property name="getDatabaseNameFromConnecionStringRegEx" value="Initial Catalog=(?'databaseName'.*?);" overwrite="false" />

<property name="todaysDate" value="${string::substring(string::replace(datetime::to-string(datetime::now()), '/', '.'), 0, 10)}" />
<property name="todaysLocalBackupDirectory" value="${path::combine(localBackupDirectory, todaysDate)}" />

<target name="run">
<!-- If the SQL Server UNC directory was not specified and the local backup directory exists -->
<if test="${string::get-length(sqlServerUNCBackupDirectory) == 0 and directory::exists(localBackupDirectory)}">
<!-- This is a back of a local SQL Server instance -->
<mkdir dir="${todaysLocalBackupDirectory}" />

<property name="todaysSqlServerLocalBackupDirectory" value="${todaysLocalBackupDirectory}" />
<property name="todaysSqlServerUNCBackupDirectory" value="${todaysLocalBackupDirectory}" />

<property name="localSqlServer" value="true" />
</if>

<if test="${string::get-length(sqlServerUNCBackupDirectory) > 0 and directory::exists(sqlServerUNCBackupDirectory) }" >
<property name="todaysSqlServerLocalBackupDirectory" value="${path::combine(sqlServerLocalBackupDirectory, todaysDate)}" />
<property name="todaysSqlServerUNCBackupDirectory" value="${path::combine(sqlServerUNCBackupDirectory, todaysDate)}" />

<!-- This is a back of a networked SQL Server instance -->
<mkdir dir="${todaysSqlServerUNCBackupDirectory}" />

<property name="localSqlServer" value="false" />
</if>

<!-- If the connection string is empty,
the connection string file exists and the XPath to find the connection string is not empty -->
<if test="${string::get-length(connectionString) == 0 and file::exists(connectionStringConfigFilePath)
and string::get-length(connectionStringXPath) > 0}" >
<!-- Get the connection string to the database from the connection string config file -->
<xmlpeek
file="${connectionStringConfigFilePath}"
xpath="${connectionStringXPath}"
property="connectionString">
</xmlpeek>
</if>

<!-- If the connectionString property is not empty and the regular expression to get the database name is not empty -->
<if test="${string::get-length(connectionString) > 0 and string::get-length(getDatabaseNameFromConnecionStringRegEx) > 0}">
<!-- Get the database name from the connection string -->
<regex pattern="${getDatabaseNameFromConnecionStringRegEx}" input="${connectionString}" />

<!-- Execute the stored procedure to bckup the database -->
<sql connstring="Provider=SQLOLEDB;${connectionString}" transaction="false" delimiter=";" delimstyle="Normal">
exec dbo.backupDatabase '${databaseName}', '${todaysSqlServerLocalBackupDirectory}';
</sql>

<!-- Zip up the created databse backup file -->
<zip zipfile="${path::combine(todaysSqlServerUNCBackupDirectory, zipFileName + '-' + todaysDate + '.zip')}" ziplevel="9">
<fileset basedir="${todaysSqlServerUNCBackupDirectory}">
<exclude name="**/*.zip" />
<include name="*.*" />
</fileset>
</zip>

<!-- Delete all other files in the today's backup directory except for the created zip files -->
<delete>
<fileset basedir="${todaysSqlServerUNCBackupDirectory}">
<exclude name="**/*.zip" />
<include name="*.*" />
</fileset>
</delete>

<!-- Move the contents of today's backup directory to the local backup directory -->
<move todir="${localBackupDirectory}">
<fileset basedir="${todaysSqlServerUNCBackupDirectory}">
<include name="*.*" />
</fileset>
</move>

<!-- Delete the "todays" directories -->
<if test="${localSqlServer}">
<delete dir="${todaysLocalBackupDirectory}" />
</if>

<if test="${not localSqlServer}">
<delete dir="${todaysSqlServerUNCBackupDirectory}" />
</if>
</if>
</target>
</project>

Setting Up Backup Script and Scheduling

Review the build script and set following properties to match your setup:
  • zipFileName – The name of the zip file that will be created for the database backup
  • localBackupDirectory – The local directory where the backup will be stored
  • sqlServerLocalBackupDirectory - The local path (relative to the SQL Server instance) where SQL Server will write the backup file. This has to always be a local path since SQL server can't write to network paths.
  • sqlServerUNCBackupDirectory – The UNC path to the above "sqlServerLocalBackupDirectory" local path. This is only needed if you are backing up SQL server on the network. For backing up a local SQL Server instance, set this "”.
  • connectionString - The .NET connection string for connecting to the database. This setting always takes precedence over the “connectionStringConfigFilePath“ setting. To use a configuration file instead, set this property to "".
  • connectionStringConfigFilePath - Alternatively set the path to where the build script should get the connection string from. This is usually a app.config or connectionString.config file.
  • connectionStringXPath - The XPath expression that will be used to grab the connection string from the configuration file.
  • getDatabaseNameFromConnecionStringRegEx - The regular expression that's needed to get the database name from the connection string.


The next step is to create the schedule with either the Task Scheduler or with “at” command.

To use the Task Scheduler:

  1. Create a .bat file with the following: “driveLetter:\path\to\nant.exe /f:pathToNAntBackupScript.build” and of course replace that with the actual path to nant.exe and to the NAnt build script you got here.
  2. Go to Control Panel / Scheduled Tasks / Add Scheduled Task
  3. Browse for the .bat file you created in #1
  4. Configure the schedule run as often as you would like

To use the “at” command:

  1. Do the same as #1 above.
  2. Open a command prompt and execute the “at” command:
    ”at 23:00 /every:M,T,W,Th,F pathToBatFileFromStep1.bat”

    That will schedule the task to execute every day of the week at 11:00pm. You can get more info on the “at” command from How To Use the AT Command to Schedule Tasks.

Bonus

You don’t have to hard code the values in the NAnt build script. You can pass them from the command line. In that manner you can reuse the same script for different database. You simply need to call the script with –D:propertyName=”value" like so:

path\to\nant.exe /f:pathToNAntBackupScript.build –D:zipFileName="myDatabase" –D:localBackupDirectory="D:\Temp"

Downloads

http://tech-cats.net/blog/downloads/sql/procedure-dbo.backupDatabase.txt
http://tech-cats.net/blog/nantScripts/backupDatabase.build
http://tech-cats.net/blog/nantScripts/backupDatabase.bat

References
Published at DZone with permission of Boyan Kostadinov, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)