question

Basit avatar image
Basit asked

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

log-shippingbackup-restore
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

4 Answers

·
Fatherjack avatar image
Fatherjack answered

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

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
+1 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.
2 Likes 2 ·
Basit avatar image Basit commented ·

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

0 Likes 0 ·
Basit avatar image Basit commented ·

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

0 Likes 0 ·
Leo avatar image
Leo answered

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?

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
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
0 Likes 0 ·
tommyjones avatar image
tommyjones answered
I would recommend you repair mdf file owing to following software [ http://www.sql.recoverytoolbox.com][1] utility restores databases since Microsoft SQL Server 2000 version [1]: http://www.sql.recoverytoolbox.com
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JoelBarry avatar image
JoelBarry Suspended answered
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. http://www.repairsqlserver.net
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.