SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to restore from a shipped Transaction log


How to restore from a shipped Transaction log

Author
Message
Procmeister
Procmeister
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 44
Hello,

I've created a backup and restore schedule as so, and the jobs are working properly:

Server 1
~~~~~
- Weekly full backups on Sunday at 12am
- Daily differential backup at 6am
- Transaction log backups every 10 minutes from 6am - 6pm

A final step of each backup job xcopies the backup device to a 2nd server, Server 2, a read-only copy of all the databases.

The jobs are all working properly on Server 1, and I'm receiving the appropriate e-mail notifications.

My questions surround the restoring of the databases on S2.

I was able to create a contrived restore scenario by manually:
- Restoring from the full with NoRecovery,
- Restoring from the differential with NoRecovery, and
- Restoring from the transaction log with Recovery.

This was a one time scenario to get it working, but I need jobs that do these restores.

My first question is that if I try to restore from the transaction log backups, and there are no transactions, I get an error message.

Could someone provide a general direction or give me some advice?

I implemented this on SQL Server 2008 before I noticed the Databases --> Tasks --> Ship Transaction Logs facility.

Thanks very much,

Richard
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88647 Visits: 45284
May I suggest you use the built in log shipping functionality? Much easier.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Procmeister
Procmeister
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 44
Hi Gail,

Thank you very much for your response. I have been thinking about doing that but I'm wondering:

- Once I've done that how does it work? Will I see new jobs setup? Is a new database created? Will I be able to get an email if shipping fails?
- Can shipping work effectively at 5 minute intervals? Both servers are local, in the same building.
- Will all the databases on the standby server be marked with a "read-only" or other status?
- If the primary server fails:
- What steps do I take to make the standby server active?
- If the standby becomes active and inserts/updates/deletes occur, what steps do I take to propagate the activity over to the primary server once it is active again?

Is there a book you can recommend?
Are there any other things I should keep in mind? The servers will soon be migrated to SQL 2012.

Thanks very much,

Richard
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88647 Visits: 45284
Procmeister (8/15/2013)
- Once I've done that how does it work? Will I see new jobs setup? Is a new database created? Will I be able to get an email if shipping fails?


Yes

- Can shipping work effectively at 5 minute intervals? Both servers are local, in the same building.


Yes

- Will all the databases on the standby server be marked with a "read-only" or other status?


Yes, if you set it for Standby. Restoring (unavailable) otherwise

- If the primary server fails:
- What steps do I take to make the standby server active?
- If the standby becomes active and inserts/updates/deletes occur, what steps do I take to propagate the activity over to the primary server once it is active again?


Pretty much the same steps you'd take if you were doing this all manually, restore the standby with recovery, then restore log backups back to principal once it comes back

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Procmeister
Procmeister
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 44
Hi Gail,

Thanks for your support.
I have setup Transaction Log Shipping specifying the Primary server, and Secondary Server. When setting things up, there were two options for "Leave the DB in", NoRecovery or StandbyMode (Read-only operations). I selected StandByMode.

It ***appears*** that the database log shipping jobs are working on both Primary and Secondary, as I am looking at the logs for them on both servers, and all steps are completing with no errors.

However, I cannot check that the data is in a table for testing, because I cannot connect to the database. When I run 'Select DATABASEPROPERTYEX('Test_Site', 'Status')', I get 'RESTORING', and I can't "Use" it.

How may I "Resync" everything on the Secondary server and get the database into "Standby"?

Thanks a ton,

Richard
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88647 Visits: 45284
If you selected standby, the database should be in the standby state. Recovering is when you select the NoRecovery status.

Standby's only if you need to read the database for any reason, if you don't need to run reports on it, leave it in standby. If the logs are restoring (and that you can see in the error log or MSDB tables) then the secondary is up to date as of the last log it restored.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Procmeister
Procmeister
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 44
On the Secondary server, I have executed "RESTORE DATABASE [MyDatabase] WITH RECOVERY", so it is no longer in "Restore" mode, so I can now connect to the database.

I am looking at the table I expect to have a whole bunch of new rows, but they have not been transferred from the primary server.

I wonder what I should do now.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88647 Visits: 45284
Since you've recovered the database, you can drop it and reconfigure the log shipping from scratch.

The secondary will be up to date as of the last log backup copied and restored. You can see the logs restored from the MSDB tables, the error log and there should be some data on the log shipping properties, though it may not show the last log backup restored, I can't remember.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search