February 19, 2014 at 7:35 am
Hi,
In Log Shipping suppose there is one database of 500 GB. If one of the .trn file which exist at secondary server but it is not restored and it got deleted accidentally by someone. Now the same file does not exist on primary server because it is automatically deleted via log shipping.
Since one .trn file is missing 'out of synch' error will be shown is Log Shipping. In this type of case or situation whether we need to configure Log Shipping from scratch i.e. take Full backup and restore on secondary server or is there any solution to synch primary and secondary database without reconfiguring Log Shipping from scratch.
Thanks in advance,
Nikhil P Desai
February 19, 2014 at 10:32 am
you don't have to reconfigure from scratch, you can use the existing logshipping jobs and shares etc, you just need to reinitialise the database with a norecovery restore. That in most cases will be a full backup but you can use a differential if a further full backup has not been taken since the gap in the log chain. thus resetting the differential base LSN.
---------------------------------------------------------------------
February 20, 2014 at 2:25 am
george sibbald (2/19/2014)
you don't have to reconfigure from scratch, you can use the existing logshipping jobs and shares etc, you just need to reinitialise the database with a norecovery restore. That in most cases will be a full backup but you can use a differential if a further full backup has not been taken since the gap in the log chain. thus resetting the differential base LSN.
You don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
February 20, 2014 at 3:18 am
MysteryJimbo (2/20/2014)
george sibbald (2/19/2014)
you don't have to reconfigure from scratch, you can use the existing logshipping jobs and shares etc, you just need to reinitialise the database with a norecovery restore. That in most cases will be a full backup but you can use a differential if a further full backup has not been taken since the gap in the log chain. thus resetting the differential base LSN.You don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
please read my post again, fully this time.
---------------------------------------------------------------------
February 20, 2014 at 3:30 am
MysteryJimbo (2/20/2014)
george sibbald (2/19/2014)
you don't have to reconfigure from scratch, you can use the existing logshipping jobs and shares etc, you just need to reinitialise the database with a norecovery restore. That in most cases will be a full backup but you can use a differential if a further full backup has not been taken since the gap in the log chain. thus resetting the differential base LSN.You don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
George is correct, the Differential backup would only be useful if no full backup has been taken against the primary since the log shipping failure occurred.
If a full backup on the primary has occurred the differential base LSN would have been incremented and any diffs would fail to restore to the secondary server. Only a re initialisation would be possible
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2014 at 3:42 am
george sibbald (2/20/2014)
MysteryJimbo (2/20/2014)
george sibbald (2/19/2014)
That in most cases will be a full backupYou don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
please read my post again, fully this time.
No, I still disagree. Most cases I would expect a differential to be sufficient if your enterprise uses them. The most common use for differential is to take frequent diffs and a infrequent full backups meaning it is far more likely you will have a useful diff than a full simply due to frequency.
February 20, 2014 at 4:02 am
MysteryJimbo (2/20/2014)
george sibbald (2/20/2014)
MysteryJimbo (2/20/2014)
george sibbald (2/19/2014)
That in most cases will be a full backupYou don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
please read my post again, fully this time.
....Most cases I would expect a differential to be sufficient if your enterprise uses them..... .
thats a big if and not what you said. It read as if you had assumed I had not mentioned the differential option
---------------------------------------------------------------------
February 20, 2014 at 4:07 am
george sibbald (2/20/2014)
MysteryJimbo (2/20/2014)
george sibbald (2/20/2014)
MysteryJimbo (2/20/2014)
george sibbald (2/19/2014)
That in most cases will be a full backupYou don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
please read my post again, fully this time.
....Most cases I would expect a differential to be sufficient if your enterprise uses them..... .
thats a big if and not what you said. It read as if you had assumed I had not mentioned the differential option
I would question the need of logshipping if you don't require differential backups. If you can perform a full backup daily it may well be just as quick to restore it.
February 20, 2014 at 4:09 am
MysteryJimbo (2/20/2014)
No, I still disagree.
Really??? You wouldn't be the first :Whistling:
Perform the following and read my guide at this link[/url], then tell me if you still disagree
Attempting to restore the 3rd log or the diff will fail. The 3rd log will fail since i threw the 2nd away, the diff fails as the base LSN has been incremented.
The following query
select name, differential_base_lsn, differential_base_time from sys.master_files
where database_id = DB_ID('yourdb') and type_desc = 'rows'
On my system shows
Source db
mysourcedbfile130380000158304001452014-02-20 10:58:16.853
Target db
mytargetdbfile130380000158254000372014-02-20 10:58:09.910
MysteryJimbo (2/20/2014)
Most cases I would expect a differential to be sufficient if your enterprise uses them. The most common use for differential is to take frequent diffs and a infrequent full backups meaning it is far more likely you will have a useful diff than a full simply due to frequency.
Which is why i said as long as no full backup has been taken in between the LS failure 😉
In my experience the first panic button people hit with broken LS is "oh let's do a full backup on the primary"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2014 at 4:28 am
MysteryJimbo (2/20/2014)
george sibbald (2/20/2014)
MysteryJimbo (2/20/2014)
george sibbald (2/20/2014)
MysteryJimbo (2/20/2014)
george sibbald (2/19/2014)
That in most cases will be a full backupYou don't even need a full backup to be restored. If you have a differential backup covering the missing LSN period this would suffice.
please read my post again, fully this time.
....Most cases I would expect a differential to be sufficient if your enterprise uses them..... .
thats a big if and not what you said. It read as if you had assumed I had not mentioned the differential option
I would question the need of logshipping if you don't require differential backups. If you can perform a full backup daily it may well be just as quick to restore it.
the recovery would never be as quick as restoring one last log backup or running recovery, and that won't give you the granularity of log restores, which is the whole point.
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply