sql sever获取数据库还原时间语句

--只获取数据库名称和最后的还原时间

SELECT sdb.Name AS DatabaseName ,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101), -) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

 

--获取完整备份、差异备份、日志备份最后时间的语句

方法一:

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS DATABASE Name,
b.backup_start_date AS Full DB Backup Status,
c.backup_start_date AS Differential DB Backup Status,
d.backup_start_date AS Transaction Log Backup Status
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS Full DB Backup Status
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = D) -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)Differential DB Backup Status
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = I)
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)Transaction Log Backup Status
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = L)
WHERE s.name <>tempdb
ORDER BY s.name

 

--方法二

SELECT
s.name
, [Full DB Backup Status] = D
, [Differential DB Backup Status] = I
, [Transaction Log Backup Status] = L
FROM sys.sysdatabases s
LEFT JOIN (
SELECT p.*
FROM (
SELECT [type], database_name, backup_start_date
FROM msdb.dbo.backupset
WHERE type IN (L,I,D)
) t
PIVOT (
MAX(backup_start_date)
FOR [type] IN ([L], [I], [D])
) p
) t ON s.name = t.database_name
ORDER BY s.name

 

sql sever获取数据库还原时间语句,布布扣,bubuko.com

sql sever获取数据库还原时间语句

上一篇:Oracle的nvl()和substr()方法


下一篇:mysql学习笔记 第五天