Hur återskapar man en MS SQL databas till en viss tidpunkt?

Detta har troligen hänt de flesta. Nånting har hänt och nu är databasen korrupt eller så har det försvunnit information (typ nån (självklart inte du) har kört ett kommando som raderat nått som inte skulle tas bort)...

Tibor Karaszi har skrivit en riktigt bra post om detta för knappt två månader sedan.
Inte nog med att han förklarar hur det funkar utan även hur man gör för att laga det hela. :)

Det hela börjar så här:

This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster?



Yes, of course we can (unless for more extreme cases, read on), but many don't realize/do that, for some strange reason.


This blog post was inspired from a thread in the MSDN forums, which exposed just this misunderstanding. Basically the scenario was that they do db backup and only log backup once a day. Now, doing log backup that infrequent is of course a bit weird, but that is beside the point. The point is that you can recover all the way up to the point of disaster. Of course, it depends on what the disaster is (don't expect too much if the planet blows up, for instance).

Since "log backup only once a day" was mentioned, I will first elaborate a bit on frequency for database vs log backups. For the sake of discussion, say we do both db and log backup once a day. You say:


"What? Both db backup and log backup once a day - why would anybody do that way? Wouldn't one do log backup more frequently than db backup?"


Yes, of course (but I actually see such weird implementations from time to time). But again, that doesn't change the topic at hand, but I will first elaborate on this; just so we don't see blurring comments later arguing this irrelevant argument.

Hela posten hittar man här: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/03/27/restore-database-to-the-point-of-disaster.aspx