Tip for Backing up User Databases

We all take a database backup as a first resort to recover from a damage occur during the disaster / server crash / for any other reason if require to recover or restore our data back to its previous stat as it was before the database server was crashed!! And for this we schedule a job or make use of database maintenance plan which will take a database backup be it Full / Differential or Log, and we all take a special care and consideration when we design 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 to have a database backup strategy where in we do not keep multiple copies of database backup  and if one have one or two days retention policy. 

declare @int int ,@dbname varchar(22),@maxdbid int
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
set @bkpath='C:\SQLDB\backup\'
USE master
while (@int < @maxdbid)
begin
set @dbname = (select [name] from master..sysdatabases where dbid=@int and status!=1073807392)  
if (@dbname != 'tempdb') 
 begin
  select @int as 'DBID',@dbname as 'Database'  
  select @path=@bkpath+@dbname+CONVERT(VARCHAR(12),GETDATE(),112)
  BACKUP DATABASE @dbname TO DISK = @path  
  print @path
  print @bkpath
  restore verifyonly from disk=@path
  end  
 set @int = @int + 1  
 continue 
 break
end

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 welcome!

Regards

- Hemantgiri S. Goswami