People talk plenty about data backup, audit trails, etc., and of course that is super-important. But one thing I’ve found is that I sometimes need an easy – or at least, relatively easy – way to either roll back or just view previous versions of sql objects such as stored procedures, functions, and views. Now, I script all my objects in a drop\/create format, and save the script using my Dropbox folder, so a) I can access the scripts from various places, and b) there is some version history, but I don’t feel comfortable relying on that. I could also back up the whole db, and often do (in addition to regular, automated backups), but in most cases relevant to this particular need of mine, I don’t need or want to have to restore a whole db, just a single object; also, I am sometimes dealing with very large databases that are slow or even impossible (given space constraints) to back up onto the same server.<\/p>\n
Of course, the real answer – or at least, a<\/em> real answer – is to use a source control system, check in those scripts with each change, and annotate them as to the reason for each change. But to be brief, I’m not doing that – you can argue all you want that I ought to, and I won’t disagree, but that’s not what this post is about. It is about how to store that information more or less automatically, in the database itself. I am using MS SQL Server 2005 for this – I expect the same is applicable, possibly with some variation, in other versions, but so far I’m not bothering to investigate that.<\/p>\n What I’ve done is create a log table like this:<\/p>\n and then added a procedure like this:<\/p>\n Now, in my scripts for any other objects, I start them like this:<\/p>\n This way, any time I modify the script, I am automatically saving a copy of the last version, then dropping the object, ready to create anew. Obviously, this proc is somewhat specific to my uses; it can and will be improved, but I just whipped it up this morning and it is sufficient for my purposes. Changes\/improvements include:<\/p>\n Now, one thing to note in using the logged info – Syscomments stores the script in chunks, depending how long it is. So, for a long stored proc, for instance, I might have 10 rows for a single backup. So, to restore, or review, I need to select ObjectContext from the ObjectBackups table for the last (or other specific) date of change, and then manually concatenate those rows in my SQL query window.<\/p>\n I expect some will say this is too hackish, and I’m sure they’re right – I’d like to hear some concrete arguments against this concept (I’m always learning, I hope), but for now, it serves my purposes, and maybe it will help you too.<\/p>\n","protected":false},"excerpt":{"rendered":" People talk plenty about data backup, audit trails, etc., and of course that is super-important. But one thing I’ve found is that I sometimes need an easy – or at least, relatively easy – way to either roll back or just view previous versions of sql objects such as stored procedures, functions, and views. Now, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6,7],"tags":[],"class_list":["post-10","post","type-post","status-publish","format-standard","hentry","category-database","category-sql-server","category-workflowbackup"],"_links":{"self":[{"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/posts\/10","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/comments?post=10"}],"version-history":[{"count":7,"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/posts\/10\/revisions"}],"predecessor-version":[{"id":17,"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/posts\/10\/revisions\/17"}],"wp:attachment":[{"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/media?parent=10"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/categories?post=10"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/experimentalism.net\/morematter\/wp-json\/wp\/v2\/tags?post=10"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}create table dbo.ObjectBackups (ID int identity(1,1) primary key,\r\n DatabaseName nvarchar(256) not null,\r\n ObjectName nvarchar(256) not null,\r\n ObjectType nvarchar(2) not null,\r\n ObjectContext nvarchar(max) not null,\r\n DeletedByUser varchar(256) not null,\r\n DateDeleted datetime not null default(getdate())\r\n )<\/pre>\n
create procedure dbo.DeleteObjectWithBackup @ObjectName nvarchar(255), @ObjectType nvarchar(2)\r\nas\r\n if (select object_id(@ObjectName, @ObjectType)) is not null\r\n and @ObjectType in ('P', 'V', 'FN', 'IF', 'TF')\r\n begin\r\n insert dbo.ObjectBackups (DatabaseName, ObjectName, ObjectType, ObjectContext, DeletedByUser)\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\r\n select db_name(), @ObjectName, @ObjectType, cast(sc.text as nvarchar(max)), user_name()\r\n from sysobjects so\r\n inner join syscomments sc on so.id = sc.id\r\n where so.type = @ObjectType\r\n and so.name = @ObjectName\r\n declare @sql nvarchar(max)\r\n select @sql = 'drop ' +\r\n case @ObjectType when 'P' then 'procedure'\r\n when 'V' then 'view'\r\n when 'TF' then 'function'\r\n when 'IF' then 'function'\r\n when 'FN' then 'function'\r\n end + ' dbo.' + @ObjectName\r\n exec (@sql)\r\n end\r\ngo<\/pre>\nexec dbo.DeleteObjectWithBackup 'myProc', 'P'\r\ngo\r\ncreate procedure dbo.myProc ...<\/pre>\n
\n