T-SQL系列之所有数据库备份情况(MSSQL)

关注

原创最新推荐文章于 2026-03-28 09:31:28 发布 696 阅读 AI 写同款· GEO 优化 ›

MSSQL数据库备份 1、MSSQL数据库的定期自动备份计划通过企业管理器设置数据库的定期自动备份计划。1、打开企业管理器，双击打开你的服务器2、然后点上面菜单中的工具--> 阅读详情

--所有数据库备份情况


set nocount on
go
declare @counter smallint
declare @dbname varchar(100)
declare @db_bkpdate varchar(100)
declare @status varchar(20)
declare @svr_name varchar(100)
declare @media_set_id varchar(20)
declare @filepath varchar(1000)
declare @filestatus int
declare @fileavailable varchar(20)
declare @backupsize float


select @counter=max(dbid) from master..sysdatabases
create table #backup_details 
(
servername varchar(100),
databasename varchar(100),
bkpdate varchar(20) null,
backupsize_in_mb varchar(20),
status varchar(20),
filepath varchar(1000),
fileavailable varchar(200)
)
select @svr_name = cast(serverproperty('servername')as sysname)
while @counter > 0
begin
select @dbname = null , @db_bkpdate = null ,
@media_set_id = null , @backupsize = null ,
@filepath = null , @filestatus = null , 
@fileavailable = null , @status = null , @backupsize = null


select @dbname = name from master..sysdatabases where dbid = @counter
select @db_bkpdate = max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d'
select @media_set_id = media_set_id from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d')
select @backupsize = backup_size from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d')
select @filepath = physical_device_name from msdb..backupmediafamily where media_set_id = @media_set_id
exec master..xp_fileexist @filepath , @filestatus out
if @filestatus = 1
set @fileavailable = 'available'
else
set @fileavailable = 'not available'
if (datediff(day,@db_bkpdate,getdate()) > 7)
set @status = 'warning'
else
set @status = 'healthy'
set @backupsize = (@backupsize/1024)/1024
insert into #backup_details select @svr_name,@dbname,@db_bkpdate,@backupsize,@status,@filepath,@fileavailable
update #backup_details
set status = 'warning' where bkpdate is null
set @counter = @counter - 1
end
select substring(servername,0,20) AS [服务器名],
substring(databasename,0,20) AS [数据库名], 
rtrim(ltrim(bkpdate)) AS  [备份日期],
rtrim(ltrim(backupsize_in_mb)) AS [备份大小],
rtrim(ltrim([status])) AS [备份状态],
substring(rtrim(ltrim(filepath)),0,40) AS  [备份文件路径],
rtrim(fileavailable) AS  [备份文件是否可用]
 from #backup_details where databasename not in ('tempdb','northwind','pubs')
drop table #backup_details
set nocount off
go

收录于