March 15, 2010 at 6:01 pm
Hi,
I have a test setup 2x SQL 2K8 servers.
I setup log shipping by making the Db backup, restoring it with "NO RECOVERY" using the management studio on the \\TEST-SQL02 server.
It sat there with DBname (RESTORING...) which to me said it was "replaying the log files" as they were being shipped over ?
This is the same as the production SQL server setup which i inherited by the previous admin, so i wanted to replicate the same.
I left \\TEST-SQL01 and \\TEST-SQL02 for about 30 hours, and the logs on \\TEST-SQL02 were down to about 24 hours, which to me said that everything was working as it should.
Time for bringing \\TEST-SQL01 down and restoring / activating \\TEST-02.
I shut the \\TEST-SQL01 machine down, making the Db unavaliable, i then on \\TEST-02 opened the management console and ran a query,
RESTORE DATABASE DBname WITH RECOVERY
I think i did a typo but then i got
Msg 3153, Level 16, State 2, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The Db was then "showing" as online, in SQL manager, but i wasn't sure if it really was, as my web server was struggling to connect to it,
I also noticed that there were heaps of LOGs still in the log shipping folder.
so , can someone please give me a "dummys guide" to what is actually happening with the different methods or Restore eg. why one is showing (Standby / Read Only)(Recovering...) and what is supposed to happen with all of the extra log files ?
Are they supposed to slowly inject into the Db on the seccond server ?
Thanks
March 17, 2010 at 2:03 am
Hi,
First manuvally copy the transaction logs from primary server to secondary server.and apply the transactions logs using restore commands.......
now u can check it ......
March 17, 2010 at 4:26 am
I worked out that using
RESTORE DATABASE <my DB name> WITH RECOVERY
and refreshing the console, puts it into normal mode.
I then was able to run queries on it.
Im just a little confused how the log shipping works.
I was thinking it was a DB Backup, then adding all the incrimental logs to build up the same state as server #1.
But when server #1 crashes / offline etc. server 2, I run (RESTORE DATABASE <my DB name> WITH RECOVERY) and great its online, what happens with the remaining logs in the TranLogs folders?
Do they auto-insert when i run the (RESTORE DATABASE <my DB name> WITH RECOVERY ) command ?
Or do they slowly add in over the next x amount of time ?
Thanks in advance
M
March 17, 2010 at 5:57 pm
Im just a little confused how the log shipping works.
I was thinking it was a DB Backup, then adding all the incrimental logs to build up the same state as server #1.
But when server #1 crashes / offline etc. server 2, I run (RESTORE DATABASE <my DB name> WITH RECOVERY) and great its online, what happens with the remaining logs in the TranLogs folders?
Do they auto-insert when i run the (RESTORE DATABASE <my DB name> WITH RECOVERY ) command ?
Or do they slowly add in over the next x amount of time ?
For eg. When you do log ship from production instance [Primary] to DR (Disaster Recovery) instance[Secondary], off course it is DB backup which restored in the secondary either in NoRecovery /Standby Read Only mode, only on these states the secondary database will allow to apply incremental Tlogs on it.
When you want to failover from primary to secondary instance you've to ensure all the Tlogs and Tail Logs applied on the secondary before you recover the Secondary database [RESTORE DATABASE <my DB name> WITH RECOVERY]. Read more on http://msdn.microsoft.com/en-us/library/ms191233.aspx
But when server #1 crashes / offline etc. server 2, I run (RESTORE DATABASE <my DB name> WITH RECOVERY) and great its online, what happens with the remaining logs in the TranLogs folders?
As per your question, I don't think that the secondary database was in sync with the primary, which has to be taken care before you recover the secondary database. Once you recovered then it will be ready for new data from users but you can not think about to apply Tlogs on top of it.
Hope I clarified your doubts...
Thanks
Jay
http://www.sqldbops.com
March 17, 2010 at 6:16 pm
ok i think you have cleared up allot.
Can you confirm my thoughts then.
- SVR#1 running happily, shipping logs to SVR#2 (Folder contains about 24hours worth of logs) and on SVR#2 Db showing (Restoring...)
- SVR#1 crashes, SVR#2 still has Db in (Restoring...) and 24hours worth of log files.
- Connect to SVR#2, start bringing the Db upto speed by restoring the log files (24 hours worth) in their order with command
RESTORE LOG <database_name> FROM <DB_20100317053003.trn> WITH NORECOVERY
RESTORE LOG <database_name> FROM <DB_20100317063002.trn> WITH NORECOVERY
etc. etc. untill i am at the last Db Transaction log, then run the following
RESTORE DATABASE <database_name> WITH RECOVERY
- SVR#2 Db now showing up correctly in the SQL Manager.
?
Does that sound correct ?
Also, as i restore the TRN logs, do they dissapear from the backup folder ?
Thank you
Mat
March 17, 2010 at 8:45 pm
- SVR#1 running happily, shipping logs to SVR#2 (Folder contains about 24hours worth of logs) and on SVR#2 Db showing (Restoring...)
When you setup LS, you can schedule the Backup (SVR#1), Copy& Restore(SVR#2) jobs every 15mins(default) or every 5mins based on your requirement, if so then 95% of time there will not be any latency in restoring Tlogs in SVR#2, it may be 15 to 30mins delay but not 24hrs unless there is a critical issue on SVR#2 side.
As I said when you setup LS, you can keep database in NORecovery (which keep the db always Restoring state) or Standby/ReadOnly state (which allows the users to read/use the db when there is not restore activity on the db).
- SVR#1 crashes, SVR#2 still has Db in (Restoring...) and 24hours worth of log files.
- Connect to SVR#2, start bringing the Db upto speed by restoring the log files (24 hours worth) in their order with command
RESTORE LOG <database_name> FROM <DB_20100317053003.trn> WITH NORECOVERY
RESTORE LOG <database_name> FROM <DB_20100317063002.trn> WITH NORECOVERY
Even your SVR#1 crashed but in SVR#2 the LS restore job will be there and you can run it continuously to restore all the pending Tlog files instead of applying manually.
etc. etc. untill i am at the last Db Transaction log, then run the following
RESTORE DATABASE <database_name> WITH RECOVERY
- SVR#2 Db now showing up correctly in the SQL Manager.
?
Once you ensured that all the Tlogs applied on SVR#2 then execute the above commands to recover the db.
Does that sound correct ?
Also, as i restore the TRN logs, do they dissapear from the backup folder ?
LS restore job only will delete the Tlog files on the destination folder (SVR#2) based on your Tlog Delete Files after period, which you specify when setup LS.
Thanks
Jay
http://www.sqldbops.com
March 17, 2010 at 9:05 pm
Ahh yes, i see on SVR#2 the "LSRestore_SVR#1_DBName
I have looked in the Job History of this, and you can pin-point the LOG files that HAVE been restoring into the Db by filename.
(that was going to be my next question).
🙂
I think im now pretty right with this, for some more testing and breaking / repairing.
🙂
Thank you very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply