Welcome, guest | Sign In | My Account | Store | Cart

Code and Stored Procedure for database backup, zip and links to download and delete backup files.

Text, 116 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
----------- Stored Procedure > backup_handle -----------

USE [your_database]
GO
/****** Object:  StoredProcedure [dbo].[backup_handle]    Script Date: 05/05/2009 14:03:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/***********************************************************************
DEVELOPED BY    :	Dimitris Siskopoulos
CREATED		:	05.05.2009
DESCRIPTION	:	Handles database backup
VERSION		:	1.0.0
CHANGES		: 	
************************************************************************/
ALTER PROCEDURE [dbo].[backup_handle]
	@databaseName varchar(100),
	@backupDirectory varchar(100)
AS

--set @databaseName = 'YourDatabaseName'  
--set @backupDirectory = 'd:\mywebsites\'

-----------------------------------------------------------------   
-- Backup database to file   
-----------------------------------------------------------------   
DECLARE @backupFileName varchar(100),
		@databaseDataFilename varchar(100),
		@databaseLogFilename varchar(100),   
		@databaseDataFile varchar(100),
		@databaseLogFile varchar(100),   
		@execSql varchar(1000)   
  
-- Set the name of the database to backup   
--set @databaseName = 'myDatabase'  
-- Set the path fo the backup directory on the sql server pc   
--set @backupDirectory = 'aboslute_path_to_backup_directory' -- such as 'c:\temp\'   
  
-- 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)  

-- EOF --


----------- COLDFUSION 8 CFML -----------
<!--- 
	Developed by: Dimitris Siskopoulos 5-5-2009
	Requires SP: backup_handle to work properly
 --->

<!--- <cfinclude template="yourautentication.cfm">  --->
<cfset databaseName="YourDatabaseName">
<cfset backupDirectory="d:\mywebsites\">
<cfset BackupName="#databaseName#-" & dateformat(now(),"dd.mm.yyyy.bak")>


<cfif not isdefined("delbackup")>
  <CFSTOREDPROC procedure="backup_handle" datasource="YourDatabaseDSN">
    <CFPROCPARAM type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="databaseName" value="#databaseName#" null="no">
    <CFPROCPARAM type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="backupDirectory" value="#backupDirectory#" null="no">
    <CFPROCRESULT name="backup">
  </CFSTOREDPROC>
  <cfoutput>
    <h1>done!</h1>
    <!--- file to store zip --->
    <cfzip
action="zip"
source="#ExpandPath( './#BackupName#/')#"
file="#ExpandPath( './#BackupName#.zip')#"
overwrite="true"
/>
    <h2><a href="#Request.ROOT#/adm/#BackupName#.zip">Click to download <strong>#BackupName#.zip</strong></a></h2>
    <h4><a href="?delbackup=true">Click here to delete</a> the backup files after your download!</h4>
  </cfoutput>
  <cfelse>
  <cfif delbackup eq "true">
    <cfif fileexists('#ExpandPath('./#BackupName#')#')>
      <cffile action = "delete" file = "#ExpandPath('./#BackupName#')#">
      <cfoutput>
        <h3><span style="color:green">File</span> #ExpandPath('./#BackupName#')# <span style="color:green">deleted succesfully!</span></h3>
      </cfoutput>
      <cfelse>
      <cfoutput>
        <h3><span style="color:red">File</span> #ExpandPath('./#BackupName#')# <span style="color:red">not found!</span></h3>
      </cfoutput>
    </cfif>
    <cfif fileexists('#ExpandPath('./#BackupName#.zip')#')>
      <cffile action = "delete" file = "#ExpandPath('./#BackupName#.zip')#">
      <cfoutput>
        <h3><span style="color:green">File</span> #ExpandPath('./#BackupName#.zip')# <span style="color:green">deleted succesfully!</span></h3>
      </cfoutput>
      <cfelse>
      <cfoutput>
        <h3><span style="color:red">File</span> #ExpandPath('./#BackupName#.zip')# <span style="color:red">not found!</span></h3>
      </cfoutput>
    </cfif>
    <cfoutput>
      <h4><a href="#Request.ROOT#">Click here to return to main page of your site #Request.ROOT#</a></h4>
    </cfoutput>
  </cfif>
</cfif>
Created by dimitris siskopoulos on Wed, 6 May 2009 (MIT)
Text recipes (14)
dimitris siskopoulos's recipes (1)

Required Modules

  • (none specified)

Other Information and Tasks