Code Examples:

The code provided below is only an example. Your specific website may require different code. As explained in our article Management and Support Levels, we are unable to help you troubleshoot code issues. We recommend speaking with your developer before implementing any scripts.

Microsoft SQL Server has a built-in backup feature that you can run as a query or a stored procedure. You can configure the output file to write into an FTP folder. This tutorial will explain how to accomplish an MSSQL database backup using scripts to write into the folder. While Cloud Sites now offers automatic backups, MSSQL is not included in these backups, you will need to follow the steps below to create a cron to back up your MSSQL databases.

This article will walk you through the steps of creating the backup folder and the cron job, you will:

Create the Folder
Create the Stored Procedure
Create the Web Page
Create the Cron Job

There are some unsupported commands in cron, see the section Unsupported Commands in Cron for the list.

Warning:

It is important to use caution when running unfamiliar scripts. If you are unfamiliar with running scripts for cron jobs, please consult a developer to help you deploy them.

Create the Folder

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

Connect your FTP client to the site in Cloud Sites that has the database attached that you want to back up.
Create a folder called backups inside the /web directory.
In your FTP client, right-click the folder you just created and set its permissions to 766. If you are unable to update permissions in your FTP client, we suggest using FileZilla, CoffeeCup FTP or the Firefox plugin FireFTP.

Permissions

These permissions give all users write permissions for the folder. For more information on permissions, see our article What Are File Permissions?.
Last, you will need to set the permissions on the /web folder on your site to 751.

Create the Stored Procedure

Now you need to create a stored procedure that will perform the backup with an input parameter for the file name. Connect to your MSSQL server database using myLittleAdmin or Windows SQL Server Management Studio Express and run a query similar to the following one. In this example, the stored procedure is DatabaseBackup.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DatabaseBackup]
@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

Create the Web Page

Last, you’ll create a web page that has the code to execute the stored procedure. You can use any language supported on Windows such as ASP Classic or ASP.NET. For this task, we recommend ASP Classic so that there is no .dll file and no application restart is needed.

Create a new ASP page and call it backupdb.asp. Edit the location path and SQL connection string. The content for the site is below.

<%@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 >
<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-n03stor1wc1dfw8382492382489www.yoursite.combackups"
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>

Upload the webpage you just created into the folder created in the first step using your FTP client.

Create the Cron Job

The last step in the process is to schedule the cron job. See our article Creating Cron Jobs in Cloud Sites for information on scheduling a cron job.

Using cURL to Execute

When setting up the Cron job, it is important that the script file selected is cURL to properly execute the script. Cron jobs that run over 900 seconds (15 minutes) are automatically terminated.

Unsupported Commands and Cron

Some functions of commands have been restricted to protect the integrity of the Cloud Sites Platform. See the list below for the commands that are not supported in Cloud Sites and scripts for crons.