Yeah, well, I'm a twit ... but in this situation not too often , 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.
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
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 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
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 ..."