March 25, 2014 at 10:44 am
Hi,
I need to see all the restores that were made to a database called SGCTI.
The SQL server instance is far way from me.
Can I ask for a backup of the msdb and restore that database on my SQL Server instance (under other name) and than make a select to return all the restores that were made?
Thank you.
P.S - Do I need to ask for any additional database backup, like the master?
March 25, 2014 at 11:32 am
also, what object inside the msdb can I query to have all the restores that were made to SGCTI?
Can I do a select where name ='SGCTI' to one of the tables inside the MSDB database that I will restore?
March 25, 2014 at 11:46 am
if you have no access to this server a restore of the msdb on your server as a user database would give you the information you need, no other information is needed.
You could ask them to run this query and return the results to you
SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
where [rs].[destination_database_name] = 'SGCTI'
ORDER BY [rs].[restore_date] DESC
If you restored the msdb you would have to amend the database name you queried in the code above
---------------------------------------------------------------------
March 25, 2014 at 2:36 pm
It seems perfect. How can I add information to the script about the user that this this operation?
March 26, 2014 at 4:17 am
add column [rs].[user_name] to the select list
---------------------------------------------------------------------
March 26, 2014 at 4:34 am
SELECT destination_database_name AS 'Database Name',
restore_date AS 'Date Restored',
CASE restore_type
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Diffrential backup'
WHEN 'L' THEN 'Log Backup'
END AS 'Restored From',
CASE recovery
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS 'With Recovery',
backup_finish_date AS 'Backup taken',
Cast(backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,
physical_device_name AS 'Backup Location'
FROM msdb..restorehistory,
msdb..backupset,
msdb..backupmediafamily
WHERE msdb..restorehistory.backup_set_id= msdb..backupset.backup_set_id
AND msdb..backupset.media_set_id=msdb..backupmediafamily.media_set_id
AND database_name='SGCTI'
ORDER BY destination_database_name,
restore_date DESC
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply