Code and Stored Procedure for database backup, zip and links to download and delete backup files.
Text, 116 lines
Download
Copy to clipboard1 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>
|
Tags: coldfusion, sql_server_2005
