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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy