Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log shipping and restore issue Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 3:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 7:59 AM
Points: 879, Visits: 284
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

Post #1339029
Posted Thursday, August 2, 2012 3:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1339030
Posted Thursday, August 2, 2012 3:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 7:59 AM
Points: 879, Visits: 284
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

Post #1339038
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse