transaction log restore question

  • 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
  • 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.

  • 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

  • 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

  • 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

  • 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.

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

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