<

How to Restore a Database from a Backup (SQL Server)

Posted on
12,460 Points
1,560 Views
19 Endorsements
Last Modified:
Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.
Experience Level: Beginner
5:15
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
19
Author:Steve Wales
4 Comments

Comment

by:enrique_aeo
Backup schedule
Full database backups Saturday 10PM
Differential backups Monday, Tuesday, Thursday, Friday at 10PM
Log backups every hour (on the hour) from 9AM to 6PM
Failure Occurred at  Thursday at 10:30AM
What restore process should be followed?

Question: If a failure occurs at Thursday at 10:30AM, what is the restore process that should be undertaken?
0
LVL 23

Author Comment

by:Steve Wales
First, if you're still able to access at least a part of the database, you would want to take a backup of the tail of the transaction log and save that.

You would restore your full backup from Saturday at 10, with the NORECOVERY option.
You would then restore the most recent differential which based upon your schedule is Tuesday at 10 PM, with the NORECOVERY option.
You would then apply every transaction log from 10PM Tuesday (that first one may have you an error about it not being needed) up until 9 AM Thursday, each with the NORECOVERY option.

Now, if you don't have a tail log backup, you restore your last transaction log backup from 10AM Thursday WITH RECOVERY and you are done with recovery to 10AM and 30 minutes of data loss.

If you do have the tail log backup available, you restore that 10 AM Thursday transaction backup with NORECOVERY and then restore the tail log backup and you're back to the point of failure with no data loss.
0
LVL 2

Comment

by:Alice Simpson
Very Informative video. Thanks for sharing knowledge.
0

Comment

by:Dana&Danny Acker

    0

    Suggested Videos

    Title Views Activity
    SQL - The SELECT Statement 220
    Query data with no SQL code (1) 47
    Query data with no SQL code (6) 24
    Query data with no SQL code (11) 60
    When running slow stored procedures from Access it may be useful to have the application do something else rather than just show the hourglass.   In this example a form flashes a message while the stored procedure executes, and then closes itself on…
    This article discusses various scenarios that can end up in a DBCC CHECKDB process getting stuck in a Killed/Rollback state and how to resolve the problem.

    Keep in touch with Experts Exchange

    Tech news and trends delivered to your inbox every month