August 18, 2011 at 1:00 am
Hi
Is there a way to read the sql that was used in a log? And also if possible see if the sql in the script is for new data or a structure.
I'm not really looking for full how to read this, maybe just what I should google to get information on how to do this
Thanks in advance 😀
The Fastest Methods aren't always the Quickest Methods
August 18, 2011 at 1:10 am
August 18, 2011 at 2:25 am
Sorry, What I'm looking to do (if it's possible) is to read through the transaction logs and get all the sql that is executed to create the specific log.
In the end I wish to be able to make a backup, then make lots of changes and if everything works as expected get all the sql from somewhere (I thought it would be possible from transaction logs) and then use those scripts on the production database.
The Fastest Methods aren't always the Quickest Methods
August 18, 2011 at 2:33 am
There are tools you can buy to do that. The cheapest is around $1000
The log is not meant to be human-readable, it does not contain any T-SQL, just the effects of the changes made.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 2:58 am
Would the only way to get these changes be the way RedGates application does it by comparing 2 databases and then telling what is differences are between the 2 databases?
or is there another way to get this type of information?
The Fastest Methods aren't always the Quickest Methods
August 18, 2011 at 3:07 am
BinaryDigit (8/18/2011)
Sorry, What I'm looking to do (if it's possible) is to read through the transaction logs and get all the sql that is executed to create the specific log.In the end I wish to be able to make a backup, then make lots of changes and if everything works as expected get all the sql from somewhere (I thought it would be possible from transaction logs) and then use those scripts on the production database.
What you need a source control software not a log reader. A standard practice in developement companies is to checkin code to a source control such as TFS , SVN etc. These softwares generate version numbers for each code you check in and therefore allows you to go back in time to pick any paticular version of the code you want.
The approach of reading from a tran log is likely to cause issues because it also contains DML; besides if you really want a better option would be to create a DDl trigger and have it check for any create , alter or drop statements.
The Tran log is not meant for the kind of task you want.
August 18, 2011 at 3:13 am
Depends. What kind of changes are you making and why can't you just apply them to the production DB later?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 3:35 am
We have SVN running for our .net code. Is it possible to intergrate SVN into Management studio?
The Fastest Methods aren't always the Quickest Methods
August 18, 2011 at 3:42 am
The Developers don't always script all their changes and then after making a upgrade to a system we left without a script or 2 sometimes so if we were able to get all these changes by getting changes from the last backup we wouldn't have these problems anymore.
The Fastest Methods aren't always the Quickest Methods
August 18, 2011 at 3:47 am
BinaryDigit (8/18/2011)
We have SVN running for our .net code. Is it possible to intergrate SVN into Management studio?
Have a look at RedGate's SQLSourceControl
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 3:49 am
BinaryDigit (8/18/2011)
The Developers don't always script all their changes and then after making a upgrade to a system we left without a script or 2 sometimes so if we were able to get all these changes by getting changes from the last backup we wouldn't have these problems anymore.
That's a discipline and management problem, not a technical problem. Solve it via management (developers leaving out scripts are being careless and unprofessional), not via technology.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 3:51 am
August 18, 2011 at 4:33 am
GilaMonster
I agree that it is careless of the Developers to leave scripts out, but if I could create an application that will accomplish what I want it to do. Lots of problems can be solved and we (the company I work for) can make money selling the product.
Thanks for the source control name, will take a look at it.
Jayanth_Kurup
I'm basically going to create my own db compare tool probably and hopefully learn a lot of things about SQL in the process.
The Fastest Methods aren't always the Quickest Methods
August 18, 2011 at 4:40 am
August 18, 2011 at 4:44 am
BinaryDigit (8/18/2011)
I'm basically going to create my own db compare tool probably and hopefully learn a lot of things about SQL in the process.
I hope you have a few months spare. That's not a trivial app (I tried it once, gave up after a few weeks of solid work)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply