How to identify my last restored file(BAK), on perticular DB.

Question

Post reply

How to identify my last restored file(BAK), on perticular DB.

koteswara kishore K.

Ten Centuries

Points: 1122
More actions
January 10, 2008 at 5:14 am

#127499

Hi,

I have lot of .bak files and i restored one file in my db. Now forgot which one i used. Please help how to find that file. In MSDB which table maintaine these information. Urgent.

Kishore

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

I don't think that information is stored anywhere in msdb or any other database.

The only place you might find it is the sql server logfile. Restore actions are logged together with a message like this one:

"Database restored: Database: myDB, creation date(time): 2007/12/04(18:51:28), first LSN: 66145:4109:3, last LSN: 66145:4127:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\MSSQL\BACKUP\MyDB.BAK'})."

[font="Verdana"]Markus Bohse[/font]

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

Markus is right the log is the only way. You can query the log for what you need by using the following code:



SET NOCOUNT ON

GO

Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,

        LogDate datetime null,

        ProcessInfo nvarchar(100) null,

        LogText nvarchar(4000) null) 

Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)

Exec master..xp_readerrorlog 

DECLARE @RESULT NVARCHAR(500), @DB NVARCHAR(25)

SET @DB = 'Test2'

SET @RESULT =

	(Select LogText

	From @ErrorLog

	Where ProcessInfo = 'Backup' AND

		  LogText like '%Database was restored: Database: ' + @DB + '%'

	) 

PRINT @RESULT

GO

SET NOCOUNT OFF

GO