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