August 2, 2012 at 3:26 am
Hi
I have logshipping configured from production server to reporting server. We do some processing on reporting server and copy row data and generate some summary data on reporting server.
Production Server
Database : database1 : Log backup is configured on this database
Reporting Server
Database: database1_live - Logship is being restored on this database
database1 : This is reporting database
My issue is when I check the backup history (using below query) on Reportting server. It shows Log back history for database1 on reporting server, where as I don't have any job to take log backup of database1.
SELECT *
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME()
I only take fullback of database1 on reporting server. Can some tell does this cause any problem? Do I have to worry about this back history?
Regards,
Nitin
August 2, 2012 at 3:31 am
When you do a restore, it logs the backupset that was used in the restore into the backupset tables.
What you are seeing are the backupsets from the primary server Database1 not the ReportingServers Database1
Hopefully this will show you what I mean
SELECT
DatabaseRestoredTo = RH.destination_database_name,
TimeOfRestore = RH.restore_date,
UserImplimentingRestore = RH.user_name,
RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END,
ServerWhereBackupTaken = BS.server_name,
UserWhoBackedUpTheDatabase = BS.user_name,
BackupOfDatabase = BS.database_name,
DateOfBackup = BS.backup_start_date,
RestoredFromPath = BMF.physical_device_name
FROM
msdb.dbo.restorehistory RH
INNER JOIN
msdb.dbo.backupset BS
ON
RH.backup_set_id = BS.backup_set_id
INNER JOIN
msdb.dbo.backupmediafamily BMF
ON
BS.media_set_id = BMF.media_set_id
ORDER BY
RH.restore_history_id
August 2, 2012 at 3:53 am
Thanks Anthony, I got your point. I was just worried because I was not taking any log backup of Database1 on reporting server, still it was showing in backupset. I thought some ghost process is taking those log backups. But Now I am clear. I tried by skipping log backup on production server, and skipped one restore on reporting server, which verifies your point.
Thanks again for your prompt reply.
-Nitin
Regards,
Nitin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply