rhat (10/15/2007)
R L Reid (10/15/2007)
Posts: 1, huh? It's obviously a troll, but I'll bite.rhat (10/15/2007)
DBCC commands are wild goose chases when a siimple restore of backup is what's needed and needed ASAP.Yes, let's just throw away the past 19 hours worth of data and overwrite. (Knock wood - I don't have issues like this because of the many layers of replication we use...but when it used to happen, there was usually discussion about how long to work on recovery before bailing out, based on business needs).
A *PROPERLY* designed system doesn't find out problems 19 hours later. A good system knows instantly, in seconds, when an error has occurred, Windows or Web, and sends an alert message to the admin and/or developer via e-mail, text message, etc......
ALSO, a properly designed database and application doesn't keep sticking bad data in the database and not let those users not know that something wrong has happening to begin with. OR, for that matter keep letting them work with a bad database for 19 hours!!!!
Secondly, it's best to go to the last known good backup just for reliability reasons. Just because you got a DBA that knows how to use DBCC doesn't' mean the database will be 100% normal again. And it could take the DBA days, weeks to figure it out and get all the errors and bad data out regardless of expertise and experience. Errors that require DBCC almost always don't just happen. There is a reason for it and it's usually BAD. And after that, there still could be problems.....i.e. wild goose chase.
Thirdly, who is to say that those 19 hours of data is GOOD data to begin with? How can one be 100% sure without rechecking it with the user who put it in there?
Four, restore is *like* using the last know good Windows OS image or the *appropriate" backup data instead of trying to get the virus out or running repair commands or wizards. In the real world, we get fast enough hardware to get this done ASAP.
Five, All that money spent on some clueless DBA that's an expert in DBCC could have been spent on faster hardware and backups to complete the job a lot faster in the end. Not to mention make the system as a whole more reliable.
But of course, DBCC, DTS and JOB SECURITY seem to go hand in hand:D
As far as DTS/SSIS goes, yes, you can use T-SQL to accomplish many of the same tasks, but a well-built DTS package can accomplish repeated ETL tasks more efficiently, assuming you are moving data from things like flat files (.txt or .xls, for example) into complex relational structures. The ability to map different transformations/actions to different results of actions, using a flow-chart style structure, combined with easily set up things like ForEach loops, complex error-handling and alerts, and ease to maintain, which can be moved from server to server simply by changing a single connection string, does have its advantages.
I've used both solutions (pure T-SQL stored procs and SSIS) and there are advantages and disadvantages to each. The choice isn't about job security (the company I worked for went out of business, and in a matter of days, the biggest competitor wanted me to take over the same job, but for them; I've got all the job security I could ever want). The choice is about effectiveness and efficiency and ROI (always ROI).
Just in the last 2 weeks, I've taken tasks that were taking man-days of labor, and using SSIS, turned them into "drop an Excel file in this folder and the server will do the rest". One went from 2 people working full time for 4 days, to under 2 minutes. With SSIS, it took me about 2 hours to set the whole thing up. And it will be used dozens of times in the coming months. Setting it all up in T-SQL would have taken much, much longer, and would be much more difficult to port to other servers.
Another task, a year or two ago, I set up in T-SQL for similar results, but it was cleaner to do that one without DTS. Simple .txt bulk import and then a bunch of set-based parsing and transformations.
On the other hand, I completely agree with you about memorizing rarely used DBCC commands in SQL 2000/2005. In six years, I've used them twice, and both times I had time (a minute or two) to look them up first and make sure I was using them correctly. I'd much rather memorize things I'll use constantly (the table names, for example), and things I'll use regularly (string and date functions, for example), than things I'll use twice in six years (DBCC commands). (Of course, I use DBCC commands all the time, but I use them by clicking buttons and right-click menues in management studio, etc., not by typing them out myself.)
As for losing 19 hours of data because of restoring to a last-known-good-point instead of using esoteric database commands to try to fix something, whoever thinks those are the only two options is to stay away from my databases. Far away! Please, go work for my competitors. They need you!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon