Backup all DMV’s to a monitor database 2

Backup All DMV’s to a Monitor DatabaseEvery once in a while I have the need to access some DMV’s from remote locations. I use these for performance tuning with the use of the query plans, but also to get some statistical data. To gather all this information on a scheduled basis I have created the script below to backup all the information into a monitor database.

First I backup the DMV’s which I gathered from ‘sys.all_views’. Next I look into the ‘sys.all_columns’ if there is a ‘plan_handle’ or a ‘sql_handle’, so I can use the ‘sys.dm_exec_query_plan’ and ‘sys.dm_exec_sql_text’ DMF’s. These will give back a query plan or a sql text for these DMV’s, which can be used later on when you are gathering information about your environment.

Be carefull to run this script blind on a production environment. The load can become quite high if you have a lot of cached query plans and such.

You can download the SQL file at the bottom of this post.

use master
go
set nocount on
--drop database DynamicManagementViewsBackup
-----------------------------------------------------------------------------------------------
-- Create database if it does not exists
-----------------------------------------------------------------------------------------------
if not exists(select 1 from sys.databases where name = 'DynamicManagementViewsBackup')
begin
  create database DynamicManagementViewsBackup 
  alter database DynamicManagementViewsBackup set recovery simple
end
go
use DynamicManagementViewsBackup
go
-----------------------------------------------------------------------------------------------
-- Declare local variables and temptable
-----------------------------------------------------------------------------------------------
declare @SQLStatements table (
  Id int identity(1, 1) not null
, SQLStatement nvarchar(300)
, SelectStatementBackupDMV nvarchar(300)
)
declare @totrow int
      , @currow int     
      , @SQLStatement nvarchar(300) 
      , @SelectStatementBackupDMV nvarchar(300)

set @SQLStatement = ''
set @SelectStatementBackupDMV = '' 
set @currow = 1      
-----------------------------------------------------------------------------------------------
-- Insert queries to process - standard DMV's
-----------------------------------------------------------------------------------------------
insert
into    @SQLStatements
select  replace(replace(replace(replace(replace(
          'select * 
           into   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_[YY][MM][DD] 
           from   [SCHEMA_NAME].[DMV_NAME]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
      , replace(replace(replace(replace(replace(
          'select * 
           from   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_[YY][MM][DD]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
from    sys.all_views v
        inner join sys.schemas s
          on  s.schema_id = v.schema_id
          and s.name = 'sys'
where   left(v.name, 2) = 'dm'
order by v.name
set @totrow = @@ROWCOUNT
-----------------------------------------------------------------------------------------------
-- Insert queries to process - DMV's with plan_handle
-----------------------------------------------------------------------------------------------
insert
into    @SQLStatements
select  replace(replace(replace(replace(replace(
          'select * 
           into   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_PlanHandle_[YY][MM][DD] 
           from   [SCHEMA_NAME].[DMV_NAME] a 
                  cross apply sys.dm_exec_query_plan(a.plan_handle) b'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
      , replace(replace(replace(replace(replace(
          'select * 
           from   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_PlanHandle_[YY][MM][DD]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
from    sys.all_views v
        inner join sys.schemas s
          on  s.schema_id = v.schema_id
          and s.name = 'sys'
        inner join sys.all_columns c
          on  c.object_id = v.object_id
          and c.name = 'plan_handle'
where   left(v.name, 2) = 'dm'
order by v.name
set @totrow = @totrow + @@ROWCOUNT
-----------------------------------------------------------------------------------------------
-- Insert queries to process - DMV's with sql_handle
-----------------------------------------------------------------------------------------------
insert
into    @SQLStatements
select  replace(replace(replace(replace(replace(
          'select * 
           into   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_SqlText_[YY][MM][DD] 
           from   [SCHEMA_NAME].[DMV_NAME] a 
                  cross apply sys.dm_exec_sql_text(a.sql_handle) b'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
      , replace(replace(replace(replace(replace(
          'select * 
           from   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_SqlText_[YY][MM][DD]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
from    sys.all_views v
        inner join sys.schemas s
          on  s.schema_id = v.schema_id
          and s.name = 'sys'
        inner join sys.all_columns c
          on  c.object_id = v.object_id
          and c.name = 'sql_handle'
where   left(v.name, 2) = 'dm'
order by v.name
set @totrow = @totrow + @@ROWCOUNT
-----------------------------------------------------------------------------------------------
-- Loop through queries and execute them
-----------------------------------------------------------------------------------------------
while @totrow > 0 and @currow <= @totrow
begin
  ---------------------------------------------------------------------------------------------
  -- Get SQL Statement to execute
  ---------------------------------------------------------------------------------------------
  select  @SQLStatement = SQLStatement
        , @SelectStatementBackupDMV = SelectStatementBackupDMV
  from    @SQLStatements
  where   Id = @currow
  ---------------------------------------------------------------------------------------------
  -- Execute SQL Statement
  ---------------------------------------------------------------------------------------------
  print replicate('-', 150)
  print @SelectStatementBackupDMV

  exec sp_executesql @SQLStatement
  ---------------------------------------------------------------------------------------------
  -- Get next row identifier
  ---------------------------------------------------------------------------------------------
  set @currow = @currow + 1
end

 
Download the complete file right here : Backup All DMV’s to a Monitor Database
Happy tuning