How to Restore the Database from Logshipping

Dear All,

I have configure the Log shipping on my Two SQL Server 2005 Machine. Every Thing goes fine , All log file are transferring from Primary to secondary server , I have made some change in the Primary Server ie ( Creation of Table , Insert Some Value in that table ) Now i want to restore my Secondary Database.

The Secondary database is showing in Restoring state ,

Please help me that how can i restore my secondary database..

Thank in Advance

Basit Khan Jn DBA GLT - Mumbai

more ▼

asked May 26, 2010 at 07:22 AM in Default

avatar image

82 7 7 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Essentially you need to run RESTORE DATABASE database_name WITH RECOVERY.

There is a pretty full explanation of log shipping and restoing the database for use here: http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/d3bfd116-2b6a-4253-9b9e-ff896348572d

more ▼

answered May 26, 2010 at 07:46 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

  • Just don't do that until you need to use the database. Keep it in this state to continue restoring the shipped logs. If you need to fail over to this database, THEN restore the last logs and recover it.

May 26, 2010 at 01:16 PM David Wimbush

Dear Jack...

I have Restore the Secondry database with the Query as you have said . The Database restore succesfully ... but the change that i have made in the primary databse has not refecled.. As i have created a "Name" table in primary database all log of Primary database are copy to secondry DB. Now when i use select statement in Secondry DB it give a error : Msg 208, Level 16, State 1, Line 1 Invalid object name 'Name'.

Can you tell me why the change has not replicate in Secondry DB

May 27, 2010 at 02:21 AM Basit

Thanks Jack,

It Working now....

Problem was i am not Restoring the Last Transaction Log File.... Thanks once again...

Basit Khan Jn DBA GTL-Mumbai

May 27, 2010 at 02:54 AM Basit
(comments are locked)
10|1200 characters needed characters left

I would recommend you repair mdf file owing to following software http://www.sql.recoverytoolbox.com utility restores databases since Microsoft SQL Server 2000 version

more ▼

answered Oct 29, 2012 at 02:58 PM

avatar image


(comments are locked)
10|1200 characters needed characters left

While working with corrupt Microsoft SQL Server, you come across unexpected behavior of SQL Server. In order to resolve such issues of SQL Server in an efficient manner, RecoveryFIX for SQL Database Recovery is one such proficient third party tool. By using this tool, you can easily recover your lost, corrupt or inaccessible SQL database files from all the versions of the corrupt Microsoft SQL server.


more ▼

answered Mar 01, 2013 at 09:53 AM

JoelBarry (suspended)
(comments are locked)
10|1200 characters needed characters left

Hi All,

I am bit confused with Basit question. Does he really need to restore the whole database on Secondary? As long as Log Shippping is on, whatever you made changes (creating tables, adding records, update, etc..) will reflect to Secondary. Is that correct, isn't it?

more ▼

answered May 26, 2010 at 08:34 AM

avatar image

1.6k 55 59 62

All the data will be there but the database will not be available for application access, it is in a recovering state - permanently waiting for more log files to be applied to it. When you want to use this database you have to set it to an online state by applying a final restore WITH RECOVERY. Take a look at the details in Books OnLine for the full details

May 26, 2010 at 08:51 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 26, 2010 at 07:22 AM

Seen: 14492 times

Last Updated: Apr 14, 2013 at 10:22 AM

Copyright 2018 Redgate Software. Privacy Policy