October 11, 2015 at 7:54 pm
Can the log ship backups be used for general restoration?
I refresh Test from Live by restoring the full backup then restoring log backups, but can only roll forward a few backups until I hit a discontinuity in the LSNs.
restore headeronly
from disk = 'D:\ManualBackups\TWS_RE7_Live_20151011120000.trn'
restore headeronly
from disk = 'D:\ManualBackups\TWS_RE7_Live_20151011114500.trn'
restore headeronly
from disk = 'D:\ManualBackups\TWS_RE7_Live_20151011113000.trn'
produces
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSize
NULLNULL2NULL112NATOFFICE\SqlServiceRELIVETWS_RE7_Live6612015-07-29 08:14:59.000349184175376290000048661000011753762900000486610000117537629000004861500037175376290000048615000372015-10-11 23:00:00.0002015-10-11 23:00:00.000001033196611100460810504000RELIVE512F2A7A0C7-7B3E-4E7F-9B4E-9F6AC1843004E2CB1725-9861-417B-8527-C058A4446EF6Latin1_General_CI_AI8396CE0A-4278-48B4-B692-8E2172FE43070000000000E2CB1725-9861-417B-8527-C058A4446EF6NULLFULLNULLNULLTransaction LogC893BF31-8206-45F1-A835-488D8F5EC2C419003
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSize
NULLNULL2NULL112NATOFFICE\SqlServiceRELIVETWS_RE7_Live6612015-07-29 08:14:59.000415744175376290000048612000011753762900000486580000117537629000004861500037175376290000048615000372015-10-11 22:45:00.0002015-10-11 22:45:00.000001033196611100460810504000RELIVE512F2A7A0C7-7B3E-4E7F-9B4E-9F6AC1843004E2CB1725-9861-417B-8527-C058A4446EF6Latin1_General_CI_AI8396CE0A-4278-48B4-B692-8E2172FE43070000000000E2CB1725-9861-417B-8527-C058A4446EF6NULLFULLNULLNULLTransaction Log9942886B-74E9-40DE-BDED-BFF06EC6F62B17235
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSize
NULLNULL2NULL112NATOFFICE\SqlServiceRELIVETWS_RE7_Live6612015-07-29 08:14:59.000349184175376290000048612000011753762900000486120000117537629000004849800078175376290000048498000782015-10-11 22:30:00.0002015-10-11 22:30:00.000001033196611100460810504000RELIVE512F2A7A0C7-7B3E-4E7F-9B4E-9F6AC1843004E2CB1725-9861-417B-8527-C058A4446EF6Latin1_General_CI_AI8396CE0A-4278-48B4-B692-8E2172FE43070000000000E2CB1725-9861-417B-8527-C058A4446EF6NULLFULLNULLNULLTransaction LogD7A7B24E-994C-4FC9-B053-3E985487916219009
and there is a discontinuity between the last tow log backups - transactions 58, 59, 60 are missing.
Is what I want to do feasible?
Thanks.
Paul Smith
October 11, 2015 at 8:10 pm
Yes - you should be able to restore full backup and then the log backups. If you are finding that you have missing LSNs then this means that you have missed at least one of the transaction log backups
Underneath the wrappers - log shipping is simply an automated way to continually backup and restore transaction logs.
October 11, 2015 at 8:39 pm
The backups are occurring every 15 minutes but note that the last two listed have a gap in the LSNs even though they are consecutive backups.
October 12, 2015 at 3:06 am
vmit02 (10/11/2015)
The backups are occurring every 15 minutes but note that the last two listed have a gap in the LSNs even though they are consecutive backups.
Someone / something else took a log backup in the interval?
SELECT TOP 1000
--BS.database_name,
BS.backup_set_id,
BS.backup_start_date,
BS.backup_finish_date,
BS.type,
BS.backup_size,
BS.name,
BS.user_name,
BS.first_lsn,
BS.last_lsn,
BS.database_backup_lsn,
BS.description,
BMF.family_sequence_number,
BMF.device_type,
BMF.physical_device_name
FROMmsdb.dbo.backupset AS BS
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
ON BMF.media_set_id = BS.media_set_id
WHERE1=1
AND BS.database_name = DB_Name()
--AND BS.database_name = N'MyDatabaseName'-- SELECT DB_NAME()
-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
AND BS.type='L'
AND BS.backup_start_date > '20151011 11:30'
AND BS.backup_start_date <= '20151011 12:30'
--
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_number
October 12, 2015 at 3:26 am
vmit02 (10/11/2015)
The backups are occurring every 15 minutes but note that the last two listed have a gap in the LSNs even though they are consecutive backups.
Either you've misplaced a log backup file, or some other process took a log backup and stored it somewhere else. If the latter, the log shipping will probably have broken due to the gap in the log chain.
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
October 12, 2015 at 7:35 pm
Results of the posted SQL are:
backup_set_idbackup_start_datebackup_finish_datetypebackup_sizenameuser_namefirst_lsnlast_lsndatabase_backup_lsndescriptionfamily_sequence_numberdevice_typephysical_device_name
59862015-10-11 12:30:00.0002015-10-11 12:30:00.000L98304NULLNATOFFICE\SqlService587700000030840000015877000000308400000158770000002957000037NULL12D:\RELIVE_LogShipping\TWS_RE7_Inhouse_Tables_Live_20151011013000.trn
59842015-10-11 12:15:00.0002015-10-11 12:15:00.000L98304NULLNATOFFICE\SqlService587700000030840000015877000000308400000158770000002957000037NULL12D:\RELIVE_LogShipping\TWS_RE7_Inhouse_Tables_Live_20151011011500.trn
59822015-10-11 12:00:00.0002015-10-11 12:00:00.000L98304NULLNATOFFICE\SqlService587700000030840000015877000000308400000158770000002957000037NULL12D:\RELIVE_LogShipping\TWS_RE7_Inhouse_Tables_Live_20151011010000.trn
59802015-10-11 11:45:00.0002015-10-11 11:45:00.000L98304NULLNATOFFICE\SqlService587700000030840000015877000000308400000158770000002957000037NULL12D:\RELIVE_LogShipping\TWS_RE7_Inhouse_Tables_Live_20151011004500.trn
October 12, 2015 at 7:37 pm
and:
59872015-10-11 12:30:00.0002015-10-11 12:30:00.000L349184NULLNATOFFICE\SqlService175368500000019499000011753685000000194990000117536850000001945100037NULL12D:\RELIVE_LogShipping\TWS_RE7_Live_20151011013000.trn
59852015-10-11 12:15:00.0002015-10-11 12:15:00.000L349184NULLNATOFFICE\SqlService175368500000019499000011753685000000194990000117536850000001945100037NULL12D:\RELIVE_LogShipping\TWS_RE7_Live_20151011011500.trn
59832015-10-11 12:00:00.0002015-10-11 12:00:00.000L349184NULLNATOFFICE\SqlService175368500000019499000011753685000000194990000117536850000001945100037NULL12D:\RELIVE_LogShipping\TWS_RE7_Live_20151011010000.trn
59812015-10-11 11:45:00.0002015-10-11 11:45:00.000L349184NULLNATOFFICE\SqlService175368500000019499000011753685000000194990000117536850000001945100037NULL12D:\RELIVE_LogShipping\TWS_RE7_Live_20151011004500.trn
since this is happening on 2 databases; both fail to restore at the same transaction log restore point in time.
October 13, 2015 at 2:11 am
Is it relevant that the RESTORE HEADER you did was:
restore headeronly
from disk = 'D:\ManualBackups\TWS_RE7_Live_20151011[highlight="#ffff11"]120000[/highlight].trn'
restore headeronly
from disk = 'D:\ManualBackups\TWS_RE7_Live_20151011[highlight="#ffff11"]114500[/highlight].trn'
restore headeronly
from disk = 'D:\ManualBackups\TWS_RE7_Live_20151011[highlight="#ffff11"]113000[/highlight].trn'
but the query shows:
backup_set_id backup_start_date physical_device_name
5986 2015-10-11 [highlight="#ffff11"]12:30[/highlight]:00.000 TWS_RE7_Inhouse_Tables_Live_20151011[highlight="#ffff11"]013000[/highlight].trn
5984 2015-10-11 [highlight="#ffff11"]12:15[/highlight]:00.000 TWS_RE7_Inhouse_Tables_Live_20151011[highlight="#ffff11"]011500[/highlight].trn
5982 2015-10-11 [highlight="#ffff11"]12:00[/highlight]:00.000 TWS_RE7_Inhouse_Tables_Live_20151011[highlight="#ffff11"]010000[/highlight].trn
5980 2015-10-11 [highlight="#ffff11"]11:45[/highlight]:00.000 TWS_RE7_Inhouse_Tables_Live_20151011[highlight="#ffff11"]004500[/highlight].trn
Is filename being constructed from a 24-hour clock and some files overwritten (or an additional backup appended to perhaps?)
To see any filenames that are reused you could add the following to my earlier query (probably need to extend the date/time range of the query to be sure to include any other usage of the same filename)
JOIN
(
SELECTmedia_set_id
FROMmsdb.dbo.backupset AS BS
GROUP BY media_set_id
HAVING COUNT(*) > 1
) AS DUP
ON DUP.media_set_id = BS.media_set_id
October 13, 2015 at 4:04 am
vmit02 (10/11/2015)
Can the log ship backups be used for general restoration?
Yes, they're regular log backups so don't go deleting them.
vmit02 (10/11/2015)
I refresh Test from Live by restoring the full backup then restoring log backups, but can only roll forward a few backups until I hit a discontinuity in the LSNs.
As others have advised, you have either backups occurring outside of the LS plan or a file was deleted maybe.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 13, 2015 at 4:47 pm
Adding the JOIN produced 0 results. That means only 1 backup is in each file? which is what it should be.
The filenames are being constructed by the log shipping facility.
They appear to be using GMT in the naming, 11 hours behind our time.
I searched for any other .trn files on the machine and found no others. So if another backup has been made (outside the logship schedule), how do I find it?
Thanks.
October 14, 2015 at 1:09 am
vmit02 (10/13/2015)
Adding the JOIN produced 0 results. That means only 1 backup is in each file? which is what it should be.
That's good to hear! (I was worried that any filename reuse would INITIALISE the file rather than APPEND so you'd only have the latest backup in the file ... glad that's not the case 🙂 )
They appear to be using GMT in the naming
Obvious, now that you say it. However in adding that JOIN to the script I found that we have an intermittent re-use of backup filenames here which I've been able to cure (I set the backup to APPEND instead of INITIALISE just for belt-and-braces)
if another backup has been made (outside the logship schedule), how do I find it?
The SQL error log stores a comment about each backup. I don't know if it is possible to take a backup and for that NOT to be logged in MSDB history table. Perhaps some tape backup software APPs are able to do it that way? If they do maybe that wouldn't be in SQL Error Log either ...
Is there a pattern as to when this happens? Some time / day-of-week that might point the finger at a scheduled task / activity?
October 14, 2015 at 4:20 pm
It is happening on the two main databases at the same time, the 11pm transaction log backup (named *120000.trn in the GMT naming scheme).
I cannot find another job (apart from the log ship backup) in the Agent that affects logs.
The dbs are full backed up each evening, not at 11pm, they are well finished by then.
Frustration.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply