March 28, 2019 at 11:40 am
I have 300 Servers,
How can I Check all the Full recovery DB, if Log Backup is done?
In other words I want to know whatever DB is in Full Recovery mode, if Log backup is done or Not?
--Nita
March 28, 2019 at 12:05 pm
there are many ways to do that, here are some of them
1) you can use batch/cmd file to run query to find out
2) you can use powershell for the same purpose
3) you can use linked server and query
4) you can register servers in SSMS and use Policy Based Management.
5) you can use 3rd party software.
6) you can register servers in SSMS and run query against multipal instances to find out
March 28, 2019 at 12:20 pm
goher2000 - Thursday, March 28, 2019 12:05 PMthere are many ways to do that, here are some of them1) you can use batch/cmd file to run query to find out
2) you can use powershell for the same purpose
3) you can use linked server and query
4) you can register servers in SSMS and use Policy Based Management.
5) you can use 3rd party software.
6) you can register servers in SSMS and run query against multipal instances to find out
Yes I can register servers in SSMS and run query against multipal instances to find out but I want to know the query which can give me that result. The DB which is full recovery and had never Log backup
March 28, 2019 at 12:33 pm
Nita Reddy - Thursday, March 28, 2019 12:20 PMYes I can register servers in SSMS and run query against multipal instances to find out but I want to know the query which can give me that result. The DB which is full recovery and had never Log backup
Something like: SELECT [name], recovery_model_desc
FROM sys.databases d
WHERE recovery_model = 1
AND NOT EXISTS(SELECT *
FROM dbo.backupset
WHERE [type] = 'L'
AND [database_name] = d.[name])
Sue
March 28, 2019 at 12:37 pm
declare
@db sysname ,
@srv varchar(15),
@inst varchar(15),
@msd datetime,
@MFD datetime,
@bl varchar(260),
@ty char(1),
@rm varchar(255)
SET @ty = 'L'
declare c1 cursor for
select
convert(varchar(15),SERVERPROPERTY('Machinename')),
isnull(convert(varchar(15),SERVERPROPERTY('Instancename')),'Default'),name,recovery_model_desc from master.sys.databases
OPEN c1
FETCH NEXT FROM c1
into @srv,@inst, @db,@rm
WHILE @@FETCH_STATUS = 0
BEGIN
--Print @srv + ',' + @inst + ',' + @db
set @MFD = (select max(msdb.dbo.backupset.backup_finish_date) from msdb.dbo.backupset where database_name = @db and type = @ty)
set @msd = (select max(msdb.dbo.backupset.backup_start_date) from msdb.dbo.backupset where database_name = @db and type = @ty)
set @bl = (Select physical_device_name from msdb.dbo.backupmediafamily , msdb.dbo.backupset
where msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediafamily.media_set_id
and msdb.dbo.backupset.backup_finish_date = @MFD
and database_name=@db and type = @ty)
if @MFD is not null
Print @srv + ',' + @rm + ',' + @inst + ',' +@ty + ',' + @db + ',' + isnull(convert( varchar,@msd,120 ),'Backup Never happend') + ', '+
isnull(convert( varchar,@mfd,120 ),'No findate') + ', '+ @bl
else
Print @srv + ',' + @rm + ',' + @inst + ',' +@ty + ','+ @db + ',' + isnull(convert( varchar,@msd,120 ),'No Start date found, ') +
isnull(convert( varchar,@mfd,120 ),'No finish date found, ----')
FETCH NEXT FROM c1
into @srv,@inst, @db,@rm
end
close c1
deallocate c1
GO
March 28, 2019 at 12:38 pm
select name,recovery_model_desc from master.sys.databases
March 28, 2019 at 12:42 pm
-- details of last backup (for all databases) performed (full ,diff & log)
USE [Master];
GO
DECLARE @FileSpace TABLE (
[database_id] INT,
[db_name] NVARCHAR(100),
[file_id] INT,
[file_name] NVARCHAR(100),
[space_used] INT
);
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' AS DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') AS spaceused FROM sys.sysfiles';
SELECT
S.name AS [DatabaseName],
S.recovery_model_desc AS [RecoveryModel],
S.create_date AS [DatabaseCreatedDate],
S.collation_name AS [DatabaseCollation],
F.[Last_Full_Backup_Date] AS [LastFullBackupDate],
F.backup_size AS [FullBackupSize],
L.Last_Log_Backup_Date AS [LastLogBackupDate],
L.backup_size AS [LogBackupSize],
I.[Last_Diff_Backup_Date] AS [LastDiffBackupDate],
I.backup_size AS [DiffBackupSize],
S.state_desc [DatabaseState],
CURRENT_TIMESTAMP AS [ReportDate]
FROM
SYS.DATABASES S
OUTER APPLY
(SELECT TOP 1 database_name, backup_finish_date AS [Last_Full_Backup_Date] , backup_size FROM msdb.dbo.backupset
WHERE TYPE='D' and database_name =S.name ORDER BY backup_finish_date DESC ) F
OUTER APPLY
(SELECT TOP 1 database_name, backup_finish_date AS [Last_Log_Backup_Date] , backup_size FROM msdb.dbo.backupset
WHERE TYPE='L' and database_name =S.name ORDER BY backup_finish_date DESC ) L
OUTER APPLY
(SELECT TOP 1 database_name, backup_finish_date AS [Last_Diff_Backup_Date] , backup_size FROM msdb.dbo.backupset
WHERE TYPE='I' and database_name =S.name ORDER BY backup_finish_date DESC ) I
ORDER BY
S.name;
March 28, 2019 at 1:01 pm
goher2000 - Thursday, March 28, 2019 12:42 PM-- details of last backup (for all databases) performed (full ,diff & log)
USE [Master];
GODECLARE @FileSpace TABLE (
[database_id] INT,
[db_name] NVARCHAR(100),
[file_id] INT,
[file_name] NVARCHAR(100),
[space_used] INT
);
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' AS DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') AS spaceused FROM sys.sysfiles';SELECT
S.name AS [DatabaseName],
S.recovery_model_desc AS [RecoveryModel],
S.create_date AS [DatabaseCreatedDate],
S.collation_name AS [DatabaseCollation],
F.[Last_Full_Backup_Date] AS [LastFullBackupDate],
F.backup_size AS [FullBackupSize],
L.Last_Log_Backup_Date AS [LastLogBackupDate],
L.backup_size AS [LogBackupSize],
I.[Last_Diff_Backup_Date] AS [LastDiffBackupDate],
I.backup_size AS [DiffBackupSize],
S.state_desc [DatabaseState],
CURRENT_TIMESTAMP AS [ReportDate]
FROM
SYS.DATABASES S
OUTER APPLY
(SELECT TOP 1 database_name, backup_finish_date AS [Last_Full_Backup_Date] , backup_size FROM msdb.dbo.backupset
WHERE TYPE='D' and database_name =S.name ORDER BY backup_finish_date DESC ) FOUTER APPLY
(SELECT TOP 1 database_name, backup_finish_date AS [Last_Log_Backup_Date] , backup_size FROM msdb.dbo.backupset
WHERE TYPE='L' and database_name =S.name ORDER BY backup_finish_date DESC ) L
OUTER APPLY
(SELECT TOP 1 database_name, backup_finish_date AS [Last_Diff_Backup_Date] , backup_size FROM msdb.dbo.backupset
WHERE TYPE='I' and database_name =S.name ORDER BY backup_finish_date DESC ) I
ORDER BY
S.name;
Awesome....thanks
April 2, 2019 at 11:26 am
This information is retained in the MSDB. I would do one of two things-
1. Create DB links from the official report server you wish to report from to the MSDBs on each of the servers to collect the info. The queries would be something similar to this: http://blogs.microsoft.co.il/yaniv_etrogi/2017/01/03/query-msdb-backupset-to-get-backup-information/
2. Take this same info with links and write it to a table in a database that would retain the data over time and then you could query it without having to go over the network on a regular basis.
Does that make sense?
The nice thing about having a repository storing this, is that it will be available longer than the reports and you can just have it report if there is an issue or if something errors out.
Look for the problems instead of parse through data, which is more time consuming and not a good use of your time.
Just my opinion..
SQL Database Recovery Expert 🙂
April 4, 2019 at 12:56 am
you can use register servers and run one of the proposed queries or you can create list of servers and run PS as below:
$Servername=("SQLC-EST-IN-N5\INTERNAL","SQL1", "SQL2","SQL..n")
foreach ($Server in $Servername)
{
$output += (invoke-sqlcmd -ServerInstance "$Server" -query 'your_query' -querytimeout 65534)
}
$output | Export-Csv \\path\DBs state.Csv
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply