1/31/2011

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

Reactions:

6 comments:

  1. The only way to prove a backup is restorable is to run RESTORE DATABASE. And then run DBCC CHECKDB. RESTORE VERIFYONLY is not enough.

    ReplyDelete
  2. Very true, but the reason to include restore verifyonly is to get to know if their is a problem in the backup file itself, with which restore could not proceed.

    I would recommend to have restore verifyonly mainly for these reasons (excerpts from BOL):

    1. That the backup set is complete and all volumes are readable.


    2. Some header fields of database pages, such as the page ID (as if it were about to write the data).


    3. Checksum (if present on the media).


    4. Checking for sufficient space on destination devices.

    Thank you
    -Hemantgiri S. Goswami

    ReplyDelete
  3. thanks for the information..i learned SQL after my tenth class.... but now i have forgotten all.. but still can understand this...really help full for my friends who are working in this field.

    ReplyDelete
  4. Database backup is most important if you don't want to loose your precious data. I would like to give you another option to recover corrupt data. You can try SQL Database Recovery Software if you don’t have any backup of database. The software recovers corrupt SQL Server database even when DBCC CHECKDB command get fail to repair database.

    ReplyDelete
  5. Backup is the most important in the internet life, in case you have no recent SQL backup then I would recommended you to try SQL recovery tool to recover corrupt SQL Server database. http://www.sqlrecoverysoftware.net/sql-server-mdf-file/

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri