How can I create a cron job to backup my Cloud Sites MSSQL database?


NOTE: This article is written for our Cloud Sites Control Panel. You can access this interface from the Cloud Control Panel by clicking your username in the upper-right of the control panel and selecting "Cloud Sites Control Panel".

Microsoft SQL server has a built-in backup feature that you can run as a query or a stored procedure. The output file can be configured to write to an FTP folder. In this tutorial we will examine how to:

I. Create the Folder

1) First, you need to create a folder to store the backup in your FTP application.

Note: You will want to connect your FTP client to the account that has the database you want to back up.

2) Once conected, create a folder inside the /web directory called "backups". The path to the folder should look similar to this

/123456/www.domain.com/web/backups/

3) Next, set folder permissions.  In your FTP application right click the folder you just created and set its permissions to 766 (Please understand you are allowing all users write permissions for the folder). If your FTP client doesn't do this, we recommend using a client that does, such as: FileZilla, CoffeeCup FTP, or FireFTP (a firefox plugin).

IMPORTANT: If not done so already, you will need to set the permissions to 751 on the /www.domain.com and /web folders for this specific example to work.

II. Create the Stored Procedure

You will also need to create a stored procedure that performs the backup with an input parameter for the filename. This can be accomplished by connecting to your MSSQL database using the client of your choice, and running a query similar to this. In this example, the stored procedure will be named FullBackup

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FullBackup]
 @FileName nvarchar(256)
AS
BEGIN

SET NOCOUNT ON;

    BACKUP DATABASE [123456_YourDatabase] TO DISK = @FileName WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

END

III. Create the webpage

Next, you will need to create a web page that has code to execute the stored procedure. You can use any language we support on WIndows, such as ASP Classic or ASP.NET. For something this simple I prefer ASP Classic. That way there is no .dll to deal with and no application restart needed. We will create a new asp page and call it backupdb.asp.

Note: You will need to edit the location path and sql connection string. The contents of the file are as follows.

<%@LANGUAGE="VBScript" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>

</head>

<body>

<%
    dim thismonth, thisday, thisyear, location, filename, ver, extention, abolutepath

thismonth= datepart("m", now())
thisday=datepart("d", now())
thisyear=datepart("yyyy",now())

location="\\fs1-n03\stor1wc1dfw\838249\2382489\www.yoursite.com\backups\"
filename="dbBackup-" & thismonth & "-" & thisday & "-" & thisyear & "_"

ver=1
extention=".bak"

absolutepath=location & filename & ver & extention

    set fso = Server.CreateObject("Scripting.FileSystemObject")

while (fso.FileExists(absolutepath)=True)
ver=ver+1
absolutepath=location & filename & ver & extention
wend

'pre Create the file Dim fs,f Set fs=Server.CreateObject("Scripting.FileSystemObject") Set f=fs.CreateTextFile(absolutepath) set f=nothing set fs=nothing 'finished creating the file Set cn = Server.CreateObject("ADODB.Connection") cn.connectionString= "Provider=SQLOLEDB;Server=mssql12xx.wc1;Database=123456_YourDatabase;Uid=123456_YourUsername; Pwd=Yourpassword;" cn.open
Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandText = "FullBackup" cmd.CommandType = 4 'adCmdStoredProc cmd.Parameters.Refresh cmd.Parameters(1) = absolutepath cmd.Execute cn.close %> Execution complete: Filename=<%= filename & ver & extention%> </body> </html>

IV. Schedule the Cron Job

Finally, schedule a Cron Job to call the web page.

  1. Access your Classic Cloud Control Panel and go to the features tab of the site with the database.
  2. Choose "HTTP" as the language.
  3. Enter the URL to the asp script and your email in the corresponding fields.
  4. Schedule the job for a daily run at an off hour. Usually this is a late night period, such as 1:00 AM, when most sites see less traffic.

These scripts were created by one of our forum users. If you need further assistance, please contact your developer.



Was this content helpful?




© 2011-2013 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER