Wednesday, 27 March 2013

How to Export SQL Profiler / Trace to SQL Server as table ?


It profiler / trace data is already saved in a .trc file, then we can read the trace file using function name ::fn_trace_gettable()

TSQL query to open a trace file in SQL Server Management Studio (SSMS)
SELECT * FROM ::fn_trace_gettable
('<<SQL Server Trace File Name>>', default)


this will display the data in result pane, so now if we want to save this in a SQL Server table then we can easily add a INTO clause in this statement and save data in SQL table as below.

SELECT * into table_name_in_which_we_want_save_data FROM ::fn_trace_gettable
('<<SQL Server Trace File Name>>', default)

SQL Server CPU’s at 100%?

This was the issues that I faced roughly 2 months after I started my DBA career.
When I was learning through the pages, about the basics, I was surrounded by this issue.
Every now and again our live production sql server’s CPU’s would rise to 100%.
There was no consistency in when it would happen, could be first thing in the morning, mid-afternoon or late evening.

Instinct told me that CPU’s at 100% will be an issue with a big process that probably shouldn’t be running during peak times table scans or the likes, a query with possibly a lot of big table / index scans or hash joins. From task manager I could see that the biggest CPU hogger was sql server itself, my primary focus was to investigate the procedures currently running.

I started simple with a sp_who2 and sp_lock. These commands themselves took up to 30seconds to return any output! When they did the number of processes running was about normal but the one thing that did stand out was the number of blocked processes.
Since there were a lot of procedures blocking a number of other processes I decided to look into sysprocesses for processes and queries that were long running, open transactions and high CPU time to see if anything stood out as being the problem.


So, to re-cap.

  • There are no external / 3rd party processes running on this box as its a dedicated sql server. The process with high cpu was sql server itself.
  • There were no long running queries / high cpu time that stood out, almost every procedure had an open transaction as they were taking so long to process.
Sure enough, failing the server over and restarting it was the only option that came out.
But you can't keep doing that every now and then.
Checking the sql logs show nothing other than the fact that I’ve failed the server over. 

This script captures the server, the max free size, the Total Available and the capture date.

USE DBName
GO
--Create table to capture results
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'VASAvailability') AND type = (N'U'))
DROP TABLE dbo.VASAvailability
GO
CREATE TABLE dbo.VASAvailability
(
VASAvailabilityID INT IDentity(1,1),
ServerName Varchar(100),
TotalAvailMem_KB INT,
MaxFreeSize_KB INT,
CreatedDate datetime
)
GO


--Create VAS view
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'vwVas_Summary') AND type = (N'V'))
DROP VIEW dbo.vwVas_Summary
GO
CREATE VIEW dbo.vwVas_Summary AS
SELECT
Size = Vas_Dump.Size,
Reserved = sum(case (convert (INT,Vas_Dump.Base) ^ 0) when 0 then 0 else 1 end),
Free = sum(case (convert (INT,Vas_Dump.Base) ^ 0x0) when 0 then 1 else 0 end)
FROM
(
--- combine all allocation according with allocation base, don't take into
--- account allocations with zero allocation_base
---
SELECT CONVERT (varbinary,sum(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address<> 0x0
GROUP BY region_allocation_base_address
UNION
(
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
---
SELECT CONVERT (varbinary,region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0)
)
as Vas_Dump
GROUP BY Size
GO


Below is the script I used to insert the results of the vas (Total and Max), all that was required was to create the job and schedule it to run every minute.

INSERT INTO dbo.VASAvailability(ServerName,TotalAvailMem_KB,MaxFreeSize_KB,CreatedDate)
SELECT  @@ServerName, ,SUM(CONVERT(BIGINT , Size) * Free) / 1024 AS [Totalavailmem,KB] ,CAST(MAX(Size) AS BIGINT) / 1024 AS [Maxfreesize,KB] ,getdate() FROM    vwVas_Summary WHERE   Free <> 0




Database Mail

Been into configuring this feature lately.
Though this feature is as easy setting up as starting the management studio, there are still some issues related to it that might kill your time.

Today i’m providing a list of Database Mail scripts I use when troubleshooting issues. 
Firstly, a good place to start is to check the status of your Database Mail:

1. EXEC msdb.dbo.sysmail_help_status_sp
it may be possible that it isn’t sending mail because it hasn’t been started 

Following on from this, if you discover that Database Mail has in fact stopped then you can start it again with:

2. EXEC msdb.dbo.sysmail_start_sp
NOTE: It can also be stopped by simply running

3. EXEC msdb.dbo.sysmail_stop_sp
If Database Mail is running then it may be that Service Broker has not been enabled:

4. SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

Another technique I use when troubleshooting Mail issues (if its running and the settings are correct) is to check if anything is actually stuck in the SQL Server Mail system, it can be apparent that mail is sat queued but unable to send. A quick and easy way to determine this is to run:

5. EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
If the value is 0 then you can determine that there isn’t actually anything in the queue. If the value is anything other than 0 then you can investigate this further by running one or all of the below:

Test to see if your mail has actually been sent
6. SELECT * FROM msdb.dbo.sysmail_sentitems where sent_date &gt; dateadd(d,-1,getdate());


Check the mail eventlog for further errors

7. SELECT * FROM msdb.dbo.sysmail_event_log;

Check for any mail item which is sitting at the unsent status

8. SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'unsent';

A favourite of mine, determine the reasons why a mail item has actually failed

9.SELECT items.subject,
    items.last_mod_date
    ,l.description ,*
FROM msdb.dbo.sysmail_faileditems as items
INNER JOIN msdb.dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > getdate()-1
The script above tends to be the ideal script to run in order to find out why an email isn’t being sent, i tend to find 90% of my issues by running that script!

Something else worth checking is whether the user actually has permissions to be sending emails via SQL Server:

Check members of the DatabaseMailUserRole

10. EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'
These are just a handful of queries that can be ran when troubleshooting Database Mail issues, by no means is it a complete list but from my experience i rarely have to go elsewhere. There has been times when i’ve had to venture into amending retry attempts, querying sys.dm_broker_queue_monitors and sys.service_queues but this has been very rare circumstances so I’ve not included the queries for these

11. Most importantly, if everything seems fine and still the mail status is shown as failed or unsent,
just check whether the "DATABASE ENGINE" service and "SQL AGENT" service are running under same  logon.
They running under different logons might cause authentication problem at mail server.

SQL Server : Automatically Reindex All Tables in a SQL Server Database

This script will automatically reindex all indexes the tables in a selected database. When DBCC DBREINDEX is used to rebuild indexes, bear in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.



USE DatabaseName

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor





the same can be done for all available schemas in database-


USE DatabaseName

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_schema+'.'+table_name as table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor




or more efficiently, it all can be done with-

EXEC sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?','', 90)"

SQL Server : Compare Data in SQL Server Databases


-----------------------------------------------------------------------------------------
-- Comparing data in SQL Server Databases
-----------------------------------------------------------------------------------------
-- 1. Data comparison itself.
-- 1.1 Get information about unique keys in the tables. If there are unique keys then one of them
-- (PK is a highest priority candidate for this role) will be used to specify rows with
-- different data.
-- 1.2 Get information about all data columns in the table and form predicates that will be
-- used to compare data.
-- 1.3 Compare data with the criteria:
-- a. if some unique keys from the table from first database do not exist in second db (only
-- for tables with a unique key)
-- b. if some unique keys from the table from second database do not exist in first db (only
-- for tables with a unique key)
-- c. if there are rows with the same values of unique keys and different data in other
-- columns (only for tables with a unique key)
-- d. if there are rows in the table from first database that don't have a twin in the
-- table from second db
-- e. if there are rows in the table from second database that don't have a twin in the
-- table from first db
--------------------------------------------------------------------------------------------
-- Parameters:
-- 1. @db1 - name of first database to compare
-- 2. @db2 - name of second database to compare
-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be
-- compared
-- 4. @NumbToShow - number of rows with differences to show. Default - 10.
-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should
-- be compared. Default - 0
-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp
-- data type. Default - 0
-- 7. @VerboseLevel - if set to 1 allows to print querues used for data comparison
--------------------------------------------------------------------------------------------
-- ##CompareStr - will be used to pass comparing strings into dynamic script
-- to execute the string


CREATE PROCEDURE CompareDB
@db1 VARCHAR(250),
@db2 VARCHAR(250),
@TabList VARCHAR(4000),
@NumbToShow INT,
@OnlyStructure BIT,
@NoTimestamp BIT,
@VerboseLevel BIT
AS
if exists (select * from tempdb.dbo.sysobjects where name like '##CompareStr%')
drop table ##CompareStr
create table ##CompareStr (Ind int, CompareStr varchar(8000))

if exists (select * from tempdb.dbo.sysobjects where name like '#DiffTables%')
drop table #DiffTables
create table #DiffTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#IdenticalTables%')
drop table #IdenticalTables
create table #IdenticalTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#EmptyTables%')
drop table #EmptyTables
create table #EmptyTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#NoPKTables%')
drop table #NoPKTables
create table #NoPKTables (Name sysname)

if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')
truncate table #IndList1
else
create table #IndList1 (IndId int, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')
truncate table #IndList2
else
create table #IndList2 (IndId smallint, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))

print Replicate('-',51)
print 'Comparing data in tables with indentical structure:'
print Replicate('-',51)
--------------------------------------------------------------------------------------------
-- Cursor for all tables in dbs (or for all specified tables if parameter @TabList is passed)
--------------------------------------------------------------------------------------------
declare @SqlStrGetListOfKeys1 varchar(8000)
declare @SqlStrGetListOfKeys2 varchar(8000)
declare @SqlStrGetListOfColumns varchar(8000)
declare @SqlStrCompareUKeyTables varchar(8000)
declare @SqlStrCompareNonUKeyTables varchar(8000)
set @SqlStrGetListOfKeys1 = '
declare @sqlStr varchar(8000)
declare @ExecSqlStr varchar(8000)
declare @PrintSqlStr varchar(8000)
declare @Tab varchar(128)
declare @d1User varchar(128)
declare @d2User varchar(128)
declare @KeyAndStr varchar(8000)
declare @KeyCommaStr varchar(8000)
declare @AndStr varchar(8000)
declare @Eq varchar(8000)
declare @IndId int
declare @IndStatus int
declare @CurrIndId smallint
declare @CurrStatus int
declare @UKey sysname
declare @Col varchar(128)
declare @LastUsedCol varchar(128)
declare @xType int
declare @Len int
declare @SelectStr varchar(8000)
declare @ExecSql nvarchar(1000)
declare @NotInDB1 bit
declare @NotInDB2 bit
declare @NotEq bit
declare @Numb int
declare @Cnt1 int
declare @Cnt2 int
set @Numb = 0

declare @StrInd int
declare @i int
declare @PrintStr varchar(8000)
declare @ExecStr varchar(8000)
declare TabCur cursor for

select d1o.name, d1u.name, d2u.name from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o,
'+@db1+'.dbo.sysusers d1u, '+@db2+'.dbo.sysusers d2u
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.uid = d1u.uid and d2o.uid = d2u.uid
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)
order by 1

open TabCur
fetch next from TabCur into @Tab, @d1User, @d2User
while @@fetch_status = 0
begin
set @Numb = @Numb + 1
print Char(13)+Char(10)+LTRIM(STR(@Numb))+''. TABLE: [''+@Tab+''] ''

set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db1+'.[''+@d1User+''].[''+@Tab+'']''
exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt1 output
print CHAR(10)+STR(@Cnt1)+'' rows in '+@db1+'''
set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db2+'.[''+@d2User+''].[''+@Tab+'']''
exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt2 output
print STR(@Cnt2)+'' rows in '+@db2+'''
if @Cnt1 = 0 and @Cnt2 = 0
begin
exec ('' insert into #EmptyTables values(''''[''+@Tab+'']'''')'')
goto NextTab
end
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
set @NotInDB1 = 0
set @NotInDB2 = 0
set @NotEq = 0
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
truncate table #IndList1
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.sysindexes i, '+@db1+'.dbo.sysindexkeys k, '+@db1+'.dbo.syscolumns c
where i.id = o.id and o.name = @Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
while @@fetch_status = 0
begin
if @KeyAndStr <> ''''
begin
set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10)
set @KeyCommaStr = @KeyCommaStr + '', ''
end
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@UKey+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''
end
else if @xType = 189 -- timestamp (189)
begin
set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''
end
else if @xType = 98 -- SQL_variant
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
if @IndId <> @CurrIndId
begin
insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)'
set @SqlStrGetListOfKeys2 = '
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
truncate table #IndList2
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@db2+'.dbo.sysobjects o, '+@db2+'.dbo.sysindexes i, '+@db2+'.dbo.sysindexkeys k, '+@db2+'.dbo.syscolumns c
where i.id = o.id and o.name = @Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
while @@fetch_status = 0
begin
if @KeyAndStr <> ''''
begin
set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10)
set @KeyCommaStr = @KeyCommaStr + '', ''
end
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@UKey+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''
end
else if @xType = 189 -- timestamp (189)
begin
set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''
end
else if @xType = 98 -- SQL_variant
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
if @IndId <> @CurrIndId
begin
insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @KeyCommaStr = null

select @KeyCommaStr=i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr
where (i1.IndStatus & 2048)<> 0 and (i2.IndStatus & 2048)<>0

if @KeyCommaStr is null
set @KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)
set @KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr = @KeyCommaStr)
if @KeyCommaStr is null
set @KeyCommaStr = ''''
if @KeyAndStr is null
set @KeyAndStr = '''''
set @SqlStrGetListOfColumns = '
set @AndStr = ''''
set @StrInd = 1
declare Cols cursor local fast_forward for
select c.name, c.xtype, c.length from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.syscolumns c
where o.id = c.id and o.name = @Tab
and CHARINDEX(c.name, @KeyCommaStr) = 0
open Cols
fetch next from Cols into @Col, @xType, @len
while @@fetch_status = 0
begin
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 971428763405345098745 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 971428763405345098745 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 99999 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 99999 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@Col+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @Eq =
''CASE WHEN d1.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@Col+''],109) END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@Col+''],109) END ''
end
else if @xType = 34
begin
set @Eq = ''ISNULL(DATALENGTH(d1.[''+@Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@Col+'']),0) ''
end
else if @xType = 35 or @xType = 99 -- text (35),ntext (99)
begin
set @Eq = ''ISNULL(SUBSTRING(d1.[''+@Col+''],1,DATALENGTH(d1.[''+@Col+
''])),''''!#null$'''')=ISNULL(SUBSTRING(d2.[''+@Col+''],1,DATALENGTH(d2.[''+@Col+''])),''''!#null$'''') ''
end
else if @xType = 189
begin
if '+STR(@NoTimestamp)+' = 0
set @Eq = ''d1.[''+@Col+'']=d2.[''+@Col+''] ''
else
set @Eq = ''1=1''
end
else if @xType = 98 -- SQL_variant
begin
set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''
end
if @AndStr = ''''
set @AndStr = @AndStr + CHAR(10) + '' '' + @Eq
else
if len(@AndStr) + len('' and '' + @Eq)<8000
set @AndStr = @AndStr + '' and '' + CHAR(10) + '' '' + @Eq
else
begin
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr)
set @AndStr = '' and '' + @Eq
end
fetch next from Cols into @Col, @xType, @len
end
deallocate Cols '
set @SqlStrCompareUKeyTables = '
if @KeyAndStr <> ''''
begin
set @SelectStr = ''SELECT ''+ @KeyCommaStr+'' INTO ##NotInDb2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@KeyAndStr+'')''
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@db1+', but are not in db2:''+CHAR(10)+
REPLACE (@SelectStr, ''into ##NotInDB2'','''')
exec (@SelectStr)
if @@rowcount > 0
set @NotInDB2 = 1
set @SelectStr = ''SELECT ''+@KeyCommaStr+'' INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@KeyAndStr+'')''
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@db2+', but are not in '+@db1+':''+CHAR(10)+
REPLACE (@SelectStr, ''into ##NotInDB1'','''')
exec (@SelectStr)
if @@rowcount > 0
set @NotInDB1 = 1
-- if there are non-key columns
if @AndStr <> ''''
begin
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+ @KeyCommaStr+'' INTO ##NotEq FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+
'' INNER JOIN '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ON ''+CHAR(10)+@KeyAndStr+CHAR(10)+''WHERE not('')
-- Adding last string in temp table containing a comparing string to execute
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr+'')'')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotEq'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are different in non-key columns:'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)

if @@rowcount > 0
set @NotEq = 1
end
else
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''There are no non-key columns in the table''
truncate table ##CompareStr
if @NotInDB1 = 1 or @NotInDB2 = 1 or @NotEq = 1
begin
print CHAR(10)+''Data are different''
if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''These key values exist in '+@db1+', but do not exist in '+@db2+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''
exec (@SelectStr)
end
if @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''These key values exist in '+@db2+', but do not exist in '+@db1+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''
exec (@SelectStr)
end
if @NotEq = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''Row(s) with these key values contain differences in non-key columns: ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotEq''
exec (@SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'')
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')
drop table ##NotEq
end
else '
set @SqlStrCompareNonUKeyTables = '
begin
exec (''insert into #NoPKTables values(''''[''+@Tab+'']'''')'')
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+
'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 WHERE '')
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr+'')'')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB2'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@db1+', but are not in '+@db2+':'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)

if @@rowcount > 0
set @NotInDB2 = 1
delete from ##CompareStr where ind = 1
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+
'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 WHERE '')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB1'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@db2+', but are not in '+@db1+':'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)

if @@rowcount > 0
set @NotInDB1 = 1
truncate table ##CompareStr
if @NotInDB1 = 1 or @NotInDB2 = 1
begin
print CHAR(10)+''Data are different''
if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@db1+', but do not exist in '+@db2+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''
exec (@SelectStr)
end
if @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@db2+', but do not exist in '+@db1+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''
exec (@SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'')
end
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB1%'')
drop table ##NotInDB1
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB2%'')
drop table ##NotInDB2
NextTab:
fetch next from TabCur into @Tab, @d1User, @d2User
end
deallocate TabCur
'
exec (@SqlStrGetListOfKeys1+@SqlStrGetListOfKeys2+@SqlStrGetListOfColumns+
@SqlStrCompareUKeyTables+@SqlStrCompareNonUKeyTables)
print ' '
SET NOCOUNT OFF
if (select count(*) from #NoPKTables) > 0
begin
select name as 'Table(s) without Unique key:' from #NoPKTables
end
if (select count(*) from #DiffTables) > 0
begin
select name as 'Table(s) with the same name & structure, but different data:' from #DiffTables
end
else
print CHAR(10)+'No tables with the same name & structure, but different data'+CHAR(10)
if (select count(*) from #IdenticalTables) > 0
begin
select name as 'Table(s) with the same name & structure and identical data:' from #IdenticalTables
end
if (select count(*) from #EmptyTables) > 0
begin
select name as 'Table(s) with the same name & structure and empty in the both databases:' from #EmptyTables
end
drop table #TabToCheck
drop table ##CompareStr
drop table #DiffTables
drop table #IdenticalTables
drop table #EmptyTables
drop table #NoPKTables
drop table #IndList1
drop table #IndList2
return
Report Script

SQL Server : Finding DB objects


@name varchar(120),
@type varchar(5)
as
begin
declare @Sql varchar(500)
set @Sql = 'select * from sysobjects where name like ''%' + @name +'%'' and type = '''+@type+''' order by name'
print @sql
Exec (@sql)

SQL Server : List of Stored Procedure from Table


SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND (SC.Text LIKE '%UPDATE%' OR SC.Text LIKE '%INSERT%')
ORDER BY SO.Name



but a different variation can also be tried-

select
SPECIFIC_NAME
from
INFORMATION_SCHEMA.ROUTINES r
where
(r.ROUTINE_DEFINITION like '%UPDATE%' or r.ROUTINE_DEFINITION like '%INSERT%')
order by SPECIFIC_NAME

Profiler and trace

I have a question regarding profiler, What exactly are reads? and what is the correlation between cpu, reads and duration? is that correlation direct?


Events

I typically start by tracing the SQL:BatchCompleted event and the RPC:CompletedEvent event. These two events should capture performance for all your SQL batches and stored procedures. The SQL:BatchCompleted event is generated whenever a batch of SQL statements is completed. If you run code in SQL Server Management Studio (or Query Analyzer) it will generate SQL:BatchCompleted events. If you create a .NET SqlCommand object and give it a CommandType of "Text" that will also generate SQL:BatchCompleted events. If your SqlCommand object has a CommandType of "StoredProcedure" that will generate RPC:Completed events.



Data Columns

I normally capture the following data columns for the two events above:

1. EventClass. This identifies the event. It must be selected.
2. TextData. This is the text of the SQL batch or the name of the stored procedure. We'll see situations below where RPC:Completed events that have what looks like a SQL batch in the TextData.
ApplicationName. This is the name of the application that generated this event such as Query Analyzer or SQL Server Management Studio. This is something you can set in your applications through the connection string.
3. HostName. This is the computer that connected to SQL Server and issues the SQL statement.
4. LoginName. This identifies the account that the SQL statements were run under.
5. EndTime. This identifies when the event ended. StartTime could be used instead.
6. SPID. This is the SPID of the connection that ran the SQL statement. This is required.
7. CPU. This is the actual amount of CPU used in milliseconds (one thousandth of a second).
8. Duration. This is the actual duration of the batch. In SQL Server 2000 traces it is in milliseconds. In SQL Server 2005 this is reported in microseconds (one millionth of a second) however Profiler 2005 automatically translates it back into microseconds.
9. Reads. This is the number of logical disk reads in 8KB pages. For example if this number is 85 then SQL Server logically read 5,570,560 bytes of data (85 pages times 8192 bytes per page). Physical disk reads are typically much lower than logical reads as SQL Server typically caches data pages. There's no direct way to find the number of physical reads a query generates. I typically find a fairly strong correlation between logical and physical reads though. If I can reduce the logical reads usuallly the physical reads will fall also. These logical reads also take up memory. Either these data pages are already in memory or they are read into memory. A high logical reads is also a good proxy for the memory usage of a query.
10. Writes. This is the nubmer of logical disk writes in 8KB pages. Logical writes are typically much closer to physical writes. I usually don't look at this number as much.
Those events should give you a good start identifying slow queries or queries that use a lot of resources.



Cursors

You may see a pattern like this:

declare @P1 int
set @P1=180150001
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen
  @P1 output,
  N'select SomeColumn FROM TableName',
  @P2 output,
  @P3 output,
  @P4 output
select @P1, @P2, @P3, @P4
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exec sp_cursorfetch 180150001, 2, 1, 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exec sp_cursorclose 180150001
The three sections above will be three different events. The sp_cursorfetch event may be repeated any number of times. You'll find it will be repeated once per row returned by the query. This is a server-side cursor and it is the slowest way to query data out of SQL Server.

This occurs when the client issues an sp_cursoropen statement with a SQL query. This executes and is stored in a temporary table. For each row the client wants to return it issues an sp_cursorfetch. When it's done it issues an sp_cursorclose to close the cursor and delete the temporary table. This means that the client makes on full round trip to SQL Server for EACH row it wants to return. You'll find that each individual event profiled (the creates, fetches and closes) isn't that slow but combined they result in terrible performance. In SQL Server 7.0 this also resulted contention in tempdb as the database was locked while the temporary table was created.

I typically see these when a client has two active result sets open on a single connection. For example, a client might run a query to list all the books that match a search criteria. Then inside the loop that is returning that data it will run a second query to get some additional details about each book returned. That second query will run against the same connection as the outer query. That results in a server-side cursor. I think you can also generate these by specifying a server-side cursor in ODBC or OLEDB.

These typically have to be fixed on the client side by creating additional connections for each active query. If you have access to the source code they are fairly easy to fix. In ADO.NET 2.0 you can use the Multiple Active Resultset (MARS) option to allow this same situation without generating a server-side cursor.

In ClearTrace I pull the actual executed SQL statement out of the sp_cursoropen statement. I prefix it with "{CURSOR}" so it can be easily identified as a server-side cursor. You can use the keyword search to quickly find these.



Prepared SQL

I also see this pattern quite a bit -- especially with Access:

declare @P1 int
set @P1=1
exec sp_prepare
  @P1 output,
  N'@P1 int',
  N'SELECT CustName FROM Customers CustID = @P1', 1
select @P1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exec sp_execute 1, 125
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exec sp_execute 1, 126
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exec sp_execute 1, 127
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exec sp_unprepare 1
The sp_prepare statement is compiling the SQL statement (i.e. building a query plan) and assigning it handle of 1. Each sp_execute statement is calling that SQL statement and passing it the second value as the parameter @P1. The final statement is unpreparing statement nubmer 1. There are a number of variations on this including sp_prepexec and sp_prepexecrpc. I don't know if this is actually any faster than just executing the raw SQL statements. In ClearTrace I replace each sp_exectue statement with the actualy SQL statement it executes.

========================================================================

Profiler has a useful search functionality that helps you find particular piece of information you're looking for. To activate such a search, simply choose Edit --> Find within the Profiler. The "Search value" is the string that you are looking for. Data column lets you specify the particular data column you wish to find the search value in. If you don't specify the data column then all columns within the current trace will be searched. The Find Next and Find Previous buttons let you find the same piece of information again. The search functionality within the Profiler is great for finding execution of the same stored procedure or events caused by the same login. The limitation of this tool is that you cannot order or group data within the trace once it is collected.

If you wish to perform a more extensive analysis of the captured trace you should save its output as a SQL Server table. To do so, choose File --> Save As --> Trace Table within the Profiler. You will be asked to provide the server name where you wish to save the trace to and valid login credentials. Once you connect to the server you must also provide the database name and table name where trace should be saved. You can choose an existing table as the destination for the trace, so long as it has the same structure (data columns) as the trace. Keep in mind however, that saving a trace as a SQL Server table can be a resource and time intensive operation. The trace that contains millions of rows might take a while to save as a table. Therefore, avoid saving traces to production SQL Servers.


Once you have saved a trace as a table you can treat it as any other SQL Server table - you can build indexes, add and remove columns, build views on top of this table and so forth. The main advantage of saving a trace as a table is the extensive searching and sorting capabilities available with TSQL. For example, to return all events generated by SQL Server Enterprise Manager from the saved trace you could execute the following query:
1. SELECT * FROM replay_trace WHERE ApplicationName = 'MS SQLEM'
If you wish to see events in the order of their duration and CPU time taken, simply order the output by those columns:
2. SELECT * FROM replay_trace WHERE ApplicationName = 'MS SQLEM' ORDER BY duration, CPU
Here is a SELECT statement that will return the least, greatest and average execution time for each captured event, along with the total number of executions of that query. The output is ordered by the total number of times a particular statement was executed. This statement is useful for identifying the longest running queries; therefore it limits the output only to those events that have reported the duration of 1 or more milliseconds:
3.
SELECT MAX(duration) AS MaximumExecutiontime, MIN(duration) AS MinimumExecutiontime, AVG(duration) AS AverageExecutiontime, COUNT(*) AS NumberOfTimes, SUBSTRING(TextData, 1, 255) AS QueryText
FROM trace
/* eliminate those events that don't report duration or report 0 milliseconds */
WHERE duration > 0 AND duration IS NOT NULL GROUP BY SUBSTRING(TextData, 1, 255) ORDER BY 3 DESC
Notice that TextData column has a data type of TEXT and therefore cannot be used within GROUP BY statements unless you apply a SUBSTRING function first. That is why the statement above only brings back the first 255 characters of the text data column. Notice also that when you save the trace as a table, the event class column is automatically converted to the event number. If you wish to limit the query output by event class you must use event numbers. For example, the following query limits results by event number 41, which is SQL: StmtCompleted:
view sourceprint?
4. SELECT * FROM trace1 WHERE EventClass = 41 /* SQL: Stmt Completed */
ORDER BY duration DESC

Saturday, 9 March 2013

View or Configure the backup compression default Server Configuration Option


use sp_configure to configure the server instance to create compressed backups by default.


USE AdventureWorks2012;
GO
EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE ;
GO


By this, all further backups that you perform will be done with compression by default.



Then there is a very good backup scheme that I've developed.
Here it is-


DECLARE @dbName varchar(100);
DECLARE @backupPath varchar(100);
DECLARE @backupQuery varchar(500);

set @dbName = N'database_name'


EXECUTE master.dbo.xp_create_subdir N'\\your\path\to\the \folder\database_name'


set @backupPath = '\\your\path\to\the \folder\database_name\'


set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''


print @backupQuery


EXEC (@backupQuery)

Friday, 1 March 2013

Security Audit Db_DataReader


--Purpose: Find db_dbreader roles with permissions other than Select or Connect for each database
--Recommendations: Run this in a non-production environment first, the script uses dynamic SQL and an
--                 undocumented stored procedure

EXEC sp_MSforeachdb @command1='USE [?]

DECLARE @Roles varchar(200)
SET @Roles = ''db_datareader, Init_Role, Viewer''

DECLARE @sqlcmd1 nvarchar(500)
CREATE TABLE #temp_helprotect(Owner varchar(50), Object varchar(500),
Grantee varchar(50), Grantor varchar(50),
ProtectType varchar(50), Action varchar(50), RefColumn varchar(1000));

SET @sqlcmd1 = ''EXEC [?]..sp_helprotect'';

INSERT INTO #temp_helprotect EXECUTE(@sqlcmd1);

DECLARE @sqlcmd2 nvarchar(200);

CREATE TABLE #temp_rolemember(DbRole varchar(50), MemberName varchar(100), MemberSID nvarchar(1000));

SET @sqlcmd2 = ''EXEC [?]..sp_helprolemember'';

INSERT INTO #temp_rolemember EXECUTE(@sqlcmd2);

SELECT DbRole, MemberName
INTO #dbreaders
FROM #temp_rolemember
WHERE MemberName NOT IN(Select MemberName FROM #temp_rolemember WHERE RTRIM(LTRIM(DbRole)) NOT IN(''db_datareader'', ''Init_role'', ''Viewer''))

SELECT DISTINCT
A.MemberName, B.Grantee, B.Object, B.Grantor, B.ProtectType, B.Action, C.name
FROM #dbreaders A, #temp_helprotect B, sys.sysdatabases C, #temp_rolemember D
WHERE
A.MemberName = D.MemberName
AND A.MemberName LIKE ''%'' + B.Grantee + ''%''
AND Action Not In(''Select'', ''Connect'')
AND C.dbid = DB_ID()
ORDER BY A.MemberName, B.Grantee;

DROP TABLE #temp_rolemember;
DROP TABLE #dbreaders;
DROP TABLE #temp_helprotect;'

Killing User Connection(session) connected to the Database


DECLARE @SPID varchar(8000)
DECLARE @Conntions_Killed smallint
DECLARE @DBName varchar(100)

SET @SPID = ''
SET @Conntions_Killed = 0;
SET @DBName = 'test' -- Pass the Database Name.

SELECT @SPID=coalesce(@spid,',' )+'KILL '+convert(varchar, spid)+ '; '
FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);

Print @SPID;

IF LEN(@SPID) > 0
BEGIN
EXEC(@SPID);

SELECT @Conntions_Killed = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

Kill The Sessions


Declare @DatabaseName Varchar(100)
Set @DatabaseName='TEST_DB' -- Put the database name here


----- Show Data
select 'BEFORE -->',spid,
HOSTNAME,
Loginame,
DB_NAME(dbid) AS Database_Name,
USER_NAME(uid) AS UserName,
Status,
[program_name]
CMD from master.sys.sysprocesses
Where dbid=DB_ID(@DatabaseName)
order by Status,DB_NAME(dbid) desc,Hostname,spid,uid
------

Declare @SessionInfo Table (id int identity(1,1),Session_ID varchar(10))
Declare @count int
Declare @max int
declare @SqlString nvarchar(50)
select @count=1,@max=0

--insert into @SessionInfo(Session_ID)
select spid
from master.sys.sysprocesses
Where dbid=DB_ID(@DatabaseName)
and Status<>'runnable'
return

select @max=MAX(id) from @SessionInfo

while (@count<=@max)
BEGIN
    set @SqlString=''
    select @SqlString='KILL '+Session_ID from @SessionInfo where id=@count
    EXEC sp_executesql @SqlString
Set @count=@count+1;
END

select 'AFTER -->',spid,
HOSTNAME,
Loginame,
DB_NAME(dbid) AS Database_Name,
USER_NAME(uid) AS UserName,
Status,
[program_name]
CMD from master.sys.sysprocesses
Where DB_NAME(dbid)=@DatabaseName
order by Status,DB_NAME(dbid) desc,Hostname,spid,uid

Part 1: Restore single database


It is a good practice to have all the scripts you usually use in one single container. The best place is a small database, DBAdmin, which could be deployed in every SQL server instances in the company.

This is my effort to make a "standard" toolbox for sql server dba and share it with the community, in the hope that it will be more contributions from others.

This script is for SQL2008 and SQL2008R2. For other versions modifications will be needed since the RESTORE HEADERONLY and FILELISTONLY have different outputs.

EDIT: Changed from ALTER PROC to CREATE PROC.

USE [DBAdmin]
GO
/****** Object: StoredProcedure [dbo].[admsp_RestoreDatabase]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****************************************************
* Last Changed 2011-06-10
*
* Description:    
*    Restore database and log backup file from all backup files in a directory
*    to the latest transaction backed up.
*    
* - Error-resilient against non-backup files within the directory
* - Physical files are renamed
* - Placement of files are configurable. All data files are placed together,
* and all log files are placed together
*
*
* Parameters:
* @source_dir - directory where backup files are stored
* @dbname - Original database name
* @datafile_dir - Directories where physical data files are to be placed
* @logfile_dir - Directories where physical log files are to be placed
* @fulltext_root - the Freetext search related files will be placed under directory <@fulltext_root>\<@dbname>_FT\
*        This is for backwardcompatibility.
* @dbname_new - the restored database name (default = @dbname)
* @RunMode
* - Restore: do the restore, it will stop if database with the same name exists.
* - ScriptOnly: generate scripts only (default)
*
*  Usage sample:
*  To generate the restore scripts
*  EXEC admsp_RestoreDatabase '\\fileshare\sqlbackup$\server1', 'database1', 'D:\SQLData', 'L:\SQLLog'
****************************************************/
CREATE PROC [dbo].[admsp_RestoreDatabase]
    @source_dir varchar(255),
  @dbname sysname,
    @datafile_dir varchar(255),
    @logfile_dir varchar(255),
    @fulltext_root varchar(255) = NULL,
    @dbname_new sysname = null,
    @RunMode varchar(10) = 'ScriptOnly',
    @InfoLevel varchar(10) = 'VERBOSE'

AS
SET NOCOUNT ON

-- Set default
IF @fulltext_root IS NULL SET @fulltext_root = @datafile_dir

----------------------------------------------------------------------
-- Constants
----------------------------------------------------------------------
DECLARE @RESTORE varchar(10)
SET @RESTORE = 'RESTORE'

DECLARE @VERBOSE varchar(10), @SILENT varchar(10)
SET @VERBOSE = 'VERBOSE'
SET @SILENT = 'SILENT'

DECLARE @NEWLINE varchar(1)
SET @NEWLINE = ' '

DECLARE @LOGDETAIL varchar(10), @LOGINFO varchar(10)
SET @LOGDETAIL = 'DETAIL'
SET @LOGINFO = 'INFO'

---------------------------------------------------------------------
-- Normalize directory names
---------------------------------------------------------------------
IF RIGHT (@source_dir, 1) != '\' SET @source_dir += '\'
IF RIGHT (@datafile_dir, 1) != '\' SET @datafile_dir += '\'
IF RIGHT (@logfile_dir, 1) != '\' SET @logfile_dir += '\'
IF RIGHT (@fulltext_root, 1) != '\' SET @fulltext_root += '\'

DECLARE
    @cmd_dir varchar(255),
    @cmd_restore varchar(2000),
    @diskfile nvarchar(128),
    @new_FT_Dir varchar(255),

    @LSN numeric(25,0),
    @RecoveryModel nvarchar(60),
    @BackupStartDate DateTime,
    @LogicalDataFile sysname,
    @LogicalLogFile sysname

-- Set run mode --
if @RunMode IS NULL OR @RunMode not in ('Restore', 'ScriptOnly')
    SET @RunMode = 'ScriptOnly'

-- Print message
IF @InfoLevel != @SILENT
    PRINT '============================== RunMode ' + @RunMode + ' =============================='

-- check and set @dbname_new
if @dbname_new is null or @dbname_new = ''
    set @dbname_new = @dbname

set @new_FT_Dir = @fulltext_root + @dbname + '_FT\'

/***********************************************************
* If Runmode is normal and database exists stop running
*
***********************************************************/
if @RunMode = @RESTORE AND Exists (select * from master.dbo.sysdatabases where name = @dbname_new)
begin
    PRINT 'Database ' + @dbname_new + ' exists.' + char(10) + 'Change @dbname_new to a new name.'
    RETURN 1
end

/******************************************************************************
* Create or initialize temp files

*******************************************************************************/
IF object_id('tempdb..#tmp_diskfile') IS NOT NULL
    Truncate TABLE #tmp_diskfile
ELSE
    create table #tmp_diskfile (bkfile varchar(255), depth int, [file] int)

IF object_id('tempdb..#tmp_dbfile') IS NOT NULL
 Truncate TABLE #tmp_dbfile
ELSE
    create table #tmp_dbfile (
        lName nvarchar(128),
        phName nvarchar(260),
        [Type] char(1),
        fGrpName nvarchar(128),
        [Size] numeric(25,0),
        [MaxSize] numeric(25,0),
        fileId BigInt,
        CreateLSN numeric(25,0),
        DropLSN numeric(25,0),
        UniqueId UniqueIdentifier,
        ReadOnlyLSN numeric(25,0),
        ReadWriteLSN numeric(25,0),
        BackupSizeInBytes bigInt,
        SourceBlockSize int,
        FileGroupId int,
        LogGroupGuid UniqueIdentifier,
        DiffBaseLSN numeric(25,0),
        DiffBaseGuid UniqueIdentifier,
        IsReadOnly bit,
        IsPresent bit,
        -- only for 2008
        TDEThumbprint Decimal
    )

IF object_id('tempdb..#tmp_header') IS NOT NULL
    Truncate TABLE #tmp_header
ELSE
    create table #tmp_header
    (
        DiskFileName nvarchar(128) NULL,
        BackupName nvarchar(128),
        BackupDescription nvarchar(255),
        BackupType smallint,
        ExpirationDate datetime,
        Compressed tinyint,
        Position smallint,
        DeviceType tinyint,
        UserName nvarchar(128),
        ServerName nvarchar(128),
        DatabaseName nvarchar(128),
        DatabaseVersion int,
        DatabaseCreationDate datetime,
        BackupSize numeric(20,0),
        FirstLSN numeric(25,0),
        LastLSN numeric(25,0),
        CheckpointLSN numeric(25,0),
        DatabaseBackupLSN numeric(25,0),
        BackupStartDate datetime,
        BackupFinishDate datetime,
        SortOrder smallint,
        CodePage smallint,
        UnicodeLocaleId int,
        UnicodeComparisonStyle int,
        CompatibilityLevel tinyint,
        SoftwareVendorId int,
        SoftwareVersionMajor int,
        SoftwareVersionMinor int,
        SoftwareVersionBuild int,
        MachineName nvarchar(128),
        Flags int,
        BindingID uniqueidentifier,
        RecoveryForkID uniqueidentifier,
        Collation nvarchar(128),
        FamilyGUID uniqueidentifier NULL,
        HasBulkLoggedData bit NULL,
        IsSnapshot bit NULL,
        IsReadOnly bit NULL,
        IsSingleUser bit NULL,
        HasBackupChecksums bit NULL,
        IsDamaged bit NULL,
        BeginsLogChain bit NULL,
        HasIncompleteMetaData bit NULL,
        IsForceOffline bit NULL,
        IsCopyOnly bit NULL,
        FirstRecoveryForkID uniqueidentifier NULL,
        ForkPointLSN numeric(25,0) NULL,
        RecoveryModel nvarchar(60) NULL,
        DifferentialBaseLSN numeric(25,0) NULL,
        DifferentialBaseGUID uniqueidentifier NULL,
        BackupTypeDescription nvarchar(60) NULL,
        BackupSetGUID uniqueidentifier NULL,
        -- Only for 2008
        CompressedBackupSize numeric(20,0)
)

/*******************************************************************************
* Enumerate disk files
*
********************************************************************************/
insert #tmp_diskfile
 exec master.sys.xp_dirtree @source_dir, 1, 1

/*******************************************************************************
* Get backup set headers for all files
*
********************************************************************************/
declare cur_diskfile CURSOR FOR
    select bkfile from #tmp_diskfile

open cur_diskfile

fetch next from cur_diskfile into @diskfile
while @@fetch_status = 0
BEGIN
    if @diskfile is not null
    BEGIN TRY
        SET @cmd_restore = 'restore headeronly from disk=''' + @source_dir + @diskfile + ''''
        insert #tmp_header (BackupName, BackupDescription, BackupType, ExpirationDate,
            Compressed, Position, DeviceType, UserName, ServerName, DatabaseName,    
            DatabaseVersion, DatabaseCreationDate, BackupSize, FirstLSN, LastLSN,
            CheckpointLSN, DatabaseBackupLSN, BackupStartDate, BackupFinishDate, SortOrder,
            CodePage, UnicodeLocaleId, UnicodeComparisonStyle, CompatibilityLevel,
            SoftwareVendorId, SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild,
            MachineName, Flags, BindingID, RecoveryForkID, Collation, FamilyGUID,
            HasBulkLoggedData, IsSnapshot, IsReadOnly, IsSingleUser, HasBackupChecksums, IsDamaged,
            BeginsLogChain, HasIncompleteMetaData, IsForceOffline, IsCopyOnly, FirstRecoveryForkID,
            ForkPointLSN, RecoveryModel, DifferentialBaseLSN, DifferentialBaseGUID,
            BackupTypeDescription, BackupSetGUID, CompressedBackupSize)
            exec (@cmd_restore)
    
            update #tmp_header set diskfilename = @diskfile where diskfilename IS NULL
    END TRY
    BEGIN CATCH

    END CATCH

    
    fetch next from cur_diskfile into @diskfile
END
close cur_diskfile
deallocate cur_diskfile

/*********************************************************************************
* Get the latest full backup
*
*********************************************************************************/
select TOP 1 @diskfile = DiskFileName, @LSN = LastLSN, @RecoveryModel = RecoveryModel, @BackupStartDate = BackupStartDate
    from #tmp_header
    where backuptype = 1 and DataBaseName = @dbname
    ORDER BY FirstLSN DESC, BackupStartDate DESC

if @@rowcount = 0
BEGIN
    RAISERROR ('Database %s not find in any of the full backup file(s)', 16, 1, @dbname)
    return 2
END

/************************************************************************************
* Compose the Restore Database statement
*
*************************************************************************************/
SET @cmd_restore = 'RESTORE FileListOnly from disk = ''' + @source_dir + @diskfile + ''''

INSERT #tmp_dbfile (
    lName, phName, [Type], fGrpName,
    [Size], [MaxSize],
    fileId, CreateLSN, DropLSN, UniqueId,
    ReadOnlyLSN,
    ReadWriteLSN,
    BackupSizeInBytes,
    SourceBlockSize,
    FileGroupId,
    LogGroupGuid,
    DiffBaseLSN,
    DiffBaseGuid,
    IsReadOnly,
    IsPresent,
    TDEThumbprint
    )
EXEC ( @cmd_restore )

-- TODO check the recovery mode of the database, and determine if recovery needed in this step
IF @RecoveryModel = 'SIMPLE'
    SET @cmd_restore = 'RESTORE DATABASE ' + @dbname_new + '
    FROM disk = ''' + @source_dir + @diskfile + ''' WITH RECOVERY '
ELSE
    SET @cmd_restore = 'RESTORE DATABASE ' + @dbname_new + '
    FROM disk = ''' + @source_dir + @diskfile + ''' WITH NORECOVERY '

DECLARE cur_dbfile CURSOR FOR
    select lName, phName, [Type] from #tmp_dbfile
    
declare @lName nvarchar(128), @phName nvarchar(260), @Type char(1)
declare @db_filenr int, @log_filenr int, @ext char(4)
SET @db_filenr = 0
SET @log_filenr = 0

OPEN cur_dbfile
FETCH NEXT FROM cur_dbfile INTO @lName, @phName, @Type
WHILE @@Fetch_Status = 0
BEGIN
    if @Type = 'D'
    BEGIN
        SET @db_filenr = @db_filenr + 1

        if @db_filenr = 1
            set @ext = '.MDF'
        else
            set @ext = '.NDF'

        ---- Append ----
        SET @cmd_restore = @cmd_restore + ',
    MOVE ''' + @lName + ''' TO ''' + @datafile_dir + @dbname_new + '_Data_' + CAST( @db_filenr as varchar(2)) + @ext + ''''
    
    ----------------
    END
    else if @Type = 'L'
    BEGIN
        SET @log_filenr = @log_filenr + 1
    ---- Append ----
        SET @cmd_restore = @cmd_restore + ',
    MOVE ''' + @lName + ''' TO ''' + @logfile_dir + @dbname_new + '_Log_' + CAST( @log_filenr as varchar(2)) + '.LDF'''
    ----------------
    END
    else if @Type = 'F'
    BEGIN
    ---- Append ----
        SET @cmd_restore = @cmd_restore + ',
    MOVE ''' + @lName + ''' TO ''' + @new_FT_Dir + ''''
    ----------------
    END

    FETCH NEXT FROM cur_dbfile INTO @lName, @phName, @Type
END

CLOSE cur_dbfile
DEALLOCATE cur_dbfile

IF @InfoLevel != @SILENT
    SET @cmd_restore += ', STATS = 10'

-- SET NOCOUNT OFF

PRINT @cmd_restore

IF @RunMode = @RESTORE
BEGIN
    if @InfoLevel != @SILENT print 'Now restoring database from ' + @source_dir + @diskfile + '...'
    EXEC ( @cmd_restore )
END

--- TODO restore differential backup

/*****************************************************************************
* Restore log backup
*
******************************************************************************/
IF @RecoveryModel IS NULL OR @RecoveryModel != 'SIMPLE'
BEGIN

SELECT TOP 1 @diskfile = DiskFileName, @LSN = LastLSN, @RecoveryModel = RecoveryModel, @BackupStartDate = BackupStartDate
    from #tmp_header
    where backuptype = 2 and DataBaseName = @dbname
 AND FirstLSN <= @LSN AND LastLSN >= @LSN AND BackupStartDate > @BackupStartDate
    ORDER BY BackupStartDate ASC

WHILE @@RowCount = 1
BEGIN
    SET @cmd_restore = 'RESTORE LOG ' + @dbname_new + '
    FROM disk = ''' + @source_dir + @diskfile + ''' WITH NORECOVERY'

    if @InfoLevel != @SILENT SET @cmd_restore += ', STATS=10'

    IF @RunMode = @RESTORE BEGIN
        if @InfoLevel != @SILENT print 'Now restoring log from ' + @source_dir + @diskfile + '...'
        EXEC ( @cmd_restore )
    End
    Else BEGIN
        print @NEWLINE
        PRINT @cmd_restore
        print @NEWLINE        
    END

    SELECT TOP 1 @diskfile = DiskFileName, @LSN = LastLSN, @RecoveryModel = RecoveryModel, @BackupStartDate = BackupStartDate
        from #tmp_header
        where backuptype = 2 and DataBaseName = @dbname
         AND FirstLSN <= @LSN AND LastLSN >= @LSN AND BackupStartDate > @BackupStartDate
        ORDER BY BackupStartDate ASC
END

-- Recover the database
SET @cmd_restore = 'RESTORE DATABASE ' + @dbname_new + ' WITH RECOVERY '
if @RunMode = @RESTORE
BEGIN
    if @InfoLevel != @SILENT print 'Now recover database ...'
    EXEC (@cmd_restore)
END
ELSE
BEGIN
    print @NEWLINE
    PRINT @cmd_restore
    print @NEWLINE
END

END

drop table #tmp_diskfile
drop table #tmp_dbfile
drop table #tmp_header

IF @InfoLevel != @SILENT
    print '======================================= Finished ==============================================='




















Part 2: Verify all databases in the enterprise




It is a DBA responsibility to verify that database backups can be successfully restored but many DBAs (the majority ?) fail to do that. It is understandable that in a enterprise environment with hundreds of databases, manually restoring every one every day or week is impossible. But from now on - the moment that you get this set of scripts, there is no excuse any more for not regularly restoring and dbcc checking your database backup files. You can of course just ignore it at your own risk.

It is easy to set up and configure the verification station. For this you need a dedicated SQL server with highest SQL version in the enterprise. This version of script are for SQL Server 2008R2. The server should have enough disk space to restore the largest database. The service account of the database engine must have read access to all the backup files. I hope you have all the backups in a central network share, not in every SQL servers.

Objects used:
admsp_VerifyBackups: main procedure for restore and verification
RestoreConfig: configuration table
_admsp_RestoreDatabase: restore procedure
_admsp_LogVerificationResult: logging procedure, writing to BackupVerificationResult
_admsp_InventoryBackupFiles: logging procedure, writing to BackupFiles
BackupVerificationResult: table for job result
BackupFiles: table containing information of all backup files
RestoreFileLisOnlytOutput: User defined table type
RestoreHeaderOnlyOutput: User defined table type
BackupInfo: User defined table type

( The DBAdmin name convention: all the stored procedures, except one which is "Help", are prefixed with admsp_ or _admsp_. SPs prefixed with _admsp_ are internal and should be called only by other SPs. )



Deployment and configuration:

1. Create DBAdmin database if it does not exist
2. Run the script
3. Configure default restore setting (default setting has level 0: lvl=0)

Sample:
INSERT INTO [RestoreConfig] ([Dist_Datafile_Dir], [Dist_LogFile_Dir], [Dist_FullText_Dir],[Enable],[lvl])
VALUES ('D:\SQLData', 'L:\SQLLog', 'D:\SQLData', 1, 0)

4. Configure each database instance (instance setting has level 1: lvl=1)

Sample:
INSERT INTO [RestoreConfig] ([InstanceName], [SourceRoot], [Enable], [lvl])
VALUES ('SQLServer1', \\filesahre\sqlbackup$\SQLServer1', 1, 1)

The solution restore all databases under SourceRoot or one level below it.

5. Schedule the agent job executing admsp_VerifyBackups.



The results of job is logged in two tables: BackupVerificationResult and BackupFiles. Right now yo have to check the tables manually to find anything wrong with the backups. The analysis and alert part will be added later. However it will not be the next one. In the next part we will look at backup.


/****** Object: UserDefinedTableType [dbo].[RestoreHeaderOnlyOutput] ******/

CREATE TYPE [dbo].[RestoreHeaderOnlyOutput] AS TABLE(

    [BackupName] [nvarchar](128) NULL,

    [BackupDescription] [nvarchar](255) NULL,

    [BackupType] [smallint] NULL,

    [ExpirationDate] [datetime] NULL,

    [Compressed] [tinyint] NULL,

    [Position] [smallint] NULL,

    [DeviceType] [tinyint] NULL,

    [UserName] [nvarchar](128) NULL,

    [ServerName] [nvarchar](128) NULL,

    [DatabaseName] [nvarchar](128) NULL,

    [DatabaseVersion] [int] NULL,

    [DatabaseCreationDate] [datetime] NULL,

    [BackupSize] [numeric](20, 0) NULL,

    [FirstLSN] [numeric](25, 0) NULL,

    [LastLSN] [numeric](25, 0) NULL,

    [CheckpointLSN] [numeric](25, 0) NULL,

    [DatabaseBackupLSN] [numeric](25, 0) NULL,

    [BackupStartDate] [datetime] NULL,

    [BackupFinishDate] [datetime] NULL,

    [SortOrder] [smallint] NULL,

    [CodePage] [smallint] NULL,

    [UnicodeLocaleId] [int] NULL,

    [UnicodeComparisonStyle] [int] NULL,

    [CompatibilityLevel] [tinyint] NULL,

    [SoftwareVendorId] [int] NULL,

    [SoftwareVersionMajor] [int] NULL,

    [SoftwareVersionMinor] [int] NULL,

    [SoftwareVersionBuild] [int] NULL,

    [MachineName] [nvarchar](128) NULL,

    [Flags] [int] NULL,

    [BindingID] [uniqueidentifier] NULL,

    [RecoveryForkID] [uniqueidentifier] NULL,

    [Collation] [nvarchar](128) NULL,

    [FamilyGUID] [uniqueidentifier] NULL,

    [HasBulkLoggedData] [bit] NULL,

    [IsSnapshot] [bit] NULL,

    [IsReadOnly] [bit] NULL,

    [IsSingleUser] [bit] NULL,

    [HasBackupChecksums] [bit] NULL,

    [IsDamaged] [bit] NULL,

    [BeginsLogChain] [bit] NULL,

    [HasIncompleteMetaData] [bit] NULL,

    [IsForceOffline] [bit] NULL,

    [IsCopyOnly] [bit] NULL,

    [FirstRecoveryForkID] [uniqueidentifier] NULL,

    [ForkPointLSN] [numeric](25, 0) NULL,

    [RecoveryModel] [nvarchar](60) NULL,

    [DifferentialBaseLSN] [numeric](25, 0) NULL,

    [DifferentialBaseGUID] [uniqueidentifier] NULL,

    [BackupTypeDescription] [nvarchar](60) NULL,

    [BackupSetGUID] [uniqueidentifier] NULL,

    [CompressedBackupSize] [numeric](20, 0) NULL

)

GO



/****** Object: UserDefinedTableType [dbo].[RestoreFileLisOnlytOutput] ******/

CREATE TYPE [dbo].[RestoreFileLisOnlytOutput] AS TABLE(

    [lName] [nvarchar](128) NULL,

    [phName] [nvarchar](260) NULL,

    [Type] [char](1) NULL,

    [fGrpName] [nvarchar](128) NULL,

    [Size] [numeric](25, 0) NULL,

    [MaxSize] [numeric](25, 0) NULL,

    [fileId] [bigint] NULL,

    [CreateLSN] [numeric](25, 0) NULL,

    [DropLSN] [numeric](25, 0) NULL,

    [UniqueId] [uniqueidentifier] NULL,

    [ReadOnlyLSN] [numeric](25, 0) NULL,

    [ReadWriteLSN] [numeric](25, 0) NULL,

    [BackupSizeInBytes] [bigint] NULL,

    [SourceBlockSize] [int] NULL,

    [FileGroupId] [int] NULL,

    [LogGroupGuid] [uniqueidentifier] NULL,

    [DiffBaseLSN] [numeric](25, 0) NULL,

    [DiffBaseGuid] [uniqueidentifier] NULL,

    [IsReadOnly] [bit] NULL,

    [IsPresent] [bit] NULL,

    [TDEThumbprint] [decimal](18, 0) NULL

)

GO



/****** Object: Table [dbo].[RestoreConfig] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[RestoreConfig](

    [ConfigGuid] [uniqueidentifier] NOT NULL,

    [InstanceName] [varchar](50) NULL,

    [SourceRoot] [varchar](255) NULL,

    [DatabaseName] [varchar](50) NULL,

    [Dist_Datafile_Dir] [varchar](255) NULL,

    [Dist_LogFile_Dir] [varchar](255) NULL,

    [Dist_FullText_Dir] [varchar](255) NULL,

    [DBExclude] [varchar](2000) NULL,

    [Enable] [bit] NOT NULL,

    [lvl] [tinyint] NOT NULL,

CONSTRAINT [PK_RestoreConfig] PRIMARY KEY CLUSTERED

(

    [ConfigGuid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [UIX_RestoreConfig] UNIQUE NONCLUSTERED

(

    [InstanceName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[RestoreConfig] ADD CONSTRAINT [DF_RestoreConfig_ConfigGuid] DEFAULT (newid()) FOR [ConfigGuid]

GO

ALTER TABLE [dbo].[RestoreConfig] ADD CONSTRAINT [DF_RestoreConfig_DatabaseName] DEFAULT ('') FOR [DatabaseName]

GO

ALTER TABLE [dbo].[RestoreConfig] ADD CONSTRAINT [DF_RestoreConfig_Active] DEFAULT ((1)) FOR [Enable]

GO

ALTER TABLE [dbo].[RestoreConfig] ADD CONSTRAINT [DF_RestoreConfig_Level] DEFAULT ((1)) FOR [lvl]

GO



/****** Object: Table [dbo].[BackupVerificationResult]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BackupVerificationResult](

    [RowId] [bigint] IDENTITY(1,1) NOT NULL,

    [BatchStartTime] [datetime] NOT NULL,

    [InstanceName] [varchar](128) NULL,

    [FolderName] [varchar](128) NULL,

    [DatabaseName] [varchar](128) NULL,

    [Description] [varchar](500) NOT NULL,

    [Result] [int] NOT NULL,

    [Info] [varchar](200) NULL,

    [Message] [varchar](max) NULL,

    [LogDateTime] [datetime] NOT NULL,

CONSTRAINT [PK_VerifyResult] PRIMARY KEY CLUSTERED

(

    [RowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO



/****** Object: UserDefinedTableType [dbo].[BackupInfo] ******/

CREATE TYPE [dbo].[BackupInfo] AS TABLE(

    [Folder] [varchar](128) NULL,

    [DiskFileName] [varchar](500) NULL,

    [BackupName] [nvarchar](128) NULL,

    [BackupDescription] [nvarchar](255) NULL,

    [BackupType] [smallint] NULL,

    [ExpirationDate] [datetime] NULL,

    [Compressed] [tinyint] NULL,

    [Position] [smallint] NULL,

    [DeviceType] [tinyint] NULL,

    [UserName] [nvarchar](128) NULL,

    [ServerName] [nvarchar](128) NULL,

    [DatabaseName] [nvarchar](128) NULL,

    [DatabaseVersion] [int] NULL,

    [DatabaseCreationDate] [datetime] NULL,

    [BackupSize] [numeric](20, 0) NULL,

    [FirstLSN] [numeric](25, 0) NULL,

    [LastLSN] [numeric](25, 0) NULL,

    [CheckpointLSN] [numeric](25, 0) NULL,

    [DatabaseBackupLSN] [numeric](25, 0) NULL,

    [BackupStartDate] [datetime] NULL,

    [BackupFinishDate] [datetime] NULL,

    [SortOrder] [smallint] NULL,

    [CodePage] [smallint] NULL,

    [UnicodeLocaleId] [int] NULL,

    [UnicodeComparisonStyle] [int] NULL,

    [CompatibilityLevel] [tinyint] NULL,

    [SoftwareVendorId] [int] NULL,

    [SoftwareVersionMajor] [int] NULL,

    [SoftwareVersionMinor] [int] NULL,

    [SoftwareVersionBuild] [int] NULL,

    [MachineName] [nvarchar](128) NULL,

    [Flags] [int] NULL,

    [BindingID] [uniqueidentifier] NULL,

    [RecoveryForkID] [uniqueidentifier] NULL,

    [Collation] [nvarchar](128) NULL,

    [FamilyGUID] [uniqueidentifier] NULL,

    [HasBulkLoggedData] [bit] NULL,

    [IsSnapshot] [bit] NULL,

    [IsReadOnly] [bit] NULL,

    [IsSingleUser] [bit] NULL,

    [HasBackupChecksums] [bit] NULL,

    [IsDamaged] [bit] NULL,

    [BeginsLogChain] [bit] NULL,

    [HasIncompleteMetaData] [bit] NULL,

    [IsForceOffline] [bit] NULL,

    [IsCopyOnly] [bit] NULL,

    [FirstRecoveryForkID] [uniqueidentifier] NULL,

    [ForkPointLSN] [numeric](25, 0) NULL,

    [RecoveryModel] [nvarchar](60) NULL,

    [DifferentialBaseLSN] [numeric](25, 0) NULL,

    [DifferentialBaseGUID] [uniqueidentifier] NULL,

    [BackupTypeDescription] [nvarchar](60) NULL,

    [BackupSetGUID] [uniqueidentifier] NULL,

    [CompressedBackupSize] [numeric](20, 0) NULL

)

GO



/****** Object: Table [dbo].[BackupFiles] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BackupFiles](

    [RowId] [bigint] IDENTITY(1,1) NOT NULL,

    [DiskFileName] [varchar](500) NOT NULL,

    [BackupName] [nvarchar](128) NULL,

    [BackupDescription] [nvarchar](255) NULL,

    [BackupType] [smallint] NULL,

    [ExpirationDate] [datetime] NULL,

    [Compressed] [tinyint] NULL,

    [Position] [smallint] NULL,

    [DeviceType] [tinyint] NULL,

    [UserName] [nvarchar](128) NULL,

    [ServerName] [nvarchar](128) NULL,

    [DatabaseName] [nvarchar](128) NULL,

    [DatabaseVersion] [int] NULL,

    [DatabaseCreationDate] [datetime] NULL,

    [BackupSize] [numeric](20, 0) NULL,

    [FirstLSN] [numeric](25, 0) NULL,

    [LastLSN] [numeric](25, 0) NULL,

    [CheckpointLSN] [numeric](25, 0) NULL,

    [DatabaseBackupLSN] [numeric](25, 0) NULL,

    [BackupStartDate] [datetime] NULL,

    [BackupFinishDate] [datetime] NULL,

    [SortOrder] [smallint] NULL,

    [CodePage] [smallint] NULL,

    [UnicodeLocaleId] [int] NULL,

    [UnicodeComparisonStyle] [int] NULL,

    [CompatibilityLevel] [tinyint] NULL,

    [SoftwareVendorId] [int] NULL,

    [SoftwareVersionMajor] [int] NULL,

    [SoftwareVersionMinor] [int] NULL,

    [SoftwareVersionBuild] [int] NULL,

    [MachineName] [nvarchar](128) NULL,

    [Flags] [int] NULL,

    [BindingID] [uniqueidentifier] NULL,

    [RecoveryForkID] [uniqueidentifier] NULL,

    [Collation] [nvarchar](128) NULL,

    [FamilyGUID] [uniqueidentifier] NULL,

    [HasBulkLoggedData] [bit] NULL,

    [IsSnapshot] [bit] NULL,

    [IsReadOnly] [bit] NULL,

    [IsSingleUser] [bit] NULL,

    [HasBackupChecksums] [bit] NULL,

    [IsDamaged] [bit] NULL,

    [BeginsLogChain] [bit] NULL,

    [HasIncompleteMetaData] [bit] NULL,

    [IsForceOffline] [bit] NULL,

    [IsCopyOnly] [bit] NULL,

    [FirstRecoveryForkID] [uniqueidentifier] NULL,

    [ForkPointLSN] [numeric](25, 0) NULL,

    [RecoveryModel] [nvarchar](60) NULL,

    [DifferentialBaseLSN] [numeric](25, 0) NULL,

    [DifferentialBaseGUID] [uniqueidentifier] NULL,

    [BackupTypeDescription] [nvarchar](60) NULL,

    [BackupSetGUID] [uniqueidentifier] NULL,

    [CompressedBackupSize] [numeric](20, 0) NULL,

    [FirstCheckTime] [datetime] NOT NULL,

    [LastCheckTime] [datetime] NOT NULL,

    [IsDeleted] [bit] NOT NULL,

    [LastBatchStartTime] [datetime] NULL,

CONSTRAINT [PK_BackupFiles] PRIMARY KEY CLUSTERED

(

    [RowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[BackupFiles] ADD CONSTRAINT [DF_BackupFiles_FirstCheckTime] DEFAULT (getdate()) FOR [FirstCheckTime]

GO

ALTER TABLE [dbo].[BackupFiles] ADD CONSTRAINT [DF_BackupFiles_LastCheckTime] DEFAULT (getdate()) FOR [LastCheckTime]

GO

ALTER TABLE [dbo].[BackupFiles] ADD CONSTRAINT [DF_BackupFiles_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]

GO

CREATE NONCLUSTERED INDEX [IX_BackupFiles_FileName] ON [dbo].[BackupFiles]

(

    [DiskFileName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO





/****** Object: StoredProcedure [dbo].[_admsp_RestoreDatabase]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/********************************************************************************

* Restore a database from a list of files contained in @backupinfo parameter

*

* Internal procedure, called by dbo.admsp_VerifyBackups only

*********************************************************************************/

CREATE PROC [dbo].[_admsp_RestoreDatabase]

    @backupinfo dbo.backupinfo READONLY,

    @datafile_dir VARCHAR(500),

    @logfile_dir VARCHAR(500),

    @fulltext_root VARCHAR(500),

    @NewDBName VARCHAR(128) OUT,

    @FullbackupTime DateTime OUT,

    @LastLogBackupTime DateTime OUT

    

AS

BEGIN

    SET NOCOUNT ON

    

    -- Normalize the paths

    IF RIGHT (@datafile_dir, 1) != '\' SET @datafile_dir += '\'

    IF RIGHT (@logfile_dir, 1) != '\' SET @logfile_dir += '\'

    IF RIGHT (@fulltext_root, 1) != '\' SET @fulltext_root += '\'

    

    DECLARE

        @msg_err_no_fullback VARCHAR(200) = 'Fullbackup for database %s not find in any of the backup files',

        @cmd_filelist_fmt VARCHAR(2000) = 'RESTORE FILELISTONLY FROM DISK=''{bkfile}''',

        @cmd_restore_db_fmt VARCHAR(2000) = 'RESTORE DATABASE [{database}] FROM DISK=''{bkfile}'' WITH FILE={position}, NORECOVERY, REPLACE',

        @cmd_movedatafile_fmt VARCHAR (2000) = ', MOVE ''{lname}'' TO ''{dir}{dbfile}_data_{filenbr}.{ext}''',

        @cmd_movelogfile_fmt VARCHAR (2000) = ', MOVE ''{lname}'' TO ''{dir}{dbfile}_log_{filenbr}.{ext}''',

        @cmd_moveftfile_fmt VARCHAR(2000) = ', MOVE ''{lname}'' TO ''{ftdir}''',

        @cmd_restore_log_fmt VARCHAR(2000) = 'RESTORE LOG [{database}] FROM DISK=''{bkfile}'' WITH FILE={position}, NORECOVERY',

        @cmd_recover_fmt VARCHAR(500) = 'RESTORE DATABASE [{database}] WITH RECOVERY',

        @dbname_new_fmt VARCHAR(500) = '{instance}_{database}',



        @cmd VARCHAR(2000),



        @dbname VARCHAR(128),

        @instance VARCHAR(128),

        @diskfile VARCHAR(500),

        @position SMALLINT,

        @lsn NUMERIC(25,0),

        @recoverymodel NVARCHAR(60),

        @backupstartdate DATETIME,

        @logicaldatafile SYSNAME,

        @logicallogfile SYSNAME,



        @lname NVARCHAR(128),

        @phname NVARCHAR(260),

        @type CHAR(1),

        @db_filenr INT = 0,

        @log_filenr INT = 0,

        @ext VARCHAR(3),

        @dbfilelist dbo.RestoreFileLisOnlytOutput

        

    

    SELECT TOP 1 @diskfile = diskfilename, @position = position,

                @instance = servername, @dbname = databasename, @recoverymodel = recoverymodel,

                @lsn = lastlsn, @backupstartdate = backupstartdate

    FROM @backupinfo

    WHERE backuptype = 1

    ORDER BY firstlsn DESC, backupstartdate DESC



    IF @@rowcount = 0

    BEGIN

        RAISERROR (@msg_err_no_fullback, 16, 1, @dbname)

        RETURN 2

    END



/************************************************************************************

* Restore database full backup

*************************************************************************************/

SET @cmd = REPLACE(@cmd_filelist_fmt, '{bkfile}', @diskfile)

INSERT @dbfilelist EXEC ( @cmd )



SET @NewDBName = REPLACE(REPLACE(REPLACE(@dbname_new_fmt, '{instance}', @instance), '{database}', @dbname), '\', '$')

DECLARE @new_ft_dir VARCHAR(500) = @fulltext_root + @NewDBName + '_ft\'



-- delete @new_ft_dir if exists



-- main segment of restore database statement

SET @cmd = REPLACE(REPLACE(REPLACE(@cmd_restore_db_fmt, '{database}', @NewDBName), '{bkfile}', @diskfile), '{position}', CAST(@position AS VARCHAR(5)))

SET @FullbackupTime = @backupstartdate



-- move files

DECLARE cur_dbfile CURSOR FOR

    SELECT lname, phname, [type] FROM @dbfilelist

    

OPEN cur_dbfile

WHILE 1 = 1

BEGIN

 FETCH NEXT FROM cur_dbfile INTO @lname, @phname, @type

    IF @@fetch_status <> 0 BREAK

    

    IF @type = 'D'

    BEGIN

        SET @db_filenr += 1

        SET @cmd += REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@cmd_movedatafile_fmt,

                            '{lname}', @lname),

                            '{dir}', @datafile_dir),

                            '{dbfile}', @NewDBName),

                            '{filenbr}', cast(@db_filenr as VARCHAR(2))),

                            '{ext}', CASE @db_filenr WHEN 1 THEN 'mdf' ELSE 'ndf' END)

    END

    ELSE IF @type = 'L'

    BEGIN

        SET @log_filenr += 1

        SET @cmd += REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@cmd_movelogfile_fmt,

                            '{lname}', @lname),

                            '{dir}', @logfile_dir),

                            '{dbfile}', @NewDBName),

                            '{filenbr}', cast(@log_filenr AS VARCHAR(2))),

                            '{ext}', 'ldf')

    END

    ELSE IF @type = 'F'

    BEGIN

        SET @cmd += replace(replace(@cmd_moveftfile_fmt,

                            '{lname}', @lname),

                            '{ftdir}', @new_ft_dir)

    END

END



CLOSE cur_dbfile

DEALLOCATE cur_dbfile



BEGIN TRY

    EXEC (@cmd)

END TRY

BEGIN CATCH

    RETURN ERROR_NUMBER()

END CATCH



/************************************************************************************

* Restore log files

*************************************************************************************/

IF @recoverymodel IS NULL OR @recoverymodel != 'SIMPLE'

WHILE 1 = 1

BEGIN

    SELECT TOP 1 @diskfile = diskfilename, @position = position,

                @instance = servername, @dbname = databasename, @recoverymodel = recoverymodel,

                @lsn = lastlsn, @backupstartdate = backupstartdate

        FROM @backupinfo

        WHERE backuptype = 2 and firstlsn <= @lsn and lastlsn >= @lsn and backupstartdate > @backupstartdate

        ORDER BY backupstartdate DESC

    

    IF @@rowcount = 0 BREAK



    SET @cmd = REPLACE(REPLACE(REPLACE(@cmd_restore_log_fmt, '{database}', @NewDBName), '{bkfile}', @diskfile), '{position}', CAST(@position AS VARCHAR(5)))

 SET @LastLogBackupTime = @backupstartdate



BEGIN TRY

    EXEC (@cmd)

END TRY

BEGIN CATCH

    RETURN ERROR_NUMBER()

END CATCH



END



/************************************************************************************

* Rescover database

*************************************************************************************/

SET @cmd = REPLACE(@cmd_recover_fmt, '{database}', @NewDBName)



BEGIN TRY

    EXEC (@cmd)

END TRY

begin catch

    RETURN ERROR_NUMBER()

END CATCH



END

GO



/****** Object: StoredProcedure [dbo].[_admsp_LogVerificationResult] Script Date: 06/15/2011 12:21:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*************************************************************************

* Description:    

* Log the steps and results of backup verification

* Internal procedure, called by dbo.admsp_VerifyBackups only

**************************************************************************/

CREATE PROCEDURE [dbo].[_admsp_LogVerificationResult]

    @BatchStartTime DATETIME,

    @Instance VARCHAR(128),

    @Folder VARCHAR(128),

    @Database VARCHAR(128),

    @Description VARCHAR(500),

    @Result INT,

    @Info VARCHAR(200),

    @Message VARCHAR(max) = NULL

AS

BEGIN

    SET NOCOUNT ON;

    INSERT INTO dbo.BackupVerificationResult (BatchStartTime, InstanceName, FolderName, DatabaseName, [Description], Result, Info, [Message], LogDateTime)

        VALUES(@BatchStartTime, @Instance, @Folder, @Database, @Description, @Result, @Info, @Message, GETDATE())

END

GO



/****** Object: StoredProcedure [dbo].[_admsp_InventoryBackupFiles] Script Date: 06/15/2011 12:21:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/******************************************************************************

* Inventory the backup files discovered by admsp_VerifyBackups

*

* Internal procedure, called by dbo.admsp_VerifyBackups only

******************************************************************************/

CREATE PROC [dbo].[_admsp_InventoryBackupFiles]    

    @BackupInfo dbo.BackupInfo READONLY,

    @BatchStartTime DATETIME = NULL,

    @InventoryTime DATETIME = NULL

AS

IF @BatchStartTime IS NULL SET @BatchStartTime = GETDATE()

IF @InventoryTime IS NULL SET @InventoryTime = GETDATE()



MERGE INTO dbo.BackupFiles AS target

USING @BackupInfo AS source ON (target.DiskFileName=source.DiskFileName and target.position=source.position and target.FirstLSN=source.FirstLSN and target.BackupStartDate=source.BackupStartDate)

WHEN MATCHED THEN

    UPDATE SET LastCheckTime = @InventoryTime, LastBatchStartTime = @BatchStartTime, IsDeleted = 0

WHEN NOT MATCHED THEN

    INSERT (DiskFileName,BackupName,BackupDescription,BackupType,ExpirationDate,Compressed,Position,DeviceType,UserName,ServerName,DatabaseName,DatabaseVersion,DatabaseCreationDate,BackupSize,FirstLSN,LastLSN,CheckpointLSN,DatabaseBackupLSN,BackupStartDate,BackupFinishDate,SortOrder,CodePage,UnicodeLocaleId,UnicodeComparisonStyle,CompatibilityLevel,SoftwareVendorId,SoftwareVersionMajor,SoftwareVersionMinor,SoftwareVersionBuild,MachineName,Flags,BindingID,RecoveryForkID,Collation,FamilyGUID,HasBulkLoggedData,IsSnapshot,IsReadOnly,IsSingleUser,HasBackupChecksums,IsDamaged,BeginsLogChain,HasIncompleteMetaData,IsForceOffline,IsCopyOnly,FirstRecoveryForkID,ForkPointLSN,RecoveryModel,DifferentialBaseLSN,DifferentialBaseGUID,BackupTypeDescription,BackupSetGUID,CompressedBackupSize, FirstCheckTime, LastCheckTime, LastBatchStartTime)

    VALUES (DiskFileName,BackupName,BackupDescription,BackupType,ExpirationDate,Compressed,Position,DeviceType,UserName,ServerName,DatabaseName,DatabaseVersion,DatabaseCreationDate,BackupSize,FirstLSN,LastLSN,CheckpointLSN,DatabaseBackupLSN,BackupStartDate,BackupFinishDate,SortOrder,CodePage,UnicodeLocaleId,UnicodeComparisonStyle,CompatibilityLevel,SoftwareVendorId,SoftwareVersionMajor,SoftwareVersionMinor,SoftwareVersionBuild,MachineName,Flags,BindingID,RecoveryForkID,Collation,FamilyGUID,HasBulkLoggedData,IsSnapshot,IsReadOnly,IsSingleUser,HasBackupChecksums,IsDamaged,BeginsLogChain,HasIncompleteMetaData,IsForceOffline,IsCopyOnly,FirstRecoveryForkID,ForkPointLSN,RecoveryModel,DifferentialBaseLSN,DifferentialBaseGUID,BackupTypeDescription,BackupSetGUID,CompressedBackupSize, @InventoryTime, @InventoryTime, @BatchStartTime);

