Query to get all databases restore history

During a side-by-side database migration, mostly like you will backup all the user databases from the source SQL Server, copy the backup files to the destination server (which may be a newer version when you are doing an version upgrade), and finally restore all the databases on the destination SQL Server. Below query is handy for you to verify this migration by checking the latest restore history of all the databases in an instance.

;WITH CTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY destination_database_name ORDER BY restore_date DESC) AS row_num, * FROM msdb.dbo.restorehistory
) SELECT d.[name], CTE.restore_date, CTE.restore_type, BS.backup_start_date, BS.server_name, BMF.physical_device_name
FROM master.sys.databases AS D
LEFT JOIN CTE ON D.[name] = CTE.destination_database_name AND CTE.row_num = 1
LEFT JOIN msdb.dbo.backupset AS BS ON CTE.backup_set_id = BS.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily AS BMF ON BS.media_set_id = BMF.media_set_id
ORDER BY D.[name];

This query should be run on the destination SQL Server. It returns all databases including system databases and user databases, the date and time of their latest restore operation, type of restore operation (D = Full, I = Differential, L = Log), date and time of the backup being made, the source SQL Server name, and the physical name of the backup device (the backup file) being used. For example:

By checking the result of this query, you can make sure you got the right backup files to restore on the destination SQL Server.

Handling DDL on Change Data Capture (CDC) enabled Table

SQL Server does not automatically track certain DDL changes in the Change Data Capture log after CDC was enabled on the table. This includes adding new columns to a table after CDC was enabled for the table. To make the CDC log reflects the DDL changes on the source table, it must disable CDC and re-enable CDC on the CDC enabled table. Here comes an example:

USE master

GO

-- Create a database

CREATE DATABASE DbTestCDC;

GO

USE DbTestCDC;

GO

-- Create a table

CREATE TABLE TableTestCDC

(

    ID int NOT NULL PRIMARY KEY,

    FirstName varchar(30),

    LastName varchar(30),

    Email varchar(50)

);

-- Enable CDC for the current database

EXEC sys.sp_cdc_enable_db;

GO

SELECT name, is_cdc_enabled FROM sys.databases;

GO

-- Enable CDC for the specified table in the current database

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

@role_name     = NULL,

@supports_net_changes = 1

GO

-- Check CDC enabled on that table

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';

-- Check CDC retention (default 4320 minutes = 3 days)

SELECT DB_NAME(database_id), * FROM msdb.dbo.cdc_jobs;

-- Testing

INSERT INTO TableTestCDC Values (1, 'Jorge', 'Ramos', 'ramos@yahoo.com');

INSERT INTO TableTestCDC Values (2, 'Andrew', 'Strudwick', 'andrew@yahoo.com');

INSERT INTO TableTestCDC Values (3, 'Thomas', 'Tuchel', 'tuchel@gmail.com');

DELETE FROM TableTestCDC WHERE ID = 1;

UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ' WHERE ID = 2;

DELETE FROM TableTestCDC WHERE ID = 3;

SELECT * FROM TableTestCDC;

GO

WAITFOR DELAY '00:00:05';

GO

-- Query CDC data

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');

SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,

CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,

*

FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');

GO

-- ALTER TABLE on the source table, e.g. add a new column

ALTER TABLE dbo.TableTestCDC ADD NewCol varchar(50) NULL;

GO

-- Disable CDC on that table, and then re-enable CDC to reflect the table DDL changes

EXECUTE sys.sp_cdc_disable_table   

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

    @capture_instance = N'all';

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

@role_name     = NULL,

@supports_net_changes = 1

GO

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';

-- Testing (with New column)

INSERT INTO TableTestCDC Values (4, 'Jorge', 'Ramos', 'ramos@yahoo.com', 'NewCol 1');

INSERT INTO TableTestCDC Values (5, 'Andrew', 'Strudwick', 'andrew@yahoo.com', 'NewCol 2');

INSERT INTO TableTestCDC Values (6, 'Thomas', 'Tuchel', 'tuchel@gmail.com', 'NewCol 3');

DELETE FROM TableTestCDC WHERE ID = 4;

UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ', NewCol = 'New Col X' WHERE ID = 5;

DELETE FROM TableTestCDC WHERE ID = 6;

SELECT * FROM TableTestCDC;

GO

-- Query CDC data

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');

SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,

CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,

*

FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');

GO

Get the Last Known Actual Execution Plan

SQL Server 2019 introduces sys.dm_exec_query_plan_stats new DMV which returns the equivalent of the last known actual execution plan for a previously cached query plan (All plans are estimated plans. "Actual" plan is essentially = Estimated Plan + Runtime Metrics). You can turn this new feature on at the server level using trace flag 2451 or at database level with LAST_QUERY_PLAN_STATS a new database scoped configuration. For example:

USE [<DB_Name>];

GO

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

GO

Then you can query the last known actual execution plan likes below:

SELECT cp.objtype, DB_NAME(st.dbid) AS DB, OBJECT_NAME(st.objectid, st.dbid) AS ObjectName, st.[text], qps.query_plan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps

--WHERE OBJECT_NAME(st.objectid, st.dbid) = '<stored proc/function name>'

;

In the result, you can click on the query_plan XML to show the graphical plan.

As you see, the plan includes actual runtime metrics.

Dedicated Administrator Connection (DAC)

SQL Server Dedicated Admin Connection (DAC) allows database administrator to connect to SQL Server when it is in emergency. This diagnostic connection allows us to execute diagnostic queries and troubleshoot problems even when SQL Server isn't responding to standard connection requests. By default, the connection is only allowed from a local client running on the server. To guarantee resource available for DAC, only one DAC is allowed per instance of SQL Server. If a DAC connection is already active, any new DAC request is denied with error. Remote connections are disallowed unless being configured by using sp_configure 'remote admin connections option. SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon server startup. The error log contains the port number the DAC is listening on. On Failover Cluster Instance (FCI), the DAC will be off by default, but Microsoft recommends enabling remote admin connections on FCI.

Below screenshot shows the mssql error log entry indicating the DAC port of a default instance, most likely TCP 1434.

If there are multiple instances, any subsequent named instances will get a dynamically allocated port in the range 49152 to 65535 as their DAC port.

DAC are not supported via SSMS as it establishes multiple connections by design.

Instead, you can open a new query window in SSMS by pressing the "New Query" button, specifying DAC by ADMIN:<ServerName>.

Then you can run below query to confirm you are in a DAC connection:

SELECT session_id, program_name, P.name AS endpoint_name, is_admin_endpoint

FROM sys.dm_exec_sessions S JOIN sys.tcp_endpoints P ON S.endpoint_id = P.endpoint_id;

If you try to open one more DAC connection, you will get the following error.

For FCI, run the follow SQL command to enable remote DAC:

EXEC sp_configure 'remote admin connections', 1;

GO

RECONFIGURE;

GO
It will take effect immediately.

Export Schema and Table Data using Powershell

$servername = "servername\instancename";

$currentDate = Get-Date -Format "yyyyMMdd";

#Variables - details of the connection, stored procedure and parameters

$connectionString = "server=$servername;trusted_connection=true;";

$sqlQuery = "SELECT [name] FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB');";

#$param1Value = "SomeValue";

#SQL Connection - connection to SQL server

$sqlConnection = new-object System.Data.SqlClient.SqlConnection;

$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

$sqlCommand.Connection = $sqlConnection;

$sqlCommand.CommandText = $sqlQuery;

#$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

#SQL Adapter - get the results using the SQL Command

$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter ;

$sqlAdapter.SelectCommand = $sqlCommand;

$dataSet = new-object System.Data.Dataset;

$recordCount = $sqlAdapter.Fill($dataSet) ;

#Close SQL Connection

$sqlConnection.Close();

#Get single table from dataset

$data = $dataSet.Tables[0];

#Loop through each row of data

foreach($row in $data)

{

    #BEGIN - Export Schema

    $dbName = $row.name;

    $schemaQuery = "SELECT SCHEMA_NAME(tab.[schema_id]) AS [schema], tab.[name] AS table_name, col.column_id, col.[name] AS column_name, t.[name] as data_type, col.max_length, col.[precision], col.scale FROM sys.tables AS tab JOIN sys.columns AS col ON tab.[object_id] = col.[object_id] LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id ORDER BY [schema], table_name, column_id;";

    $outFile = "D:\DWExport\Schema $dbName $currentDate.bcp";

    $bcpSchemaCmd = "bcp `"$schemaQuery`" queryout `"$outFile`" -T -w -S $servername -d $dbName";

    Invoke-Expression $bcpSchemaCmd;

    #END - Export Schema

    #BEGIN - Export Table Data

    $connectionString = "server=$servername;database='$dbName';trusted_connection=true;";

    $sqlQuery = "SELECT s.name+'.'+t.name AS table2PartName FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;";

    #SQL Connection - connection to SQL server

    $sqlConnection = new-object System.Data.SqlClient.SqlConnection;

    $sqlConnection.ConnectionString = $connectionString;

    #SQL Command - set up the SQL call

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

    $sqlCommand.Connection = $sqlConnection;

    $sqlCommand.CommandText = $sqlQuery;

    #$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

    #SQL Adapter - get the results using the SQL Command

    $sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter ;

    $sqlAdapter.SelectCommand = $sqlCommand;

    $dataSet = new-object System.Data.Dataset;

    $recordCount = $sqlAdapter.Fill($dataSet) ;

    #Close SQL Connection

    $sqlConnection.Close();

    #Get single table from dataset

    $data = $dataSet.Tables[0];

    #Loop through each row of data

    foreach($row in $data)

    {

        $table2PartName = $row.table2PartName;

        $outFile = "D:\DWExport\$dbName.$table2PartName $currentDate.bcp";

        $bcpTableQueryCmd = "bcp $table2PartName out `"$outFile`" -T -w -S $servername -d $dbName";

        Invoke-Expression $bcpTableQueryCmd;

    }

}

BCP export data and schema from all tables in all databases

 EXEC master.dbo.sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1;

RECONFIGURE WITH OVERRIDE;

GO

SET NOCOUNT ON;

DECLARE @bcpSchema nvarchar(4000), @db sysname;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

    SELECT [name] FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB');

OPEN cur

WHILE 1 = 1

BEGIN

    FETCH cur INTO @db;

    IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpSchema = N'bcp "SELECT SCHEMA_NAME(tab.[schema_id]) AS [schema], tab.[name] AS table_name, col.column_id, col.[name] AS column_name, t.[name] as data_type, col.max_length, col.[precision], col.scale FROM sys.tables AS tab JOIN sys.columns AS col ON tab.[object_id] = col.[object_id] LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id ORDER BY [schema], table_name, column_id;" queryout "D:\DWExport\Schema ' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @db + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME + ' -d ' + @db;

EXEC master..xp_cmdshell @bcpSchema;

END

CLOSE cur

DEALLOCATE cur

/* EXPORT SCHEMA FINISH */

/* EXPORT TABLE DATA START */

DECLARE @allTables table (completeTableName nvarchar(4000));

DECLARE @bcpTableData nvarchar(4000), @completeTableName nvarchar(4000);

INSERT @allTables (completeTableName)

EXEC sp_msforeachdb 'USE [?];

IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''ReportServer'', ''ReportServerTempDB'')

BEGIN

SELECT ''?''+''.''+s.name+''.''+t.name

FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;

END';

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT completeTableName FROM @allTables ORDER BY completeTableName;

OPEN cur

WHILE 1 = 1

BEGIN

FETCH cur INTO @completeTableName;

IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpTableData = N'bcp ' + @completeTableName + ' out "D:\DWExport\' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @completeTableName + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME;

EXEC master..xp_cmdshell @bcpTableData;

END

CLOSE cur

DEALLOCATE cur

Database Backup Report

CREATE OR ALTER PROC DbBackupReport

AS

BEGIN

SELECT @@SERVERNAME AS serverName, D.[name] AS databaseName, D.recovery_model_desc,

MAX(CASE WHEN B.[type] = 'D' THEN B.backup_finish_date ELSE NULL END) AS LastFullBackup,

MAX(CASE WHEN B.[type] = 'I' THEN B.backup_finish_date ELSE NULL END) AS LastDiffBackup,

MAX(CASE WHEN B.[type] = 'L' THEN B.backup_finish_date ELSE NULL END) AS LastLogBackup

INTO #Result

FROM master.sys.databases D LEFT JOIN msdb.dbo.backupset B ON D.[name] = B.[database_name]

WHERE D.[name] NOT IN ('tempdb', 'model')

GROUP BY D.[name], D.recovery_model_desc;

SELECT serverName, databaseName, recovery_model_desc,

ISNULL(LastFullBackup, '19000101') AS LastFullBackup, LastDiffBackup, LastLogBackup,

CASE WHEN recovery_model_desc <> 'SIMPLE' AND ISNULL(LastFullBackup, '19000101') < DATEADD(day, -7, GETDATE()) THEN 'NO' ELSE 'YES' END AS NonSimpleModeFullBackupin7Days,

CASE WHEN ISNULL(LastFullBackup, '19000101') < DATEADD(hour, -24, GETDATE()) AND

ISNULL(LastDiffBackup, '19000101') < DATEADD(hour, -24, GETDATE())

THEN 'NO' ELSE 'YES' END AS FullOrDiffBackupIn24Hours,

CASE WHEN recovery_model_desc <> 'SIMPLE' AND ISNULL(LastLogBackup, '19000101') < DATEADD(hour, -24, GETDATE()) THEN 'NO' ELSE 'YES' END AS NonSimpleModeLogIn24Hours

INTO #Output FROM #Result;

SELECT *, CASE WHEN 'NO' IN (NonSimpleModeFullBackupin7Days, FullOrDiffBackupIn24Hours, NonSimpleModeLogIn24Hours) THEN 'Red' ELSE 'Black' END AS HighLight FROM #Output ORDER BY HighLight DESC;

END

GO

EXEC DbBackupReport;

You can use the "HighLight" column values in the stored procedure result, to make a custom formatting in the SSRS report, to highlight the databases that need your concern.