August 14, 2013 at 8:34 pm
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
August 15, 2013 at 2:43 am
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
August 15, 2013 at 4:49 am
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
August 15, 2013 at 5:02 am
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
August 15, 2013 at 11:43 am
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
August 15, 2013 at 12:01 pm
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
August 15, 2013 at 12:29 pm
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.
August 15, 2013 at 12:41 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply