Creating a SQL Server Code Back Up

Over the last few years I’ve got into the habit of setting up an SSIS job to do a daily backup of all the procedures, views and functions on databases that I’m working on. A lot of organisations I’ve worked with don’t have any source control or release procedures on database objects and having a history of these objects can be invaluable. A few times I’ve been able to use this to restore procedures that have been updated without having to find and restore an entire database to get at the old procedure version – well worth the time required set this up. First a view for the procedure and view definitions:

create view [dbo].[vw_views_and_procs]
as
select	DB_NAME() database_name
,		OBJECT_SCHEMA_NAME(o.object_id) schema_name
,		o.type_desc object_type
,		object_name(o.object_id) object_name
,		definition
from	sys.sql_modules m 
		join sys.objects o 
			on m.object_id = o.object_id

In an SSIS project set up an ADO.NET connection manager for the database. One potential gotcha here is the user connecting must have permission to view definitions. If they don’t the definition will be null. You can explicitly grant this if needed: GRANT VIEW DEFINITION TO [user]

Then create a Data Flow task and an ADO NET Source component to select the definition only from the view at the connection manager:

An SSIS source component to extract SQL Server code for backup

Add a Flat File Destination component and link source and destination:

Add an SSIS flat file destination component save SQL Server code for backup

Edit the flat file and create a new Flat File Connection Manager for a delimited file:

Add a new SSIS flat file connection manager

In the Flat File Connection Manager make sure to click the Unicode option – the definition column will come across as DT_NTEXT and specifying Unicode will mean we don’t need to muck about converting this to an ANSI compatible type:

Create a Unicode flat file connection manager

After saving this, in the connection properties, set up an expression to dynamically set the Connection String property (the file path). The expression "C:\\Temp\\Code Back Up " + replace(left((DT_WSTR, 30) GETDATE(),19) + ".txt", ":", "") will add a date time stamp to the file name:

Create a timestamped filename for an SSIS flat file output

That’s it. The package can then be scheduled in SQL Server Agent and will output a timestamped file with all procedure and view object definitions providing a history of any changes.