Pythian Group

Have you ever been asked to restore a database and wondered which backup files were available? And if many were available, which ones you should use, and in which order?

Getting familiar with the msdb schema, especially the backupset and backupmediafamily tables, helps to answer that question since all the backup history is stored in that database. But you would still have to check if the backups are still available on disk (tape?) and figure out what are the commands to restore, and the order in which to restore each file.

Okay, this is not a difficult thing to do, but when you do it over and over again, it becomes tedious, and the automation bug in you starts to look for a better and quicker way to handle it. The bug in me found the following answer.

Read the rest of this entry . . .

In a linkage of biblical proportions, Log Buffer #83 pointed me to Tom Kyte’s reiteration which pointed me to Coding Horror’s rant about source controlling the database schema.

Now, for starters, I agree with Tom’s sarcasm and Coding Horror’s rant — the database schema really should be source controlled in the same place as the application code, because otherwise how do you know what changes happened when, and which version of the code goes with which version of the schema.

The problem I have is this — being a purist (and by the way, *not* a programmer so there could very well be concepts I’m missing), I really want to source control the DDL/GRANT statements and whatever that are applied to the production database, because that way anyone can see exactly what was run, and I can do a schema backup and compare to the source controlled version.

But then you have the problem where you have the old schema + the code *to be run* and then you have the new schema, and what do you do with that file of code to be run? It’s now old, but you don’t want to delete it.

If the answer is “don’t source control the change statements, just the differences” then you’re requiring folks to either figure out how to get from point A to point B, or start with sample data only.

Plus, sometimes the DDL/GRANT statements are complex, or there are a lot of them to be run in a particular order, such that I *want* to source control them for backup and revision’s sake — even though SQL is declarative, there are still a few ways to skin each cat. How do I resolve that? (a separate and/or private repository?)

And if that all is the case, what are the security ramifications of having the schema, the code, and the data all in one place (the repository)? I’ve worked at places that have *real* 3-point authentication for a reversible hash — a user-supplied password, that wasn’t anywhere in the code (as far as I know it was passed mouth-to-mouth and not even in documentation); the encrypted data itself; and the code with the encryption/decryption algorithms. Sure, in the repository you might have test scripts, but where and how do you back up the repository? In the same manner as the data?

I guess folks have to be careful about any real data they backup, making sure it’s in separate places that their repository is backed up?