Veriify SQL Netbackup Processes With SQL

sql-verify-netbackup.sql

use master;
set nocount on
 
declare     @fqdn_server_name   varchar(255)
declare     @tomorrow       varchar(255)
declare     @30_days        varchar(255)
declare     @file_removal       table ([del_statement] varchar(555))
set     @fqdn_server_name   = (select cast(serverproperty('machinename') as varchar) + '.' + lower(default_domain()) + '.com')
set     @tomorrow       = (select convert(varchar, getdate() + 1, 101)  + ' 00:00:00')
set     @30_days        = (select convert(varchar, getdate() - 30, 101) + ' 00:00:00')
--create temp table to capture list of all confirmed netbackup file copies.
--创建临时表以捕获所有已确认的netbackup文件副本的列表。
if object_id('tempdb..##netbackup_confirmed') is not null
    drop table  ##netbackup_confirmed
create table    ##netbackup_confirmed ([file_name] varchar(255))
 
--find all backup path and backup files for the last 30 days.
--查找过去30天的所有备份路径和备份文件
declare     @duration   datetime
set     @duration   = (select getdate() - 30)
declare     @backup_history table ([location] varchar(255), [backup_file] varchar(255))
insert into @backup_history
select
    'location'          = reverse(right(reverse(upper(bmf.physical_device_name)), len(bmf.physical_device_name) - charindex('\',reverse(bmf.physical_device_name),1) + 1))
,   'backup_file'       = right(bmf.physical_device_name, charindex('\', reverse('\' + bmf.physical_device_name)) - 1)
from        msdb.dbo.backupset bs join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where       bs.backup_finish_date > @duration
        and bmf.[device_type] not in ('7')
group by    bs.database_name,   bs.backup_finish_date, bmf.physical_device_name, bs.type, bmf.device_type
order by    bs.database_name,   bs.backup_finish_date desc
 
--build Netbackup BPLIST logic for all known backup paths, and corresponding backup files.
--为所有已知备份路径和相应的备份文件构建Netbackup BPLIST逻辑。
declare @bplist_all     table ([bp_commands] varchar(max))
insert into @bplist_all
select distinct
'insert into ##netbackup_confirmed exec master..xp_cmdshell ''C:\NetBackupList\bplist -R 99 -C ' + @fqdn_server_name + ' -s ' + @30_days + ' -e ' + @tomorrow + ' -I "' + reverse(stuff(reverse([location]), 1, 1, ''))  + '"'''
from @backup_history
 
--execute Netbackup BPLIST utility across all unique backup paths gathering all confirmed files copied to enterprise storage and place them into table ##netbackup_confirmed.
--在所有唯一备份路径上执行Netbackup BPLIST实用程序，收集复制到企业存储的所有已确认文件，并将它们放入表## netbackup_confirmed中。
declare @populate_nbc       varchar(max)
set @populate_nbc       = ''
select  @populate_nbc       = @populate_nbc + 
[bp_commands]
from    @bplist_all
exec    (@populate_nbc)
 
-- get manual confirmation.  run query against @bplist_all and get the BPLIST command for any drive on the server.
-- run the following command with the target drive letter you're looking for.
--手动确认。 对@bplist_all运行查询并获取服务器上任何驱动器的BPLIST命令。
--使用你要查找的目标驱动器号运行以下命令。

select replace([bp_commands], 'insert into ##netbackup_confirmed ', '') 
from @bplist_all where [bp_commands] like '%y:\%'

 
--created file removal process for only files that have successfully backed up to the enterprise.
--为已成功备份到企业的文件创建文件删除程序。
insert into @file_removal
select 'exec master..xp_cmdshell ''DEL "' + [location] + [backup_file] + '"'';' from @backup_history where [location] + [backup_file] in (select [file_name] from ##netbackup_confirmed)
order by [backup_file] desc
 
--remove backups that thave already been moved to enterprise storage.
--删除已移至企业存储的备份。
declare @delete_files   varchar(max)
set @delete_files = ''
select  @delete_files = @delete_files + 
[del_statement] + char(10) 
from    @file_removal
exec    (@delete_files)