GO



/****** Object: StoredProcedure [dbo].[admsp_VerifyBackups] Script Date: 06/15/2011 12:21:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/***********************************************************************************************************************

Restore databases and verify them with dbcc checkdb



Dependencies:

 Configuration: dbo.RestoreConfig

 Restore: dbo._admsp_RestoreDatabase

 Logging: dbo._admsp_LogVerificationResult, dbo._admsp_InventoryBackupFiles

 Outputs (via logging): dbo.BackupVerificationResult, dbo.BackupFiles

 User Defined Types: dbo.RestoreFileLisOnlytOutput, dbo.RestoreHeaderOnlyOutput, dbo.BackupInfo





************************************************************************************************************************/

CREATE PROC [dbo].[admsp_VerifyBackups]

AS

SET NOCOUNT ON



--------------------------------------------------------------------------------------------------------------------

-- Begin initialization

--------------------------------------------------------------------------------------------------------------------



-- Define local variables

DECLARE @ProcName VARCHAR (128) = object_name(@@PROCID)



DECLARE

    @MSG_VERIFICATIONSTART VARCHAR(500) = 'Verification start',

    @MSG_VERIFICATIONEND VARCHAR(500) = 'Verification end',

    @MSG_INSTANCEVERIFICATIONSTART VARCHAR(500) = 'Instance verification start',

    @MSG_INSTANCEVERIFICATIONEND VARCHAR(500) = 'Instance verification end',

    @MSG_FOLDERVERIFICATIONSTART VARCHAR(500) = 'Folder verification start',

    @MSG_FOLDERVERIFICATIONEND VARCHAR(500) = 'Folder verification end',

    @MSG_EMPTYINSTANCE VARCHAR(500) = 'Instance is empty or instance root not exist',

    @MSG_EMPTYFOLDER VARCHAR(500) = 'Folder is empty',

    @MSG_DBVERIFICATIONSTART VARCHAR(500) = 'Database verification start',

    @MSG_DBVERIFICATIONEND VARCHAR(500) = 'Database verification end',

    @MSG_ERROR_READINGROOT VARCHAR(500) = 'Error read database backup folders at instance root',

    @MSG_ERROR_DBRESTORE VARCHAR(500) = 'Database restore error',

 @MSG_SUCCESS_DBRESTORE VARCHAR(500) = 'Database restore success',

    @MSG_ERROR_DBCC VARCHAR(500) = 'DBCC error',

    @Batchstart DATETIME = getdate(),

    @InstanceCheckStart DATETIME,

    

    @FullbackupTime DATETIME,

    @LastLogBackupTime DATETIME,

    @RestoreMessage VARCHAR(500)

    

DECLARE

    @filepath_fmt VARCHAR (500) = '{instanceroot}{dir}{file}',

    @cmd VARCHAR(150),

    @result INT,

    @msg VARCHAR(500)

    

DECLARE    

    @instance VARCHAR(128),

    @InstanceRoot VARCHAR(500),

    @filename VARCHAR(200),

    @dir VARCHAR(200),

    @fullfilepath VARCHAR(500),

    @folder VARCHAR(128),

    @file VARCHAR(200),

    @database VARCHAR(128),

    @NewDBName VARCHAR(128),

    @cmd_restore_header VARCHAR(500)



DECLARE

    @bkfileheader dbo.RestoreHeaderOnlyOutput,

    @DB_BackupInfo dbo.BackupInfo



-- Define and initialize temp tables

IF object_id('tempdb..#bkfileheaders') IS NOT NULL DROP TABLE #bkfileheaders

SELECT @folder AS Folder, @fullfilepath AS DiskFileName, * INTO #bkfileheaders FROM @bkfileheader



IF object_id('tempdb..#files') IS NOT NULL DROP TABLE #files

CREATE TABLE #files ( rownbr INT IDENTITY (1,1), sub VARCHAR(2000), depth INT, isfile BIT )





DECLARE @Default_DataDir VARCHAR(255), @Default_LogDir VARCHAR(255)

