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

view all the restores made Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 10:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
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?
Post #1554590
Posted Tuesday, March 25, 2014 11:32 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
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?
Post #1554606
Posted Tuesday, March 25, 2014 11:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,975, Visits: 12,878
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


---------------------------------------------------------------------

Post #1554615
Posted Tuesday, March 25, 2014 2:36 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
It seems perfect. How can I add information to the script about the user that this this operation?
Post #1554687
Posted Wednesday, March 26, 2014 4:17 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,975, Visits: 12,878
add column [rs].[user_name] to the select list

---------------------------------------------------------------------

Post #1554851
Posted Wednesday, March 26, 2014 4:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:13 AM
Points: 557, Visits: 492
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
Post #1554865
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse