Dynamic script to verifying the backup's

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Dynamic script to verifying the backup's

Dynamic script to verifying the backup's

Rate Topic

Display Mode

Topic Options

Author

Message

saran.dharsh

Posted Tuesday, January 22, 2013 10:38 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:10 PM
Points: 27, Visits: 360

Am using below script to verify the backup's dynamically... but am not getting expected output

DECLARE @Count INT
DECLARE @query nvarchar(2000)
DECLARE @current INT
DECLARE @name VARCHAR(2000)

DECLARE @tempBackupCheck TABLE
(
intID INT IDENTITY(1,1) PRIMARY KEY,
Server nvarchar(50),
Database_Name nvarchar(50),
backup_start_date datetime,
backup_finish_date datetime,
backup_size varchar(20),
physical_device_Name varchar(1000),
backup_type varchar (50),
error_msg VARCHAR(2000),
[status] bit
)

----------------------------------------------------------------------------
-- 1. Select Backup
----------------------------------------------------------------------------

INSERT INTO @tempBackupCheck(Server,database_name,backup_start_date,backup_finish_date,backup_size,physical_device_name,backup_type)
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
database_name,backup_start_date,backup_finish_date,
convert(varchar,cast(backup_size/1024/1024 as money),10)as 'Backup Size in GB',
physical_device_name,
CASE [Type]
WHEN 'D' THEN 'Full'
--WHEN 'F' THEN 'File'
--WHEN 'G' THEN 'Filegroup'
--WHEN 'I' THEN 'Differential'
--WHEN 'L' THEN 'Transaction Log'
--WHEN 'V' THEN 'Verifyonly'
END AS rhType
FROM msdb.dbo.backupset b
inner JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name NOT IN ('master','model','tempdb','msdb','distribution','Northwind')
and b.type = 'D' AND backup_finish_date >convert(varchar(10),getdate(),120)
ORDER BY backup_finish_date DESC

----------------------------------------------------------------------------
-- 2. Verify
----------------------------------------------------------------------------

SELECT @Count = COUNT(intID) FROM @tempBackupCheck
IF ((@Count IS NOT NULL) AND (@Count > 0))
BEGIN
set @current=1
WHILE (@current <= @Count)
BEGIN
SELECT
@name = physical_device_name
FROM @tempBackupCheck
WHERE intID = @current
set @query='RESTORE VERIFYONLY FROM DISK= '''+ @name +''' WITH checksum'
BEGIN TRY
print @query;
exec sp_executesql @query

-- Update Staus

update @tempBackupCheck
set [status]=1
WHERE intID = @current
END TRY
BEGIN CATCH

-- Update Error Message and Staus

update @tempBackupCheck
set error_msg=ERROR_MESSAGE(),
[status]=1
WHERE intID = @current
END CATCH
set @current=@current+1
END
END

----------------------------------------------------------------------------
-- Show Output
----------------------------------------------------------------------------
SELECT * FROM @tempBackupCheck

Output:
intID Server Database_Name backup_start_date backup_finish_date backup_size physical_device_Name backup_type error_msg status
1 Hostname DBName 53:29.0 53:29.0 2.64 Backupname Full VERIFY DATABASE is terminating abnormally. 1

Case 1:
for Error_msg column am exepcting to get valid or corrupted backup..
Case 2:
In case if backup fails, i have capture those information also in my script..

How to achieve expected output?

Post #1410343

« Prev Topic | Next Topic »

Permissions