3

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)

2

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
  • 2
    Only part of this that really answers the OP's question is the last three lines, including the GO. Not sure why you brought up Ola's script. – user507 Sep 15 '15 at 12:55
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?

enter image description here

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

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.