SQL Server Restore History
USE msdb ;
SELECT
DISTINCT
DBRestored = destination_database_name ,
RestoreDate = restore_date ,
SourceDB = b.database_name ,
BackupDate = backup_start_date
FROM RestoreHistory h
JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
GROUP BY destination_database_name ,
restore_date ,
b.database_name ,
backup_start_date
ORDER BY RestoreDate DESC
GO
SQL Server Send mail backup details
If exists(Select * from [Backup_Status] Where File_Location Like 'F:%' OR File_Location Like '\\blcch12db\F$\%')
Begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
[LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM [dbo].[Backup_Status] Where File_Location Like 'F:%' OR File_Location Like '\\blcch12db\F$\%'
ORDER BY LastBackupTime asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1>
<tr>
<th bgcolor="#66CCFF"> Database_Name </th> <th bgcolor="#66CCFF"> DaysSinceLastBackup </th> <th bgcolor="#66CCFF"> LastBackupDate </th> <th bgcolor="#66CCFF"> LastBackupTime </th> <th bgcolor="#66CCFF"> File_Location </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST_PROFILE',
@body = @body,
@body_format ='HTML',
@recipients = 'abc@xyz.com',
@subject ='SQL Server Backup Status' ;
END
Else
Begin
Print 'No record found!'
End
What got restored from where, by who and when
SELECT
DatabaseRestoredTo = RH.destination_database_name,
TimeOfRestore = RH.restore_date,
UserImplimentingRestore = RH.user_name,
RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END,
ServerWhereBackupTaken = BS.server_name,
UserWhoBackedUpTheDatabase = BS.user_name,
BackupOfDatabase = BS.database_name,
DateOfBackup = BS.backup_start_date,
RestoredFromPath = BMF.physical_device_name
FROM
msdb.dbo.restorehistory RH
INNER JOIN
msdb.dbo.backupset BS
ON
RH.backup_set_id = BS.backup_set_id
INNER JOIN
msdb.dbo.backupmediafamily BMF
ON
BS.media_set_id = BMF.media_set_id
ORDER BY
RH.restore_history_id
Backup history details EMAIL (past 24 hours) in HTML format
Step 1: Create the view in your dba management database.
Step 2: Run the email section T-SQL which will send an email.
FYI - You need to create database mail in SQL Server and enable all required mail properties prior to using this script.
Reference :
http://msdn.microsoft.com/en-us/library/ms188298(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms191189.aspx
Step 3: Create a job which is scheduled to run daily executing the mail T-SQL script. (This script is not included)
Use Cases:
1. In a busy SQL Server where we have 100's of databases taken using scripts, maintenance plans, backup tape media etc to different locations and we don't know where the backup is located.
2. This script email will be handy to get the database name, backup date, backup time and backup location, which will be useful to inform the storage admin or backup admin for the location that needs to be restored from backup disk or tape.
3. Audit for previous backups. If backup is not taken in servers like QA, DEV, UAT etc.
VIEW
====
Step 1: Create a view which is easy to manage.
CREATE view [dbo].[Backup_Status] as SELECT sdb.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NO BACKUP') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 101), 'NOT APPLICABLE') as LastBackupDate,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 108), 'NOT APPLICABLE') as LastBackupTime,
bkpfmly.physical_device_name as File_Location
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bkpst with (readpast) ON bkpst.database_name = sdb.name AND bkpst.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily bkpfmly with (readpast) ON bkpfmly.media_set_id = bkpst.media_set_id
GROUP BY sdb.Name,physical_device_name
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 1),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
Note: This script provides the backup history for the past 24 hours. If you want more days than just 24 hours change the HAVING section.
EX:- 3 days
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 3),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
In case you need the complete history of backup comment the HAVING section.
HTML Email:
===========
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
[LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM Backup_Status ORDER BY LastBackupTime asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1>
<tr>
<th bgcolor="#66CCFF"> Database_Name </th> <th bgcolor="#66CCFF"> DaysSinceLastBackup </th> <th bgcolor="#66CCFF"> LastBackupDate </th> <th bgcolor="#66CCFF"> LastBackupTime </th> <th bgcolor="#66CCFF"> File_Location </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<SQL Database Mail Profile Name>',
@body = @body,
@body_format ='HTML',
@recipients = '<Notification Group email Address>',
@subject ='SQL Server Backup Status' ;
Failed Job Notification
USE [msdb]
GO
--create a DBA Team operator
EXEC msdb.dbo.sp_add_operator @name=N'Production DBA Team', -- Change this to the operator you need to be notified.
@enabled=1,
@email_address=N'DBAS@yourdomain.com' -- Change this to the email address of the operator.
GO
--add notifications for failure to all jobs
DECLARE @QuotedIdentifier char(1); SET @QuotedIdentifier = '' -- use '''' for single quote
DECLARE @ListDelimeter char(1); SET @ListDelimeter = ';'
DECLARE @CSVlist varchar(max) --use varchar(8000) for SQL Server 2000
--no event log, email on failure
SELECT @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier +
'
EXEC msdb.dbo.sp_update_job @job_id=N'''
+ convert(varchar(max),[job_id]) +
''',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_email_operator_name=N''DBA Team''' -- Change this to the operator you need to be notified.
+ @QuotedIdentifier
from msdb.dbo.sysjobs
--print @csvlist
EXEC (@CSVlist)
GO
USE msdb ;
SELECT
DISTINCT
DBRestored = destination_database_name ,
RestoreDate = restore_date ,
SourceDB = b.database_name ,
BackupDate = backup_start_date
FROM RestoreHistory h
JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
GROUP BY destination_database_name ,
restore_date ,
b.database_name ,
backup_start_date
ORDER BY RestoreDate DESC
GO
SQL Server Send mail backup details
If exists(Select * from [Backup_Status] Where File_Location Like 'F:%' OR File_Location Like '\\blcch12db\F$\%')
Begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
[LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM [dbo].[Backup_Status] Where File_Location Like 'F:%' OR File_Location Like '\\blcch12db\F$\%'
ORDER BY LastBackupTime asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1>
<tr>
<th bgcolor="#66CCFF"> Database_Name </th> <th bgcolor="#66CCFF"> DaysSinceLastBackup </th> <th bgcolor="#66CCFF"> LastBackupDate </th> <th bgcolor="#66CCFF"> LastBackupTime </th> <th bgcolor="#66CCFF"> File_Location </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST_PROFILE',
@body = @body,
@body_format ='HTML',
@recipients = 'abc@xyz.com',
@subject ='SQL Server Backup Status' ;
END
Else
Begin
Print 'No record found!'
End
What got restored from where, by who and when
SELECT
DatabaseRestoredTo = RH.destination_database_name,
TimeOfRestore = RH.restore_date,
UserImplimentingRestore = RH.user_name,
RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END,
ServerWhereBackupTaken = BS.server_name,
UserWhoBackedUpTheDatabase = BS.user_name,
BackupOfDatabase = BS.database_name,
DateOfBackup = BS.backup_start_date,
RestoredFromPath = BMF.physical_device_name
FROM
msdb.dbo.restorehistory RH
INNER JOIN
msdb.dbo.backupset BS
ON
RH.backup_set_id = BS.backup_set_id
INNER JOIN
msdb.dbo.backupmediafamily BMF
ON
BS.media_set_id = BMF.media_set_id
ORDER BY
RH.restore_history_id
Backup history details EMAIL (past 24 hours) in HTML format
Step 1: Create the view in your dba management database.
Step 2: Run the email section T-SQL which will send an email.
FYI - You need to create database mail in SQL Server and enable all required mail properties prior to using this script.
Reference :
http://msdn.microsoft.com/en-us/library/ms188298(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms191189.aspx
Step 3: Create a job which is scheduled to run daily executing the mail T-SQL script. (This script is not included)
Use Cases:
1. In a busy SQL Server where we have 100's of databases taken using scripts, maintenance plans, backup tape media etc to different locations and we don't know where the backup is located.
2. This script email will be handy to get the database name, backup date, backup time and backup location, which will be useful to inform the storage admin or backup admin for the location that needs to be restored from backup disk or tape.
3. Audit for previous backups. If backup is not taken in servers like QA, DEV, UAT etc.
VIEW
====
Step 1: Create a view which is easy to manage.
CREATE view [dbo].[Backup_Status] as SELECT sdb.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NO BACKUP') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 101), 'NOT APPLICABLE') as LastBackupDate,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 108), 'NOT APPLICABLE') as LastBackupTime,
bkpfmly.physical_device_name as File_Location
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bkpst with (readpast) ON bkpst.database_name = sdb.name AND bkpst.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily bkpfmly with (readpast) ON bkpfmly.media_set_id = bkpst.media_set_id
GROUP BY sdb.Name,physical_device_name
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 1),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
Note: This script provides the backup history for the past 24 hours. If you want more days than just 24 hours change the HAVING section.
EX:- 3 days
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 3),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
In case you need the complete history of backup comment the HAVING section.
HTML Email:
===========
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
[LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM Backup_Status ORDER BY LastBackupTime asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1>
<tr>
<th bgcolor="#66CCFF"> Database_Name </th> <th bgcolor="#66CCFF"> DaysSinceLastBackup </th> <th bgcolor="#66CCFF"> LastBackupDate </th> <th bgcolor="#66CCFF"> LastBackupTime </th> <th bgcolor="#66CCFF"> File_Location </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<SQL Database Mail Profile Name>',
@body = @body,
@body_format ='HTML',
@recipients = '<Notification Group email Address>',
@subject ='SQL Server Backup Status' ;
Failed Job Notification
USE [msdb]
GO
--create a DBA Team operator
EXEC msdb.dbo.sp_add_operator @name=N'Production DBA Team', -- Change this to the operator you need to be notified.
@enabled=1,
@email_address=N'DBAS@yourdomain.com' -- Change this to the email address of the operator.
GO
--add notifications for failure to all jobs
DECLARE @QuotedIdentifier char(1); SET @QuotedIdentifier = '' -- use '''' for single quote
DECLARE @ListDelimeter char(1); SET @ListDelimeter = ';'
DECLARE @CSVlist varchar(max) --use varchar(8000) for SQL Server 2000
--no event log, email on failure
SELECT @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier +
'
EXEC msdb.dbo.sp_update_job @job_id=N'''
+ convert(varchar(max),[job_id]) +
''',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_email_operator_name=N''DBA Team''' -- Change this to the operator you need to be notified.
+ @QuotedIdentifier
from msdb.dbo.sysjobs
--print @csvlist
EXEC (@CSVlist)
GO
No comments:
Post a Comment