5

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 )

9

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
  • 1
    You guys are really good. Hope I will be a DBA as good as you some day. Thank you very much. – Racer SQL Mar 17 '15 at 14:35
  • 2
    Note that you can parameterize the path, so dynamic SQL isn't necessary. – Aaron Bertrand Mar 17 '15 at 15:55
  • @AaronBertrand, i can do this with msdb.dbo.backupmediafamily and msdb.dbo.backupset, right? – Racer SQL Mar 17 '15 at 18:01
  • 1
    @Rafael Not sure I follow your question. This answer solves the problem, and yes you use those tables, I was only commenting on the code inside the cursor loop - it doesn't need to be dynamic, you can just say RESTORE VERIFY ONLY FROM DISK = @backupfile;. – Aaron Bertrand Mar 17 '15 at 18:18
  • @AaronBertrand yes, that answer solved my problem. I was just curious about your comment. Thanks. – Racer SQL Mar 17 '15 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.