snippetsqlMinor
How to write "Verify backup when finished" in T-SQL?
Viewed 0 times
finishedhowsqlwriteverifywhenbackup
Problem
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)
Solution
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:
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.
Apart from this if you still like T-SQL that would be
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
GOCode Snippets
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup',
@BackupType = 'FULL',
@Verify = 'Y'RESTORE VERIFYONLY FROM DISK = B:\databasebackup.BAK
GOContext
StackExchange Database Administrators Q#115061, answer score: 3
Revisions (0)
No revisions yet.