Generate Scripts from existing email configurations

 

Generate SQL Server Database Mail Configuration Scripts Using T-SQL

Setting up Database Mail in SQL Server is an important task, especially for sending alerts, job notifications, or custom messages from your SQL instance. While this can be configured through SQL Server Management Studio (SSMS), generating a repeatable T-SQL script allows you to automate and replicate your mail configuration across environments.

In this post, we'll walk through a T-SQL script that dynamically generates the configuration commands for existing Database Mail profiles, accounts, and server settings. This script pulls metadata from msdb and creates a reusable script block for recreating the configuration elsewhere.

---

Overview of the Script

The script does the following:

1. Enables required configuration settings (Database Mail XPs).

2. Generates script blocks to:

   • Create mail profiles if they don’t exist.

   • Create mail accounts with their respective settings.

   • Associate mail accounts with profiles.

3. Optionally includes commented-out commands to drop these configurations.

This is useful for backup purposes or for migrating Database Mail configurations to another server.

---

T-SQL Script for Mail Configuration Generation

Here’s the simplified logic behind the actual T-SQL that builds the configuration script:

USE msdb;
GO

DECLARE @TheResults VARCHAR(MAX), 
        @vbCrLf CHAR(2);
        
SET @vbCrLf = CHAR(13) + CHAR(10);
SET @TheResults = '
USE master
GO
sp_configure ''show advanced options'', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ''Database Mail XPs'', 1
GO
RECONFIGURE
GO
';

-- Append configuration for each mail profile/account
SELECT @TheResults = @TheResults + '
--------------------------------------------------------------------------------------------------
-- BEGIN Mail Settings: ' + p.name + '
--------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_profile_sp 
        @profile_name = ''' + p.name + ''',
        @description = ''' + ISNULL(p.description, '') + ''';
END;

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_account_sp 
        @account_name = ''' + a.name + ''',
        @email_address = ''' + a.email_address + ''',
        @display_name = ''' + a.display_name + ''',
        @replyto_address = ''' + a.replyto_address + ''',
        @description = ''' + a.description + ''',
        @mailserver_name = ''' + s.servername + ''',
        @mailserver_type = ''' + s.servertype + ''',
        @port = ''' + CONVERT(VARCHAR, s.port) + ''',
        @username = ''' + ISNULL(c.credential_identity, '') + ''',
        @password = ''NotTheRealPassword'',
        @use_default_credentials = ' + CAST(s.use_default_credentials AS VARCHAR) + ',
        @enable_ssl = ' + CAST(s.enable_ssl AS VARCHAR) + ';
