Benefit of DDL Trigger in DEV (and Script Execution logging)

Yeah, well, I'm a twit ... but in this situation not too often :slight_smile: , and probably even you lot manage to do it once in a while ...

I have a script of all DDL changes to the database. As I make a change, in DEV, I add that to the script (i.e. chronologically) and then I can use that script (or an adaptation of that script) to rollout the DDL changes to TEST ... and then to PRODUCTION.

Two days ago I Copy & Pasted a completely different script over the top of the DDL Script ... and saved it. :rage:

I was supposed to be pasting over the top of a database-to-database-data-sync script, but I must have got the wrong one - probably did it in the wrong "open window" at the time :frowning:

So I now have some DDL changes that were in that original script file which are "forgotten".

I have a number of options, which I'll discuss below.

How did this happen? Will it happen again?

The reason that the Copy & Paste failure occurred (apart from me being a twit ...) is:

My TABLE SYNC scripts are mechanically generated. I hadn't used this one recently but needed to freshen up DEV with latest data from PRODUCTION. But the database has tables / columns added recently (since the SYNC script was last used), so first I needed to freshen it up.

My procedure is:

Generate a new script (mechanically), save to COMPARE_NEW.SQL then locate the current/old version and save that to COMPARE_OLD.SQL. Now run my programmers editor Diff&Edit tool (which makes miraculous comparison and allows easy copy-across and/or edit along with RegEx search and the whole nine-yards). This is done in a pair of TEMP files so that I can start again if it goes horribly wrong.

Then I Copy & Paste from COMPARE_NEW.SQL over the top of the original file. Or "in the wrong window" as must have happened.

But I planned for that even that SNAFU ... at the bottom of every file is a comment (actually a PRINT statement) with its filename, so I can paste UP TO that statement, see that the filename from the pasted copy matches the one left behind and, only then, delete the duplicate line and ONLY THEN !! save it.

That "Name of file at the bottom" rule applies to absolutely all SQL files here ... except the blinking DDL and SYNC Scripts :frowning: So clearly I need to put that right as a longer term fix.

So my options are:

Restore the Script File from Backup. I do NOT have real-time backup, so the changes made on the day are not backed up (do you have real time backup??)

I have a Source Code repository, so I can get back the latest version from that. But that also does not include the changes that were made during the day.

The SQL Tool I use does NOT save a BAK file (does SSMS etc. have that option?)

I can restore the DEV database, to point-int-time, and script out both Backup and Current and compare the DDL (there are RedGate etc. tools that compare DDL but I don't own those as we don't deploy DDL changes based on DIFF of Before/After databases - we have too many "Add this COLUMN and then run this SQL to populate the column" along with "Be damn sure you do all these steps in the right order" !)

Aside: we have ALWAYS run our DEV databases in FULL Recovery Model, as we are extremely likely to need to do a comparison to some earlier point-in-time ... or to "repeat that again" but "better this time" !

So I'm going to have to do that Restore/Compare to be 100% certain ...

... but I also have a DDL Changes Trigger that logs all changes to a table. If nothing else that's going to give me a really good clue as to what changes I made, and when.

Somewhat interesting is that I definitely made changes to DDL yesterday, AND I would have saved them in the SCRIPT file ... so how come the mangled file, on my C: drive, is dated the day before? I can't imagine a Rename-and-Overwrite issue (I don't do that) nor a file-copy (I don't do that to different filenames, my Source Control would have a fit if it didn't know that A had become B ...) I did search all source code for the name of a DDL object unique to yesterday's change ... Nada :frowning:

So if you don't have a DDL Change s trigger in DEV you might like to think of trying that.

Ditto if you don't run your DEV database in FULL Recovery Model

Right ... I'm off to Restore/Compare ... "I may be gone some time ..."

Update: "Hurry up slowly" applies here :slight_smile:

I found the DDL script for the objects I created yesterday, it was in a different project (so not on the PATH that I globally searched earlier)

So now my question has changed:

"Did that script file change DURING that day, or is it unchanged since the previous backup?"

We have another "Get out of Jail Free" card here. Every SQL script has a logging statement at the top (and scripts that perform multiple operations, such as DDL changes, have one at the bottom too)

My scripts (this one for a Stored Procedure, but View, Trigger, Function are all similar) look like this:

--
PRINT 'Create procedure My_SP_roc' -- '
GO
-- MyTemplateName template version 151222
EXEC dbo.My_SP_LogScript 'My_SP_roc' ,'171123'
GO
-- DROP PROCEDURE dbo.My_SP_roc
IF OBJECT_ID(N'[dbo].[My_SP_roc]', N'P') IS NULL
	EXEC ('CREATE PROC dbo.My_SP_roc AS RAISERROR(''My_SP_roc:stub version, full version not found'', 10, 1) WITH LOG, SETERROR RETURN -1')
