last restore data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

last restore data

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-11-02 : 14:03:11

Hi,

I am trying to use the "sp_msforeachdb" to display each database and the last restore date.
It errors out. I am not able to spot the problem in the command.

Can someone help, please?
Thank you

- SHM


EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME() 
 SELECT TOP 1 restore_date FROM MSDB.dbo.RESTOREHISTORY WITH (nolock) 
 WHERE (DESTINATION_DATABASE_NAME = ?) 
 ORDER BY RESTORE_DATE DESC'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 14:14:01

If you are not specifically required to use sp_msforeachdb, you can simply query like this.

SELECT destination_database_name,MAX(restore_date)
FROM MSDB.dbo.RESTOREHISTORY 
GROUP BY destination_database_name

If you are trying to learn using sp_msforeachdb, then do it like this:

EXECUTE sp_msforeachdb '
 SELECT TOP 1 ''?'' as dbname,restore_date FROM MSDB.dbo.RESTOREHISTORY WITH (nolock) 
 WHERE (DESTINATION_DATABASE_NAME = ''?'') 
 ORDER BY RESTORE_DATE DESC'

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-11-02 : 14:23:14

Thanks a lot Sunita

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-11-02 : 15:22:26

All types of Restores are included in the RestoreHistory table.If you need the restore_type such as :
CASE restore_type]
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'V' THEN 'Verifyonly'
include it in the SELECT statement

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources