Recovery to a Point in Time

  • What about a media failure, and backup the log file is impossible.

    Can't we recover the database to the time just before this happened, like Oracle can do by using it's archivelog files?

  • If you mean that the database goes away due to a media failure and you were unable to backup the log file, then you can only restore up to the last good log file backup, when ever that was.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (2/10/2009)


    If you mean that the database goes away due to a media failure and you were unable to backup the log file, then you can only restore up to the last good log file backup, when ever that was.

    That's exactly what I want to confirm.

    I know Oracle has the concept of incomplete restore and point-in-time recovery by cancel, by time, etc, by using archive log. Which in that case, even if you didn't setup any maintenance plan for any scheduled backups, you can still use Oracle internal automatic archive log files to restore the database to point-in-time.

    In SQL Server, it doesn't have the similar term?

  • No, not really. Without a log backup of some sort, there's nothing to restore from for point in time recoveries. You have to set up maintenance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Great article, thanks!

    I keep a folder on the server called "Disaster Management". It contains everything I need in an emergency to keep my head 🙂 : scripts to find current executing processes, to display server configuration settings, to kill all connections and establish DAC connection, to enumerate log files, and to perform the restores.

    This was a good discussion/presentation of killing connections and setting a DB to single user mode:

    http://www.sqlservercentral.com/scripts/Maintenance/62619/

    [/url].

    HTH,

    Rich

Viewing 5 posts - 16 through 19 (of 19 total)

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