Q
Problem solve Get help with specific problems with your technologies, process and projects.

Database not completing restore process, remaining in loading mode

I have done a full back up weekly and implemented differential back up daily and transactional back up every 3hrs. If I try to restore from back up, I have a problem. I am restoring in the sequence that I did back up -- first, full back up with no recovery, second, differential with no recovery and third, transactional backup with recovery. But it is not completing the process, and still the database is in loading mode only. What may be the problem?

The problem may be all the necessary transaction logs were not restored. When restoring transaction logs, all logs between the last full back up or differential need to be restored in sequential order.

The process for doing a restore is:

  1. Apply a full database back up using the "WITH NORECOVERY" option.
  2. Apply the differential back up that is closest without going over the date and time to which the database should be restored; again use the "WITH NORECOVERY" option.
  3. Apply every transaction log back up taken between the differential applied in Step 2 and the date and time to which the database should be restored. Use the "WITH NORECOVERY" option on every transaction log back up except the last.
  4. Apply the last transaction log using the "WITH RECOVERY" option.
Example:

Using the schedule supplied in the question (making the assumption the full back up happens on Sunday and the differentials occur each day at 4:00 PM); to restore the database to Thursday at 12:15 PM on Friday:
  1. Apply the most recent weekly backup using the "WITH NORECOVERY" option.
  2. Apply the differential backup from Wednesday at 4:00 PM using the "WITH NORECOVERY" option.
  3. Apply every transaction log between Wednesday at 4:00 PM and Thursday at 12:00 PM, again use the "WITH NORECOVERY" option.
  4. Apply the last transaction log up to 12:15 using the "WITH RECOVERY" option.

 

For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close