Tips for Backing up User Databases

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
set @bkpath=’C:\SQLDB\backup\’
USE master
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’
  select @path=@bkpath+@dbname+CONVERT(VARCHAR(12),GETDATE(),112)
  BACKUP DATABASE @dbname TO DISK = @path
  print @path
  print @bkpath
  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!

Comments (2)

Very cool blog, I will bookmark your site and take the feeds also.I’m happy to find numerous useful info here in the post, we need word out more strategies in this regard, thanks for sharing.


Write a comment


© 2010 Increase your website traffic with