I’ve turned into a SQL Server DBA lately. We had been doing database backups manually (since our client data volume is fairly small) but as we grow automating everything becomes important.
There is a simple reason people like SQL Server. It’s just easy to administer. I know the Oracle fans out there will say you can do more, but the simple fact is that I needed to learn SQL Server maintenance plans, backups and restores, best practices and implement them on a production server.
It took less than a day.
Of course, there’s no substitute for experience and I’m sure I will learn more and more, but the simple fact that this can be done so easily means that I can get back to developing features that our clients will like.
We also wanted an automated way to do a restore for a single client in a multi-client database. This is tricky if you don’t want to bring down the database and you don’t want to do a restore on the entire database.
The SQL MERGE command is very useful here, allowing you to merge two tables (backup and target for example) with different options for when you find a match (update the target from the backup), find a record only in the source (insert into target) or find a record only in the target (delete). Very handy and quite fast.
But the problem with the MERGE command is that you have to specify every column. You also need to be careful to do the tables in dependency order, otherwise you may get a failure due to foreign keys.
So how do you maintain such a huge script when you are developing rapidly and changing the data model fairly frequently? Well, you have to automate the generation of the script, of course.
So this has been my geeky SQL Server DBA experience.
How do you get your tables in dependency order? First you need a list of tables in the schema. That one’s easy:
select name From sys.tables where name not like ‘sys%’ order by name
Then you need to know what tables each table depends on:
EXEC sp_MSdependencies <tablename>
That returns the tables that <tablename> depends on. So a simple recursive function can get a list of tables in dependency order. This amused my geeky side because I can’t remember the last time I used recursion, but this is one of those perfect cases for it.
A gazillion years ago, when I actually did the hiring of developers I would sometimes ask a recursion question. Not because you are likely to need recursion, just to gauge the developers mindset. Recursion seems to be one of those things that some minds grasp easily and others always struggle with. Good developers always fall into the former category.
With the tables in the right order, you then just need to be able to get column information from SQL Server. To get columns in a table:
select c.name ColumnName FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where t.name = ‘<tablename>’
order by t.name, c.column_id
You may need to know the primary key for a table:
select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = ‘<tablename>’
and CONSTRAINT_TYPE = ‘PRIMARY KEY’ and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
And you’ll need to know if the table has an Identity column or not, so you know if the INSERT requires you to turn Identity Insert on or not:
select c.name FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
and t.name = ‘<tablename>’
With that information, I wrote a program to generate a script that creates MERGE statements for every table in the proper order. This will do a single client, in place, live restore (also works to copy a single client to a new database).
Totally automated restore scripts, no matter how often we change the database. Sweet.
OK, I am a geek…
Steve Fischer's Random Blog 