Backup Script with Verify Database 1

[dldev]

Hello, I have the following script which performs a nightly differential backup...

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)

SET @path = '\\test\SQLBACKUPS\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.bak'
BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

...but I can't find any information on how to script in a 'verify database', the option you have to verify the database integrity when manually setting up an individual backup job for a database. Is it possible to code for this?

Thanks,
Dennis

 

[mrdenny]

You have to use the RESTORE command to verify the backup. Put this code in after the BACKUP DATABASE command.

RESTORE VERIFYONLY FROM DISK=@filename

Keep in mind that this only verifies that the backupfile is valid, and that the format of the file is correct. It does not verify that the data is correct.

Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical. For more information, see the Remarks.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com