I’ve wanted to implement some kind of source control on my SQL Servers before, but the only product available at the moment is Red-Gate’s SQL Source Control, and I didn’t need all the functionality it offered (or want to pay for it). Also, it relies on developers checking-in their changes, and that’s prone to forgetfulness anyways, as well as leaving your database prone when somebody just changes something in production, without using their development tool – ouch. Sure, you’re protected against accidental drops, but what if somebody tweaks something in production without checking it back in? You’re hosed.
All I wanted was a simple process that would run automatically, taking periodic snapshots of the database objects and recording any changes. I decided to roll my own – it’s quick, simple, can be set up to run on a schedule, and automatically includes any new databases created on the server without any intervention.
This Stored Procedure goes through the following steps:
- If the Master.dbo.coSourceControl table (used to store the history) doesn’t exist, it creates it
- For each database on the server (so new databases are added automatically), it:
- Grabs the text contents of all the user objects (not flagged as “IsMsShipped”)
- Compares the contents of each to the last known copy (if there is one)
- If the object is new or has changed, add a new copy to the source control table in master
- Output the number of objects updated
- Optionally, it could email somebody to tell them about the results, but it currently does not
The history is kept in a single table – master.dbo.coSourceControl – which has the database it came from, the object_id, the object name, object contents, and the timestamp. Since it uses the object_id to track things, it will also record a name change in an object, even if the contents didn’t change.
To implement it, just grab the script and run it in the master database – it will create the stored procedure coSourceControlRefresh. That’s it – now either run it on demand, or you can schedule it. It will create the supporting table (if it’s missing) and scan every database every time it’s run. To see the history for an object, just do:
SELECT db_name(databaseid) as [Database], object_name(objectid) as [Object Name], SourceDate, ObjectText FROM master.dbo.coSourceControl WHERE object_name(objectid) LIKE '%The name of some object%' ORDER BY SourceDate DESC
Restoring a dropped or changed database object should be as simple as running the query above, grabbing the contents of ObjectText you’re interested in, and then pasting it in another window and executing it. Bam – previous version of the object restored (and this stored proc should, the next time it runs, see that you’ve altered the object and record that there’s a “new” version of it).
If you run it and like it – or don’t like it – please leave a comment to let me know – nothing expected in return, but it’s nice to know when people find it useful. I’m happy to make any enhancements you’d like to see. I hope you enjoy it and it’s able to save you from the headache of a dropped database object to which you can’t find the source!
Very cool. Clean and convenient. Thanks!
Quick and elegant. Very nice solution to exactly the issue I was researching. Thank you!
Could this be modified to allow a single developer to update changes to an individual stored procedure? Like: fn_UpdateMySp(‘MyStoredProcedureName’)?
@Michael Miller:
I don’t see why it couldn’t be modified to allow on-demand backup of specific stored procedures. All you’d have to do is modify the CREATE PROC statement to accept a parameter:
CREATE PROC dbo.coSourceControlRefresh
(@ObjectNameToBackup NVARCHAR(1000))
AS
And then just add the filter to the WHERE clause of the first table in the INSERT statement:
…
FROM sys.objects (NOLOCK)
WHERE object_id > 0
AND OBJECTPROPERTY(object_id, ”IsMSShipped”) = 0
AND object_definition(object_id) IS NOT NULL
AND object_name(object_id) LIKE @ObjectNameToBackup) s0
…
However, the existing script would accomplish the same goal – instead of backing up on demand (and leaving it up to the developer or DBA to remember to do so), it would automatically detect and record changes as they happen. While you might end up with some additional copies of the SP while development is in-progress, they’d be easily identifiable because you’d see that another version was created not long afterwards.
I could see the desire to have an on-demand backup scheme though for just certain procs, and this change would definitely meet that need.
thank you so much.
note: set “results to file” to view the scripts including line breaks.
Thanks a lot.
Simple, effective and fast
Thanks
rockOn
Hi,
What about if object is removed from the Database.
Thanx,
Jevis.
In its current form, the script will just ignore that item – it will continue to show up when you run the query to get the most recent copy of all database objects. The purpose was to preserve the code if the deletion was accidental, so that it makes restoring the object to its previous state easy. Also, it saves the objects by their OBJECT_ID, so if you drop an object and recreate something with the same name, it will show up as a new record.
If you wanted to mark an object as deleted in the source control table, the query could be changed to insert the text with a zero-length string or NULL. If you decide to do this, let me know and I’ll post the update here alongside the original script.