How to write “Verify backup when finished” in T-SQL?

Question

In SQL Server one can cross an option under Reliability which says "Verify backup when finished", when one makes a backup through the management studio. How can I write it in T-SQL? (I want to write a job which do the backup so I need to put it all to code)

Answer 1

You can use ola.hallengren Backup scripts and schedule the job once you have created the required scripts from Ola's link:

In job you can use:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup',
@BackupType = 'FULL',
@Verify = 'Y'

Here as you can see Verify='Y' will do the job in addition to backing up the user databases.

The best approach would be above as you can log the history in table as well for the verification that is being done by using extra parameter in above code:

i.e. @LogToTable='Y' which Log commands to the table dbo.CommandLog.

Apart from this if you still like T-SQL that would be

RESTORE VERIFYONLY FROM DISK = B:\databasebackup.BAK
GO

Answer 2

In SQL Server one can cross an option under Reliability which says "Verify backup when finished", when one makes a backup through the management studio. How can I write it in T-SQL?

Below is the script generated :

backup database [AdventureWorksDW2012] to disk = N'D:\SQLServer2012\MSSQL11.SQL2012\MSSQL\Backup\AdventureWorksDW2012.bak'
with NOFORMAT
    ,INIT
    ,name = N'AdventureWorksDW2012-Full Database Backup'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
go

declare @backupSetId as int

select @backupSetId = position
from msdb..backupset
where database_name = N'AdventureWorksDW2012'
    and backup_set_id = (
        select max(backup_set_id)
        from msdb..backupset
        where database_name = N'AdventureWorksDW2012'
        )

if @backupSetId is null
begin
    raiserror (
            N'Verify failed. Backup information for database ''AdventureWorksDW2012'' not found.'
            ,16
            ,1
            )
end

restore VERIFYONLY
from disk = N'D:\SQLServer2012\MSSQL11.SQL2012\MSSQL\Backup\AdventureWorksDW2012.bak'
with file = @backupSetId
    ,NOUNLOAD
    ,NOREWIND
go