Using sqbdata to get the log filename

NeMNeM Posts: 25
edited February 17, 2011 9:38PM in SQL Backup Previous Versions
The following is just pseudo-code.

DECLARE @dbname NVARCHAR(MAX)
DECLARE @logname NVARCHAR(MAX)
SET @dbname='MyDB'
SET @logname=EXEC master..sqbdata 'select logfilename from restorehistory where restore_end >= CONVERT(NVARCHAR(20),GETDATE(),101) and dbname=@dbname'

My intention is to pass the database name as an input parameter to the sqbdata query and get back the logfilename from the restorehistory table and store it in a variable, @logname. This would then make it possible for me set up a sql job that will email me the log (I do not wish to use the MAILTO keyword in the RESTORE command as that email cannot be customized prior to delivery). Please let me know how this can be accomplished. Thanks.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Try this:
    DECLARE @logname NVARCHAR(MAX) 
DECLARE @dbname NVARCHAR(MAX) 
SET @dbname='MyDB' 

DECLARE @cmd NVARCHAR(MAX)
SET @cmd = 'SELECT logfilename FROM restorehistory WHERE restore_end >= CONVERT(NVARCHAR(20),GETDATE(),101) AND dbname= ''' + @dbname + ''' ORDER BY restore_end DESC'

CREATE TABLE #templog (logname NVARCHAR(256))
INSERT INTO #templog EXEC master..sqbdata @cmd 
SELECT TOP 1 @logname = logname FROM #templog 
DROP TABLE #templog
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.