Backup of schema by query

Question

Backup of schema by query

Stephen York

SSC Journeyman

Points: 79
More actions
April 29, 2007 at 9:59 pm

#91103

Hi,
I support a legacy software system which has been around for years and has some usability issues and occasionally the database ends up in an inconsistent state.
To fix the data, we delete, update, etc certain sets of tables to correct the issue.
We currently have some sql scripts which select * from each table that will be affected by our manual 'patch' just in case things don't go as expected.
With the data output we can then just reinsert the rows to return to the previous state.
These scripts only work on about 8 tables at this point and delete based on some key values in a parent table.
I currently have a request to delete a 'case' from some base table. The issue is that we don't have the individual sql statements to backup all 80+ tables in the schema, therefore if I delete the record in question then the cascade deletes will remove data from tables we don't presently backup.
The issue I have with manually creating all the select statements is that there are so many, and doing it this way will be very time consuming and error prone.
I started writing a template for CodeSmith Tools which steps through all the tables determining relationships by the foreign keys, but ran into an issue when calling my function recursively. Also I've spent too long on it so far so I'd like to investigate a quicker solution.
Does anyone know of a method / tool which allows you to backup records from ALL tables in a schema where some 'base' tables has columna = @value1 and columnb = @value2 ?
I need to have the data separated into tables of data for reinsertion later if necessary, so a simple inner join of all the tables isn't sufficient.
Thanks,
Stephen York

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8109 More actions · Answer 1

why don't you just take a backup of the entire db in the normal way? then you can play around all you like, and if you mess it up, just restore from the backup.

doing a selective "backup" of only certain rows in certain tables seems risky and error prone to me.

---------------------------------------
elsasoft.org

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 738683

April 30, 2007 at 6:37 am

#703258

Take a backup and zip it.

Stephen York SSC Journeyman Points: 79 More actions · Answer 3

There are security implications with just doing an backup / extract on the entire schema.

We have to be able to justify every transaction on the database. Basically I authority to access a particular file / case because the work has been requested.

If I just go and back up the entire database, then I'm breaching privacy acts, etc.

Also, the amount of data is too big to be realistically doing a complete backup.

Any other ideas?

Thanks,

Steve

jezemine SSCrazy Eights Points: 8109 More actions · Answer 4

are you saying you don't have backups of this database? if so you have bigger problems to worry about.

you say there are privacy concerns - would you customers be concerned if all their data got trashed?

accidents happen, disks fail, etc. you really ought to be allowed to make backups. I have never heard of a real production system with no backup strategy.

---------------------------------------
elsasoft.org

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 5

If you just need schema, then I'd just script out the database. All tables, views, procs, users, security, etc. Basically check everything in the script box.

Stephen York SSC Journeyman Points: 79 More actions · Answer 6

I need the data as well for a particular base table record.

jezemine SSCrazy Eights Points: 8109 More actions · Answer 7

I'll say it once more and then stop. create a backup.

all good production databases have a good backup strategy. if you don't, you risk this: http://www.iht.com/articles/ap/2007/03/20/america/NA-GEN-US-Lost-Data.php

---------------------------------------
elsasoft.org

Stephen York SSC Journeyman Points: 79 More actions · Answer 8

Jezemine,

I thank you for your concern, but I'd like to explain why your suggestion here doesn't apply.

I work for a government department supporting an application. This application makes calls to an SqlServer database. I DO NOT manage this database infrastructure. We have a dedicated database team who look after the backup schedule, migrations etc.

Backups are not my concern.

The fact is that if I do a complete backup of a production database, containing loads of data I'm not authorised to view, then I will lose my contract here.

This has happened recently to many staff working for another government department who simply viewed data which they weren't authorised to view.

It's so serious that if we need to do any further testing on a production case, we have access to run predefiend scripts which perform a copy process on a set of data which obfuscates anything sensitive.

I am only authorised to view ANY data on a production server for which I have a of production support request assigned to me which specifies a client ID, etc.

If I query ANY other data not associated with this ID, then I'm breaking privacy laws.

So...Back to my question, does anybody know how to backup a table from a database and all related tables using Sql or some 3rd party tool?

I need to delete all data relating to a particular client id (in the base table) which cascade deletes all related tables.

The policy in my section is to run select statements on all tables which will be removed just in case we need to replace data in the event we or the person who made the request, got it wrong.

Our database team can 'rollback' to a daily backup point, but they can only do it globally, so any work which our thousands of business users have done would be rolled back as well, so this isn't an option.

To date we have only deleted records which cascades from somewhere down one branch of the table hierarchy. A recent request made to our team is unusual in that the entire job needs to be deleted.

I could go and create 80+ select statements with sub queries to pull out discrete tables of data for the client in question, but it's going to be an error prone copy and paste job over MANY hours which can't be repeated quickly and easily on another schema.

If anyone can offer any practical thoughts on this I'd be very grateful.

Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Stephen

Here's something that might work for you. It generates code which you can then run. It isn't perfect, since it doesn't traverse the whole hierarchy; if you need it to do that you will have to play about with self joins, I think. I have used SELECT * FROM statements for "backing up" the databases, but you could just as easily generate bcp commands instead.

DECLARE

@table sysname

SET @table = 'MyTable'

SELECT 'SELECT * FROM ' + @table

UNION

SELECT 'SELECT * FROM ' + OBJECT_NAME(fkeyid)

FROM sysreferences

WHERE OBJECT_NAME(rkeyid) = @table

John

Stephen York SSC Journeyman Points: 79 More actions · Answer 10

MATE!!!

Thank you

That gives me a great start. From this incredibly simple bit of code I can easily determine relationships, yay

I might use this as a recurring step in a c# application.

It greatly simplifies the CodeSmith templates I started working on.

Thanks again, I shall sing your praises daily to the people