transaction log restore question

Question

transaction log restore question

Viewing 6 posts - 31 through 35 (of 35 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Brandie Tarvin (3/19/2010)
Unless... Are you treating it as a separate database in your query code?

Yes. A snapshot appears as a separate, read-only database. Hence, if you want to undo a change made in the source database and don't want to revert the entire thing, you do an insert/update and use the snapshot as the source database.

As an example

Update SomeTable

Set SomeColumn = Original.SomeColumn

From SomeTable INNER JOIN Snapshot_Database.dbo.SomeTable AS Original On SomeTable.pk = Original.pk

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 2

Thanks, Gail. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 3

I've used Apex SQL Log and Apex SQL Recover and it works perfect for just such a situation as this. I read Lumigent is a good product as well. Not very expensive either.

my 2-bits....

Tim White

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Brandie Tarvin (3/19/2010)
Paul,
It isn't a silly question. Developers should not have that ability on Production. In fact, there should be a tight throttle on the # of people who do have production rights and most of them should be read only when they are allowed.
Question for all others, how does one update a database from a snapshot without doing a RESTORE command? I've used snapshots quite regularly and never seen an UPDATE command for a snapshot.
Unless... Are you treating it as a separate database in your query code?

I agree with this. However, there are politics typically involved when a developer has access to drop and create objects directly in production. That is not a good reason, but is frequently the case (when devs have access), IMO.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

2 Tim 3:16 (3/19/2010)
I've used Apex SQL Log and Apex SQL Recover

Glad to hear somebody was able to get it to work. I tried these in a couple of situations and it never worked properly.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

BalasundaramRangasamy SSC Veteran Points: 208 More actions · Answer 6

I have written a database trigger to track the schema changes in each database. In this case, I think we can write a database trigger which can disallow if it finds any drop object event.