How can I create a cron job to backup my MSSQL database?
NOTE: This article is written for our Classic Cloud Control Panel. You can access this interface from the New Cloud Control Panel by clicking your username in the upper-right of the control panel and selecting "Classic Cloud 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:
- Create a backup stored procedure
- Create a web page that runs it
- Schedule the task to run every day.
First, you need to create a folder to store the backup in your FTP application by opening your ftp client and connecting to the account that has the database you want to back up. Once conected, create a folder outside of the /web/content/ directory called "backups". The path to the folder should look similar to this
/123456/www.domain/com/backups/
Next, set folder permissions in your FTP application by right clicking the folder and setting the folder permissions to 777 (Please understand you are allowing write for public). 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).
You will also need to create a stored procedure that performs the backup with an input parameter for the filename by connect 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
Next 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 called it backupdb.asp. Please 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
Set cn = Server.CreateObject("ADODB.Connection")
cn.connectionString= "Provider=SQLNCLI;Server=mssql08xx.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>
Finally, schedule a Cron Job to call the web page. to do this, follow the steps below
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.
4) Schedule the job for a daily run at an off hour. I prefer a "late nite" period, such as 1:00 AM, as that is when my site is slowest.
This was created by one of our forum users. If you need further assistance, please contact your developer.
© 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
0 Comments
Add new comment