Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to restore from a shipped Transaction log Expand / Collapse
Author
Message
Posted Wednesday, August 14, 2013 8:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 12:09 PM
Points: 8, 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
Post #1484562
Posted Thursday, August 15, 2013 2:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 39,867, Visits: 36,212
May I suggest you use the built in log shipping functionality? Much easier.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1484645
Posted Thursday, August 15, 2013 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 12:09 PM
Points: 8, 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
Post #1484681
Posted Thursday, August 15, 2013 5:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 39,867, Visits: 36,212
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 2008, MVP
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

Post #1484688
Posted Thursday, August 15, 2013 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 12:09 PM
Points: 8, 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
Post #1484864
Posted Thursday, August 15, 2013 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 39,867, Visits: 36,212
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 2008, MVP
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

Post #1484870
Posted Thursday, August 15, 2013 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 12:09 PM
Points: 8, 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.
Post #1484885
Posted Thursday, August 15, 2013 12:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 39,867, Visits: 36,212
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 2008, MVP
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

Post #1484889
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse