Tag Archives: cheap SQL Server hosting tips

ByAlexia Pamelov

Cheap Windows ASP.NET Hosting Tutorial – How to Create Cron Job to Backup Your SQL Database

CheapWindowsHosting.com | Best and Cheap SQL Server hosting. Cron Jobs are used for scheduling tasks to run on the server. They’re most commonly used for automating system maintenance or administration. However, they are also relevant to web application development. There are many situations when a web application may need certain tasks to run periodically. A cron job/scheduled task is a system-side automatic task that can be configured to run for an infinite number of times at a given interval. Cron/scheduled task allows you to schedule a command or a script to run at a specific time of day, or on a specific day of the week or at a particular time of day on a specific day of the month. It allow for scheduling down to the minute and up to an annual event.

database2

Here is the simple step how to create Cron Job to Backup Your SQL Database :

  • Step 1. Create a folder to store the backup in your FTP application
    Open your ftp application and connect to the account that has the database you want to back up. Create a folder outside of the webcontent called “backups”.
    fs1-n02stor1wc1dfw2382489382489www.yoursite.combackups
  • Step 2. Set folder permissions in your FTP application
    Right click the folder and add all write permissions. If your Ftp software doesn’t do this then checkout the free FTP client called FileZilla.
  • Step 3. Create a stored procedure that performs the backup with an input parameter for the filename
    Connect to your database using a client  and run a query like this. The procedure will be named FullBackup in this example
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 NOFORMAT, NOINIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

END
  • Step 4. Create a web page that executes the stored procedure
    You can use php or asp.net for this also. For something this simple, you can use classic asp. That way there is no .dll to deal with and no application restart needed.  Now create a new asp page and called it backupdb.asp.  The contents of the file are as follows. When you are done, upload this file to a folder in your content area.

This script will generate a filename based on the date. If a backup already exists for that date it will increment a version counter until a fresh filename is found. This script will generate 1 file per execution. Modify as needed if you want to increment a single file.  Edit the location & connection string to work for you.

<%@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, filelename, ver, extention, abolutespath

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

    Set cn = Server.CreateObject("ADODB.Connection")
    cn.connectionString= "Provider=SQLNCLI;Server=mssql05-01.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>
  • Step 5.  Schedule a Cron job to call the web page.
    Access your control panel and go to the features tab of the site with the database. Choose http as the language, Enter the URL to the asp script and your email and Schedule the job for a daily run at an off hour.

Best OFFER Cheap SQL Server Hosting ! Click Here