| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2004-06-04 : 10:39:35
|
| Did I ask this already?Other than EM, is there a way to script a database?Anyone?Is it Friday yet?Brett8-) |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-04 : 11:08:13
|
| What do you mean by script a database?Like generate a script to recreate the database??You could always build one... I built a vb app that mimics EM to manage my SQL Server from remote locations. Its all done through tsql.Corey |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 12:58:02
|
| Brett, why other than EM? Does EM scripting wizard not work right for you? Or do you just want an alternative to it?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-04 : 13:10:01
|
| Here's the thing....Due to all the concerns with data privacy...we've been asked to build a process that will scramble production data so it can be used for development....I know...production data can never be a good test bed if you ask me...falls on deaf ears...So...I built a process in sql server that you can point at any database and facilitate the bcp outs...ftps to the scrambling "server" (mainframe), ftp down, bcp back in...it's the back in component...Need to have a new destination db....would like to make that part of the housekeeping script...the building of an empty destination db...might be more trouble than it's worth...and/or dangerous...just wondered how EM does it....xp_something?Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-04 : 13:11:09
|
| Did I fail to mention that the first one I have to "scramble" is 15 GB and over 700 tables?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 13:16:58
|
| I do not believe that EM uses any extended stored procedures. I've heard there is a command line version of the wizard available with the SQL Server 2000 Resource Kit. I've never used or seen it though. It's something like sqlscr.exe. Do you have to scramble all data or just the sensitive stuff? I can't imagine that everything would be sensitive even in an HR database. How long does your code take to execute? I imagine to build 15GB of data that it is quite slow, no offense. Seems to me that you would backup/restore, then scramble bits and pieces of the data. Anyway, that's how we do it here. We've got very little sensitive stuff in the dbs that I support. Only GPS info is considered sensitive. Easy enough for us to generate lat and lons.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-04 : 13:25:36
|
| None taken....can't dump and restore...Yes it's only sensetive stuff...SSN, DOB, Address, ectAnd yes the mainframe scrambling component is slow....and yes ftping up will take time...currently it's serialThing I'm gonna alter a job and change ftp and execute it so it can thread...Might be too much strain on the box though....So you take the db to another location...and then have a sproc scramble it?I might have to convice them to have a mainframe componenet and a separate client server component (yeah, right),Here's another question (oh and thanks) ever bcp out from a linked server like Oracle, sybase, udb?Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 13:29:34
|
| Yes we take the db to another location then scramble it. Never bcp'ed from a linked server. We are going from SQL to SQL, so it's easy for us. So how long does the current process take? Just curious of course.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 13:30:55
|
| Yeah I've seen his script before. I've got to get into that DMO thing. That's one thing I've never done. The other being Analysis Services.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-04 : 13:34:47
|
quote:
Originally posted by tduggan
Yeah I've seen his script before. I've got to get into that DMO thing. That's one thing I've never done. The other being Analysis Services.Tara
Damn that's scary...same holes here too...I'm gonna give Nigels script a whirle...against Northwind...Since it's already written....Let you know how it goes....Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-04 : 15:02:24
|
| I imagine this is completely barking, but.Could you just RESTORE the DB to DEV, rename all the tables, and put VIEWs in their place [i.e. declared as the original table names but referencing the "new" table names] using some one-way conversion UDF on the sensitive data columns?No need then for the effort of encryption at the time of DB transfer.Have to keep the original tables from prying eyes though.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-04 : 15:49:32
|
| The req is that the data be scrambled by a mainframe process already built....yeah, I know....feel for me...I gotta work with'emThey'll never admit it's wrong until it collapses under it's own weight....I don't think they expected anyone to automate a process to port 700 tables, generate jcl, ect...I created a jcl genertor using a template...so whatever changes they throw at me, it's a simple changeI think they were expecting to have to code all the jcl...700 sets...probably 2 or 3 a day...good gig...Now they're out of business...They have to type "sub"Might have to gen a rexx or clist so they don't have to do that either...Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-05 : 03:07:07
|
| Mainframe eh? My grandad told me about them.Sounds like INFORMATION_SCHEMA.COLUMNS is your friend ... and you can add SELECT POWER(InvoiceTotal, @SomeNumberBiggerThan1)to your invoiceKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-07 : 08:46:52
|
quote:
Originally posted by Kristen
Mainframe eh? My grandad told me about them.
How long you been in the business?You probably remeber punch cards....I do...Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-08 : 15:20:20
|
quote:
You probably remeber punch cards....
Blimey, were you there? Do you remember Wendy who used to put the punch cards into the ICL?That's how I got into this darn mess ....Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-08 : 15:32:06
|
quote:
Originally posted by Kristen
quote:
You probably remeber punch cards....
Blimey, were you there? Do you remember Wendy who used to put the punch cards into the ICL?
Ah yes Wendy...how's the old girl....I think she's still sorting the tray she dropped....Gave me a...well it wasn't a computer really...to monitor the jobs from home....1200 baud...man that sucker flew....Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-09 : 12:39:31
|
| :-)Kristen |
|
|
|
|
Script DB with T-SQL?