July 12, 2012 at 10:15 am
savethytrees (7/12/2012)
Run the following queries to find which file was the last one to be copied and which file was the last one to be restored.The following query will give you the last log backup file that was copied
SELECT * FROM [msdb].[dbo].[log_shipping_secondary]
Then check what was the last log backup file that was restored.
SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]
the user doesnt want log shipping details.
jitendra.padhiyar (7/12/2012)
Yes, the given query for log shipping is very much useful. But I am asking in general that if I want to know that which backup set was last restored, than how can I know that ?
as i said in my last post check the MSDB for backup and restore info. The tables are detailed in books online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 12, 2012 at 11:58 am
Thanks Peryy and savethytrees !
August 10, 2012 at 4:39 am
DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply