January 10, 2008 at 5:14 am
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
January 10, 2008 at 7:03 am
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]
January 10, 2008 at 12:14 pm
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
January 11, 2008 at 12:24 am
You can look in msdb..restorehistory, which links to msdb..backupset, which in turn links to msdb..backupmediafamily.
Ray Mond
http://www.sqlinspect.com
In-depth query analysis for SQL Server
January 11, 2008 at 7:11 am
Good tip Ray Mond. 😉 I did not know this information was available in MSDB.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply