Backup

Alert – Databases do not have Transaction log backups

The SP below sends alert when there are no transaction log backups in the last 90 minutes.

CREATE PROCEDURE [dbo].[usp_NoTlogBackup] (@Length INT=90) -- 90 Minutes
AS
DECLARE @BadDatabases VARCHAR(8000)
DECLARE @Body VARCHAR(8000)

SELECT @BadDatabases = STUFF(
(select ', ' + cast(a.database_name as varchar(100))
 from 
 (
 select [name] as database_name
 from master.dbo.sysdatabases
 where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') 
 and databasepropertyex([name], 'isinstandby') = 0
 and databasepropertyex([name], 'status') = 'online'
 and databasepropertyex([name], 'updateability') = 'read_write'
 and [name] not in ('model','tempdb')
 ) a
 left join msdb.dbo.backupset b
 on a.database_name = b.database_name 
 and b.type='L' 
 and datediff(hour,b.backup_finish_date,getdate()) < @Length
 where b.database_name is null
FOR XML PATH ('')
),1,2,'')

IF (@BadDatabases IS NOT NULL)
BEGIN

declare @ServerT varchar(100)
select @ServerT = 'Alert Backup - '+@@SERVERNAME

SET @Body = 'On ' + @@SERVERNAME + ' The following Databases do not have Transaction log backups: ' + @BadDatabases
PRINT @Body

 EXEC msdb . dbo. sp_send_dbmail        
      @recipients=N'mail@mail.com;' ,
      @body = @Body ,         
      @subject = @ServerT,         
      @profile_name ='profile_name',         
      @body_format = 'HTML'

END
Advertisements

Estimated time of Backup / Restore [SQL Server]

Sometimes you will want to know how is the backup progress before a critical change of your environment or during the restore before setting a new high availability strategy.
This script can help you to figure out.

SELECT @@servername, command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

How to find databases without transaction log backups [SQL Server]

In my previous post Last backup of all databases, I had sent a little tip to find details about status of last backup Full.
Here is a query to show you the databases that are in Bulk-Logged and Full Recovery model where there are a Full Backup without any transaction log backups.

Possible problems
Without log backups, the transaction logs will not be check-pointed. So, the spaces will not be reused causing high growth on logfiles.


SELECT            
'DB without transaction log bkps' [.],
D.[name] as [database], D.[recovery_model_desc] as [recovery_model],
BS1.[last_log_backup_date] ,BS2. [last_data_backup_date]
FROM            
(sys. databases D left outer JOIN             
(            
   SELECT BS .[database_name],                
   MAX( BS.[backup_finish_date] ) AS [last_log_backup_date]             
   FROM msdb .dbo. backupset BS
   INNER JOIN sys .sysdatabases sys on sys . name = BS . [database_name]  
   WHERE BS .type = 'L'
   GROUP BY BS. [database_name]             
   ) BS1             
ON D. [name] = BS1 .[database_name]            
LEFT OUTER JOIN             
(            
   SELECT BS .[database_name],              
   MAX( BS.[backup_finish_date] ) AS [last_data_backup_date]             
   FROM msdb .dbo. backupset BS
   INNER JOIN sys .sysdatabases sys on sys . name = BS . [database_name]  
   WHERE BS .type = 'D'
   GROUP BY BS. [database_name]