SQL Server – How to List the Last Backup File of a Database

backupsql-server-2008

I'm trying to do something really simple, but with one problem:

declare @cmd varchar(1000)

set @cmd='restore verifyonly from disk =''\\server\backup_folder\''?'''

exec sp_msforeachdb @cmd

Obviously its not working. I need the name of the backup file to run Verifyonly ( in the above code, i'm taking the Database name ).

Is there a way to automate Verifyonly, to run in all backup files?

How can I use the name of the backup file that has been made, in my code?

Thank you very much.

( People are saying MsForeachdb is not safe. I will try to search another option, like Foreachdb )

SQL SERVER 2008

EDIT1: Well, As i see here, I don't think verifyonly is a good idea, but will be good to know how to do it ( only for knowledge )

Best Answer

This is just a matter of using dynamic SQL to process your data. You can also leverage msdb.dbo.backupset and msdb.dbo.backupfamily to find your most recent full backups. This script will get the most recent full backup file location for each database and use it to run your cursor:

declare @dbname sysname
    ,@backupfile nvarchar(max)
    ,@sqlcmd nvarchar(max)

declare recscan cursor fast_forward 
for
select
    bs.database_name
    ,bm.physical_device_name
from
    msdb.dbo.backupset bs
    join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
    join (select database_name
            ,max(backup_finish_date) last_backup
          from msdb.dbo.backupset
          where type = 'D'
          group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'

open recscan
fetch next from recscan into @dbname,@backupfile

while @@fetch_status != -1
begin
    PRINT 'Validating '+@dbname
    select @sqlcmd = N'restore verifyonly from disk = '''+@backupfile+''';'

    exec sp_executesql @sqlcmd

    fetch next from recscan into @dbname,@backupfile
end

close recscan
deallocate recscan
Related Question