SELECT @Default_DataDir = Dist_Datafile_Dir, @Default_LogDir = Dist_Logfile_Dir FROM dbo.RestoreConfig WHERE lvl = 0



-- End of intialization



EXEC [_admsp_LogVerificationResult] @batchstart, '', '', '', @MSG_VERIFICATIONSTART, 0, @ProcName





--------------------------------------------------------------------------------------------------------------------

-- Get all file headers

--------------------------------------------------------------------------------------------------------------------



-- Start loop for every row/instance in RestoreConfigure table here

DECLARE cur_inst CURSOR FOR

    SELECT SourceRoot, InstanceName FROM dbo.RestoreConfig WHERE lvl = 1 AND [Enable] = 1



OPEN cur_inst



WHILE 1 = 1

BEGIN -- cur_inst

FETCH cur_inst INTO @InstanceRoot, @Instance

IF @@FETCH_STATUS <> 0 BREAK



SET @InstanceCheckStart = GETDATE()



-- normalize @instanceroot

IF RIGHT (@InstanceRoot, 1) != '\' SET @InstanceRoot += '\'



EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', '', @MSG_INSTANCEVERIFICATIONSTART, 0, @ProcName



-- get files

INSERT #files (sub, depth, isfile)

 EXEC master.sys.xp_dirtree @InstanceRoot, 2, 1



-- If we get nothing it could be that the instance has not begun backup to the share or the share is not accessible

IF @@ROWCOUNT = 0

BEGIN

 EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', '', @MSG_EMPTYINSTANCE, 1, @ProcName



END



-- loop through sub folders

DECLARE cur_folders cursor for

    SELECT sub FROM #files WHERE depth = 1 AND isfile = 0

    UNION

    SELECT top 1 '' FROM #files WHERE depth = 1 AND isfile = 1      -- files in instance root





OPEN cur_folders

WHILE 1 = 1

BEGIN -- cur_folder

FETCH cur_folders INTO @folder

IF @@FETCH_STATUS <> 0 BREAK



EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, @folder, '', @MSG_FOLDERVERIFICATIONSTART, 0, @ProcName



-- loop through files

DECLARE cur_files cursor static for

SELECT [filename] FROM

(

    SELECT sub AS [filename], ( SELECT TOP 1 sub FROM #files

                                WHERE depth = 1 AND isfile = 0 AND rownbr < t.rownbr

                                ORDER BY rownbr DESC ) AS dir

    FROM #files t

    WHERE depth = 2 AND isfile = 1

) t WHERE @folder != '' AND dir = @folder

UNION

SELECT sub FROM #files WHERE depth = 1 AND isfile = 1 AND @folder = ''



OPEN cur_files



IF (@@CURSOR_ROWS = 0 AND @folder != '')

BEGIN

    SET @msg = @MSG_EMPTYFOLDER

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, @folder, '', @msg , 1, @ProcName, @folder

END



-- normalize @folder (after used in the cursor in its original form)

IF (@folder != '' AND RIGHT (@folder, 1) != '\') SET @folder += '\'



WHILE 1 = 1

BEGIN -- cur_files

FETCH cur_files INTO @file

IF @@FETCH_STATUS <> 0 BREAK



BEGIN TRY

    SET @fullfilepath = REPLACE(REPLACE(REPLACE(@filepath_fmt, '{instanceroot}', @instanceroot), '{dir}', @folder), '{file}', @file)

    SET @cmd_restore_header = 'RESTORE HEADERONLY FROM DISK=''' + @fullfilepath + ''''



    INSERT INTO @bkfileheader EXEC (@cmd_restore_header)

    INSERT INTO #bkfileheaders SELECT @folder, @fullfilepath, * FROM @bkfileheader

    

END TRY

BEGIN CATCH

    SET @result = ERROR_NUMBER()

    SET @msg = ERROR_MESSAGE()

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, @folder, '', @msg , @result, @ProcName, @fullfilepath

END CATCH



DELETE @bkfileheader



END -- cur_files

CLOSE cur_files

DEALLOCATE cur_files



END -- cur_folders

CLOSE cur_folders

DEALLOCATE cur_folders





--------------------------------------------------------------------------------------------------------------------

-- Got headers, now do restore

--------------------------------------------------------------------------------------------------------------------



DECLARE cur_db Cursor for

    SELECT DISTINCT ServerName, DatabaseName FROM #bkfileheaders



OPEN cur_db

WHILE 1 = 1

BEGIN



SET @FullbackupTime = NULL

SET @LastLogBackupTime = NULL



FETCH cur_db INTO @instance, @database

IF @@FETCH_STATUS <> 0 BREAK



INSERT @DB_BackupInfo SELECT * FROM #bkfileheaders WHERE ServerName = @instance AND DatabaseName = @database

IF @@ROWCOUNT = 0 BEGIN

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database , 'No backup files', 1, @ProcName

END

ELSE

BEGIN TRY



    -- merge with BackupFiles table so we get detailed info about backup files

    EXEC [_admsp_InventoryBackupFiles] @DB_BackupInfo, @BatchStart, @InstanceCheckStart



    -- Log restore start

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database , @MSG_DBVERIFICATIONSTART, 0, @ProcName

    

    -- Restore

    EXEC @result = _admsp_RestoreDatabase @DB_BackupInfo, @Default_DataDir, @Default_LogDir, @Default_DataDir, @NewDBName OUT, @FullbackupTime OUT, @LastLogBackupTime OUT

    

    SET @RestoreMessage = 'FullBackupTime:' + ISNULL(CONVERT(VARCHAR(20), @FullbackupTime, 120), '(NONE)') + ', LastLogBackupTime:' + ISNULL(CONVERT(VARCHAR(20), @LastLogBackupTime, 120), '(NONE)')

    

    IF @result > 0 BEGIN

        EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database, @MSG_ERROR_DBRESTORE, @result, @ProcName, @RestoreMessage

 END

 ELSE BEGIN

        EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database, @MSG_SUCCESS_DBRESTORE, 0, @ProcName, @RestoreMessage

 END

END TRY

BEGIN CATCH

    SET @result = ERROR_NUMBER()

    SET @msg = ERROR_MESSAGE()

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database, @msg, @result, @ProcName, @RestoreMessage

END CATCH



IF @database != 'master' -- master restored as non-master database will fail on dbcc check.

BEGIN TRY

    SET @cmd = 'DBCC CHECKDB ([' + @NewDBName + ']) WITH NO_INFOMSGS'

    EXEC (@cmd)

    SET @result = @@ERROR

    IF @result > 0

        EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database, @MSG_ERROR_DBCC, @result, @cmd

END TRY

BEGIN CATCH

    SET @result = ERROR_NUMBER()

    SET @msg = ERROR_MESSAGE()

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database, @msg, @result, @ProcName

END CATCH



IF DB_ID(@NewDBName) IS NOT NULL

BEGIN TRY

    SET @cmd = 'DROP DATABASE [' + @NewDBName + ']'

    EXEC (@cmd)

END TRY

BEGIN CATCH

    SET @result = ERROR_NUMBER()

    SET @msg = ERROR_MESSAGE()

    EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database, @msg, @result, @ProcName

END CATCH



EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', @database , @MSG_DBVERIFICATIONEND, 0, @ProcName



DELETE @DB_BackupInfo

END



CLOSE cur_db

DEALLOCATE cur_db



-- clear up

TRUNCATE TABLE #bkfileheaders

TRUNCATE TABLE #files



EXEC [_admsp_LogVerificationResult] @batchstart, @Instance, '', '', @MSG_INSTANCEVERIFICATIONEND, 0, @ProcName



END -- cur_inst



CLOSE cur_inst

DEALLOCATE cur_inst



EXEC [_admsp_LogVerificationResult] @batchstart, '', '', '', @MSG_VERIFICATIONEND, 0, @ProcName



DROP TABLE #bkfileheaders

DROP TABLE #files

GO