Posted by Hemantgiri S. Goswami | Posted in Scripts, SQL DBA, Uncategorized | Posted on 31-01-2011
We all take a database backup as a first resort to recover from a damage may occur during the disaster, server crash, or for any other reason. To do so, we usually schedule a job or make use of database maintenance plan that perform database backup, be it Full / Differential or Log, and we all take a special care & consideration while designing the backup strategy!!
But what happened if one fine day when you need your database backup file to work well and it doesn’t?? What if it is corrupted!! And you have only a Full backup for that day?
Today I am going to discuss about the script that I have posted in the download section couple of days back; the script will not only perform a full database backup of all user databases but it will also “verify the validation of the backup file”. It’s a must for DB environment having backup strategy like – where in you do not keep multiple copies of backups or simply have one day backup file retention period.
declare @int int ,@dbname varchar(22),@maxdbid int
declare @bkpath varchar(25),@path varchar(50)
select @maxdbid= max(dbid) from master..sysdatabases
set @int = 0
while (@int < @maxdbid)
set @dbname = (select [name] from master..sysdatabases where dbid=@int and status!=1073807392)
if (@dbname != ‘tempdb’)
select @int as ‘DBID’,@dbname as ‘Database’
BACKUP DATABASE @dbname TO DISK = @path
restore verifyonly from disk=@path
set @int = @int + 1
Check the line # 17, restore verifyonly from disk = @path is the line that will help us to overcome above issue. This is not a hidden code lying in a secret place somewhere but this is something that we may have missed in our code – I know most of you have already using this option for your database backup validation, how many of you are using this option? Or out of curiosity any other option!!
PS: This code is the very basic code that I have wrote initially, you may modify and use the script according to your requirement without any obligation.
Your suggestions are welcome!