-this is using SqlServer 2008, there will be modifications added later for SqlServer 2003 changes
Step 1 – Set up Folders
a. Add a folder called ‘DBBackupFTP’ in the ftp root
Add a folder called ‘DBBackupPublic’ in the webroot
b. Give both folders either universal priviliges or specify the sql client and a windows user that you’ll remember the login credentials to.
c. Make sure to disable directory viewing on ‘DBBackupPublic’
-if you do take the step to share this folder with sql and one user (b.), then when you attempt to download from a link in the email, you’ll have to enter that users credentials before you’ll be given access to the file.
Step 2 – Prep Sql Server
Open SSMS
a. Run the following on the server, to toggle the Sql Mail property to enabled
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO
b. Run the following to add the main stored procedure to the database
-configure the values at (a.) and (b.)(c.) and (d.) if necessary
-put your email address at (e.)
USE [ServerName] -- a. Replace with relevant Database Name (any database we have created)
GO
/****** Object: StoredProcedure [dbo].[BackUpDB] Script Date: 03/09/2010 10:29:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [BackUpDB]
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
DECLARE @name VARCHAR(50) -- database name
DECLARE @ftppath VARCHAR(256) -- path for backup files that can be retrieved via ftp
DECLARE @dloadpath VARCHAR(256) -- path for backup files that can be downloaded
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @name = ''
SET @ftppath = 'C:\inetpub\ftproot\DBBackupFTP\' -- b. Path to the FTP root folder
SET @dloadpath = 'C:\inetpub\wwwroot\DBBackupPublic\' -- c. Path to the website root where you created that folder
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer$MSSQLSERVER2008','ReportServer$MSSQLSERVER2008TempDB') -- d. Specify any databases not listed here that you do not wished to be backed up. You won't have permission to backup system databases anyway, so this will avoid bugs.
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @ftppath + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
SET @fileName = @dloadpath + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor;
DECLARE @bodyText AS varchar(255)
SET @bodyText = 'Winsvr DB Back-up'
SEt @bodyText = @bodyText + '
'
SET @bodyText = @bodyText + 'Download a Copy Here'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Sql2008Profile',
@recipients = 'sendto@gmail.com',
@body = @bodyText,
@subject = 'Winsvr DB Back-up',
@file_attachments = @fileName;
END
Step 3 – Set up Mail Profile with Database Mail
In the Object Explorer in SSMS, click on ‘Management’
The right click on Database Mail and Configure
a. Set up a new profile
-Create An Account
-Click Next, then Finish, Then go back and right click on Database Mail and go back to configure, this time you’ll create a new Profile
-Make the Profile name = Sql2008Profile
-Click Add…
-Click Next, then Finish.
b. Right Click on Database Mail, Click on Configure, Select ‘View or Change System Parameters’. Change the ‘Maximum File Size’ to 1000000000.
Click Next, Click Finish.
c. Right Click on Database Mail, Test Email, Enter your email, if this fails to send you an email, talk to your Server Admin.
** It’s now set up so you can email yourself a backup with an extra download link just by executing a stored procedure. The next step is just to automate that.
Step 4. Automate (this python script below is an option, or you can build your own script into your project)
a. Python Script, includes the need to add python and pymssql to the server
import pymssql
conn = pymssql.connect(host='SERVER2008\\MSSQLSERVER2008', user='Username', 'Password', 'DatabaseName')
cur = conn.cursor()
cur.execute('EXECUTE BackUpDB;')
conn.commit()
conn.close()
-add this script to your DBBackupPublic folder, call it dbBackup.ps. -if .bak files are protect by login, then specify that this script has full access from the http user, then add a cron job to call this script, http://webaddress/DBBackupPublic/dbBackup.ps however often you wish to receive a db backup, and your finished.
Greynault's Blog 