Handy Little SQL Script/Idea for Business Continuity Plan (BCP)

17 Jan

This is a script that will export certain information from a SQL database to a text file on a regular basis. In my case, I export the emergency contact details of all clients at work who have appointments scheduled in the next 4 weeks (28 days), so that in the event of a complete SQL meltdown, these people can be contacted to avoid wasted journeys to our centre. The text document is then compressed and encrypted with a password and placed in one of my remote backups up in the cloud.

Note the Output File box above.

This script runs on MS SQL as a Job which is scheduled to run every day at 1:00. It is very simple and has only 1 step:

Exec [BCP_export]

This runs the stored procedure which contains the following:

USE [MyDB]
GO
/****** Object:  StoredProcedure [dbo].[my_sp_name]    Script Date: 01/17/2011 14:43:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   Procedure [dbo].[my_sp_name]

AS

select all the details I need from this table
inner join this other table on this = that
where start_time >= DateAdd(“d”, -1, getdate())
and start_time < DateAdd(“d”, +28, getdate())
and appointment_status = active
order by start_time asc

There is then another script that runs and uses 7zip to compress and encode the document.