SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


view all the restores made


view all the restores made

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4450 Visits: 1366
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?
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4450 Visits: 1366
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?
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24680 Visits: 13698
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

---------------------------------------------------------------------
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4450 Visits: 1366
It seems perfect. How can I add information to the script about the user that this this operation?
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24680 Visits: 13698
add column [rs].[user_name] to the select list

---------------------------------------------------------------------
Sushant Yadav
Sushant Yadav
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 871
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search