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:
This runs the stored procedure which contains the following:
/****** Object: StoredProcedure [dbo].[my_sp_name] Script Date: 01/17/2011 14:43:59 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER Procedure [dbo].[my_sp_name]
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.