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)
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
-
2Only 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
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