Created Full Backup for User Defined Databases To Disk with Restore Verify Only option

Question

Post reply

Created Full Backup for User Defined Databases To Disk with Restore Verify Only option

paul.sanchez

Grasshopper

Points: 22
More actions
September 28, 2010 at 4:50 pm

#227095

First of all i want to say thank you to everyone here for looking and replying. I find this site to be very helpful.
What i am trying to accomplish is a Full Backup of user defined DB's as well as Verify the backup set. Below is the script I run to create the backups but, i am not having any luck with where to put the restore verify option (and maybe there's a better way to do this). Can you please review and recommend where to place the restore verify only option in this script?
Thanks.
BTW, this script was given to me so I am no sql expert by any means.
-- command for taking backup of userdefined databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20),
@query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
SELECT NAME FROM MASTER..Sysdatabases
WHERE NAME NOT IN ('master', 'model','msdb', 'tempdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''F:\backup\'+ @dbname+'.bak '' WITH INIT'
EXEC(@query)
FETCH NEXT FROM @cursor INTO @dbname
END
CLOSE @cursor
DEALLOCATE @cursor

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You can VERIFY it after the backup has completed. Below is an example.

FETCH NEXT FROM @cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''F:\backup\'+ @dbname+'.bak '' WITH INIT'

EXEC(@query)

SET @query = 'RESTORE VERIFYONLY FROM DISK = ''F:\backup\'+ @dbname+'.bak '' '

EXEC(@query)

FETCH NEXT FROM @cursor INTO @dbname

END

CLOSE @cursor

DEALLOCATE @cursor

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Just note that, unless the backup is taken with the CHECKSUM option, Verify does not guarantee that the backup will be restorable. It checks mostly the headers of the backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

paul.sanchez Grasshopper Points: 22 More actions · Answer 3

Thank you both for your input. I ended up adding in the CHECKSUM option to be safe. I tested this morning and it works wonderfully. This will help my guys in the field very much. Again appreciate both your input.