END;

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysmail_profileaccount pa
    JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
    JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
    WHERE p.name = ''' + p.name + ''' AND a.name = ''' + a.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_profileaccount_sp 
        @profile_name = ''' + p.name + ''',
        @account_name = ''' + a.name + ''',
        @sequence_number = ' + CONVERT(VARCHAR, pa.sequence_number) + ';
END;

/* -- Uncomment to generate cleanup (drop) statements
IF EXISTS (...) -- Checks and deletes associations, accounts, and profiles
BEGIN
    EXEC msdb.dbo.sysmail_delete_profileaccount_sp ...
    EXEC msdb.dbo.sysmail_delete_account_sp ...
    EXEC msdb.dbo.sysmail_delete_profile_sp ...
END;
*/
'
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
LEFT JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT JOIN sys.credentials c ON s.credential_id = c.credential_id;

-- Split the generated text into rows for easier reading
;WITH Tally(N) AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects
),
ItemSplit AS (
    SELECT 
        N,
        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf, N + LEN(@vbCrLf),
                  CHARINDEX(@vbCrLf, @vbCrLf + @TheResults + @vbCrLf, N + LEN(@vbCrLf)) - N - LEN(@vbCrLf)) AS Item
    FROM Tally
    WHERE N < LEN(@vbCrLf + @TheResults)
      AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf, N, LEN(@vbCrLf)) = @vbCrLf
)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS LineNumber, Item
FROM ItemSplit;

---

Why Use This Script?

• Documentation: Quickly generate a readable backup of your email configuration.

• Migration: Move Database Mail settings to another SQL Server instance with minimal effort.

• Automation: Integrate this logic into CI/CD pipelines or setup scripts.

🔐 Note: The script replaces real passwords with 'NotTheRealPassword'. Always handle credentials securely when adapting this for production use.

SQL Server database backup history

 

How to View SQL Server Backup History

Monitoring database backups is essential for maintaining data integrity and recovery readiness. In this post, we’ll look at how to retrieve backup history in SQL Server using T-SQL queries. These scripts help you analyze when your databases were last backed up, the type of backups performed, and the devices used.

---

Viewing Recent Backup History

The following script displays detailed information about all database backups from the past 30 days for a specified database:

-- Retrieve backup history for the past 30 days
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS ServerName,
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.expiration_date,
    CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Transaction Log' 
    END AS BackupType,
    bs.backup_size,
    bf.logical_device_name,
    bf.physical_device_name,
    bs.name AS BackupSetName,
    bs.description
FROM 
    msdb.dbo.backupmediafamily AS bf
    INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
WHERE 
    bs.backup_start_date >= DATEADD(DAY, -30, GETDATE()) 
    AND bs.database_name = 'myDatabase'
ORDER BY 
    bs.database_name, 
    bs.backup_finish_date;

This query pulls data from the msdb system database and provides insight into the backup type, size, device used, and more.

---

Finding the Most Recent Backup per Database

To identify the latest full backup for each database on the server, use the query below. This script returns the most recent full backup details for all databases.

-- Get the latest full backup information for each database
SELECT 
    A.Server,
    A.last_db_backup_date,
    B.backup_start_date,
    B.expiration_date,
    B.backup_size,
    B.logical_device_name,
    B.physical_device_name,
    B.backupset_name,
    B.description
FROM (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        MAX(bs.backup_finish_date) AS last_db_backup_date
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
    GROUP BY 
        bs.database_name
) AS A
LEFT JOIN (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.expiration_date,
        bs.backup_size,
        bf.logical_device_name,
        bf.physical_device_name,
        bs.name AS backupset_name,
        bs.description
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
) AS B 
ON 
    A.Server = B.Server 
    AND A.database_name = B.database_name 
    AND A.last_db_backup_date = B.backup_finish_date
ORDER BY 
    A.database_name;

---

These queries are helpful for DBAs who want to quickly validate the backup status of their databases or audit recent backup operations.

For more details on the backup-related dynamic management views, refer to the Microsoft Documentation on backupset.

how to list Query Statistics

 

Understanding dm_exec_query_stats in SQL Server

The dm_exec_query_stats view in SQL Server provides aggregated performance statistics for cached query plans. Each row in this view corresponds to a single query statement within a cached plan. The data stored here is tied to the lifetime of the plan, meaning that when a plan is removed from the cache, the associated rows in this view are also deleted.

---

Query to Retrieve Data Based on Execution Count

This SQL query displays results based on the number of times each query has been executed.

SELECT TOP 1000 
    SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, 
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.TEXT)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2) + 1),
    qs.execution_count,
    qs.total_logical_reads,
    qs.last_logical_reads,
    qs.total_logical_writes,
    qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time / 1000000 AS total_elapsed_time_in_S,
    qs.last_elapsed_time / 1000000 AS last_elapsed_time_in_S,
    qs.last_execution_time,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY execution_count DESC;

---

Example: Top Five Queries by Average CPU Time

The following query returns the top five queries based on average CPU time. The queries are grouped by their query hash, which ensures that logically equivalent queries are aggregated based on their total resource consumption.

SELECT TOP 5 
    query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1, 
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
        - QS.statement_start_offset) / 2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

---

Example: Query to Retrieve Row Count Aggregates

This query retrieves aggregate row count information, including total rows, minimum rows, maximum rows, and last rows for queries.

SELECT 
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1, 
    (CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
        ELSE qs.statement_end_offset END - 
        qs.statement_start_offset) / 2) AS query_text,
    qt.dbid, 
    dbname = DB_NAME(qt.dbid), 
    qt.objectid,
    qs.total_rows, 
    qs.last_rows, 
    qs.min_rows, 
    qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT%'
ORDER BY qs.execution_count DESC;

---

For more detailed information, you can refer to the official documentation: Sys.dm_exec_query_stats Documentation.

Database side pagination

Pagination is a technique used to break up large datasets into smaller chunks, making it easier to display them on screen. This method is often referred to as paging. It is commonly used in applications, and one of the best examples can be seen in Google Search.

Method 1

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

SELECT [SalesOrderID]
 ,[SalesOrderDetailID]
 ,[CarrierTrackingNumber]
 ,[OrderQty]
 ,[ProductID]
 ,[SpecialOfferID]
 ,[TotalCount] = COUNT(*) OVER ()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY;
GO

Method 2

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

WITH Main_CTE
AS (
 SELECT [SalesOrderID]
  ,[SalesOrderDetailID]
  ,[CarrierTrackingNumber]
  ,[OrderQty]
  ,[ProductID]
  ,[SpecialOfferID]
 FROM [dbo].[SalesOrderDetail]
 )
 ,Count_CTE
AS (
 SELECT COUNT(*) AS [TotalCount]
 FROM Main_CTE
 )
SELECT *
FROM Main_CTE
 ,Count_CTE
ORDER BY Main_CTE.SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY

Method 3

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

SELECT [SalesOrderID]
 ,[SalesOrderDetailID]
 ,[CarrierTrackingNumber]
 ,[OrderQty]
 ,[ProductID]
 ,[SpecialOfferID]
 ,[TotalCount]
FROM [dbo].[SalesOrderDetail]
CROSS APPLY (
 SELECT COUNT(*) TotalCount
 FROM [dbo].[SalesOrderDetail]
 ) [Count]
ORDER BY SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY
GO

Find missing index warnings from cached plans

This query could take some time on a busy instance, so be cautious when executing it on production environments.

The following SQL query is designed to retrieve the top 25 cached plans that are missing index recommendations. It may take some time to execute on heavily loaded instances:

SELECT TOP (25) OBJECT_NAME(objectid) AS [ObjectName]
    ,cp.objtype
    ,cp.usecounts
    ,cp.size_in_bytes
    ,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
    AND dbid = DB_ID()
ORDER BY cp.usecounts DESC
OPTION (RECOMPILE);

This query checks the cached execution plans for missing index recommendations in the current database. It can help identify potentially useful indexes that could improve performance. The query returns:

• ObjectName: The name of the object (such as a table) associated with the cached plan.
• objtype: The type of object (e.g., table, index, etc.) that the cached plan pertains to.
• usecounts: The number of times the cached plan has been executed.
• size_in_bytes: The size of the cached plan in bytes.
• query_plan: The XML query plan that details the execution of the query.

Note: The query uses the WITH (NOLOCK) hint to avoid blocking and allow for faster execution, though it may return uncommitted data. The OPTION (RECOMPILE) ensures that the query is recompiled each time it is executed, which is important for reflecting the most up-to-date plan.