What are the challenges you face when working across database platforms? Take the survey
Options

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.

Comments

  • Options
    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
Sign In or Register to comment.