GO

ALTER PROCEDURE dbo.My_SP_roc
...
--================== My_SP_roc ==================--
GO
IF OBJECT_ID(N'[dbo].[My_SP_roc]', N'P') IS NOT NULL
	GRANT EXECUTE ON dbo.My_SP_roc TO MyApplicationRole
GO
PRINT 'Create procedure My_SP_roc DONE'
GO

My_SP_LogScript does a number of things. Primarily it inserts a row into Script Log table with Name and Version (and adds Current Date/Time). "Version" is loosely defined, I just use today's date, but I can append "del" if the script has become one that deletes the object, and maybe sometimes I append "a", "b", ... to differentiate for some reason.

Also if My_SP_LogScript is executed [with just the Name but] WITHOUT the Version it displays details about the current object (including modify-date), a history of previous runs, and if the Log Entry is more recent than the object it displays a warning (and I also have Validation Scripts/Reports that check for that scenario too - if the last time a script was run it did not create the object - then it probably terminated with an error.

We also have Execution Logging of SProcs - every Sproc logs its execution time and parameters, and updates that log entry (on exit) to indicate success / specific error No. "Start but not finish" is also considered to an error. My_SP_LogScript has an optional parameter that will display that execution history ("Interesting. I wonder when this SProc was last run" ... yup, got the answer to that one :slight_smile: even "By whom", "How often", and so on along with "What version is on DEV vs. PRODUCTION")

(Logging Table stores Database and Server, so that when a DB gets restored "elsewhere" I know on what, original, Server/Database the object was previously created.

Right, enough wibble already ... I'm off to see when my DDL Script, accidentally overwritten on 21-Nov, was last run ....

... looking good:

EXEC dbo.My_SP_LogScript 'MyPatchScript' 
Database Script                    Date                    Server
-------- ------------------------- ----------------------- ------------
MyDB_DEV PATCH_MyAPP_v02_171001-E  2017-11-17 19:13:37.353 MySQLBox
MyDB_DEV PATCH_MyAPP_v02_171001-02 2017-11-17 19:13:36.930 MySQLBox
MyDB_DEV PATCH_MyAPP_v02_171001-B  2017-11-17 19:13:23.887 MySQLBox

panic over :slight_smile:

One point in case of interest. ALL our SQL scripts start with a line -- which is because we concatenate SQL files together, to make "release scripts", and sometimes there is no trailing LineBreak. I suppose I could concatenate them adding in a line-break too, but the concatenation we'e been using for decades is very basic.

Glad to hear that the panic is over. :wink:

We do similar as you for deployment up the chain of servers but a bit more formally.

First, we have each script in its own file and it has a release header for any change tracking that may occur in the process of going from Dev to Staging and possibly back to Dev and then back to Staging for retest of any required changes that were discovered during testing.

We also religiously check EVERY change into SVN (Source Control). Not only has it saved our hinnies more than once for "configuration control" but the auditors love us and we're almost always in the process of being audited by one group or another..

1 Like

What consitutes "a change" for you? For me its a bundle of work that has been tested and proven (well ... "today, in dev" is good enough as a waypoint, but its more than "I wrote it, but haven't had time to test it yet")

If we had distributed version-control I would check in all my, local, stuff all the time - and then only merge with Trunk when I was suitably ready, but sadly we only have SVN ... it always proves "too much effort" to move on to something more Whizz-Bang ...

We check-in with Peer-review, which I think helps. That spots inconsistencies with formatting and adherence to other standards - such as "Template says there should be error checking THERE" not to mention the benefit of a peer saying "Are you sure you meant to do THAT" :slight_smile:

We don't do the trunk thing for SQL Server code... no "merges" either. It's a different animal than what front-enders do.

We have "stand alone" tickets where there might only be 1-3 scripts for the ticket and we have "project tickets", which may have 50 or 60 scripts across multiple databases. We make a new folder for each stand-alone ticket and we have 1 folder with subfolders for project tickets. The ticket number is a part of the name for folders so we can find them. And, yes, it's a central repository that anyone in our CTech group can get to.

Thanks Jeff. Interesting approach and I can visualise how your "project tickets" works well.

A lot of people think both the method and myself are "out of date" and "out of touch". The funny thing is, even with the likes of some of the better source control software out there, they end up having problems that I don't. Yep. it means me manually deploying the 50-60 scripts manually on deployment night but the auditors love it and, if I run into a problem or unexpected failure (a very, very rare thing because we've already done the same in QA, UAT, and Pre-Prod, which is a copy of Prod and we do a compare for regression code), we only have to work on the current failure instead of either rolling everything back (which isn't always possible due to the nature of what we do) or trying to fix the failure in a script that contains 50-60 object/data deployments.

I've said it before, as well... the auditors absolutely love our database related deployment methods especially since they do their audits at the ticket level.