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









find out (SQL Server 2005 and onwards or with compatibility modes of 90 and higher) how long a particular Backup or restore on a database was taking or elapsed time


USE MASTER
--
SELECT SESSION_ID, '[' + CAST(DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DATABASE_ID) AS [DATABASE],
PERCENT_COMPLETE, START_TIME, STATUS, COMMAND,
DATEADD(MS, ESTIMATED_COMPLETION_TIME, GETDATE()) AS ESTIMATED_COMPLETION_TIME, CPU_TIME
FROM SYS.DM_EXEC_REQUESTS
--Apply this Where Clause Filter if you need to check specific events such as Backups, Restores, Index et al.
WHERE COMMAND LIKE '%BACKUP%' OR COMMAND LIKE '%RESTORE%' OR COMMAND LIKE '%INDEX%' OR COMMAND LIKE '%DBCC%'

Modified sp_who2


USE [master]



GO











IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwWho3]'))



DROP VIEW [dbo].[vwWho3]



GO











CREATE VIEW vwWho3



AS







SELECT



dess.session_id AS [SPID],CASE der.status WHEN 'background' THEN 'BACKGROUND' ELSE der.status END AS 'Status' ,



CONVERT(varchar(32),dess.original_login_name) AS [Login],


ISNULL(dess.host_name,'.') AS [HostName],CASE CONVERT(varchar(12),der.blocking_session_id) WHEN '0' THEN '.'



ELSE CONVERT(varchar(12),der.blocking_session_id) END AS BlkBy,



DB_NAME(der.database_id) AS DBName,



der.command AS 'Command',der.cpu_time AS [CPUTime],der.logical_reads AS [DiskIO],dess.last_request_start_time



AS [LastBatch],ISNULL(dess.program_name,'') AS [ProgramName],ISNULL(dest.text,'') AS 'CurrentQuery',



ISNULL(deqp.query_plan,'') AS 'CurrentPlan',rgwg.name AS [ResourceWorkgroupName]



FROM sys.dm_exec_requests der



INNER JOIN sys.resource_governor_workload_groups rgwg



ON



der.group_id = rgwg.group_id



INNER JOIN sys.dm_exec_sessions dess



ON



der.session_id = dess.session_id



OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest



OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp




--now only run this
--SELECT * FROM master.dbo.vwwho3

SQL Server Security Extract (Reverse Engineer) for ALL Databases


These scripts will extract security for Windows Logins, Database Users, Roles, Objects and SQL Logins.

-- Create SQL Logins
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + name + ''''
,', @defdb = ''' + default_database_name + ''''
,', @deflanguage = ''' + default_language_name + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password_hash AS varbinary(256))
,', @sid ='
, sid
FROM sys.sql_logins
WHERE name NOT IN ('sa')
ORDER BY name

-- Create Windows Logins
EXEC sp_MSforeachdb 'Print''-- Users for [?]''
Use [?];
Print''Use [?];''
select ''CREATE LOGIN ['' + server_login_name + ''] FROM WINDOWS WITH DEFAULT_DATABASE=[TEMPDB], DEFAULT_LANGUAGE=[us_english]''
from (
select dp.name as database_principal_name, sl.name server_login_name
from sys.database_principals dp
left outer join sys.server_principals sl on dp.sid = sl.sid
where dp.type =''U''and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
and sl.name <> ''''
) ss'

-- Create Database Users
EXEC sp_MSforeachdb 'Print''-- Users for [?]''
Use [?];
Print''Use [?];''
select ''CREATE USER ['' + database_principal_name + ''] FOR LOGIN ['' + server_login_name + ''] WITH DEFAULT_SCHEMA=[dbo]''
from (
select dp.name as database_principal_name, sl.name server_login_name
from sys.database_principals dp
left outer join sys.server_principals sl on dp.sid = sl.sid
where dp.type NOT IN (''R'', ''G'', ''C'')and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
and sl.name <> '''') ss'
-- ADD ROLE MEMBERS
EXEC sp_MSforeachdb 'Print''-- Role Members for [?]''
Print''Use [?];''
Use [?];SELECT ''EXEC sp_addrolemember '''''' + rp.name + '''''', '''''' + mp.name + '''''''' AS [-- CreateRoleSQL]
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
'

-- Remove ROLE MEMBERS
EXEC sp_MSforeachdb 'Print''-- Role Members for [?]''
Print''Use [?];''
Use [?];SELECT
''EXEC sp_droprolemember '''''' + rp.name + '''''', '''''' + mp.name + '''''''' AS [-- RemoveRoleSQL]
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
'

-- CREATE GRANT Object PERMISSIONS SCRIPT
Declare @sqlstm nvarchar(max)
Declare @dbname sysname
Declare rolldbname cursor for

Select name from master.dbo.sysdatabases where dbid>=5
open rolldbname
fetch next from rolldbname into @dbname
While @@fetch_status=0
BEGIN
PRINT '-- Permissions for ' + @dbname
SET @sqlstm=
 'Print''-- Role Members for [?]''
Print''Use ['+@DBName+ '];''
Use ['+@DBName+ '] SELECT
StateDesc + '' '' + PermissionName + '' ON SCHEMA::['' + s.name + ''] TO ['' + p.name + ''];'' AS [--RemoveSQL]
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.schemas AS s ON s.schema_id = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 3 /*''SCHEMA''*/
UNION ALL
SELECT
StateDesc + '' '' + PermissionName + '' TO ['' + p.name + ''];'' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 0 /*DATABASE*/
UNION ALL
SELECT
StateDesc + '' '' + PermissionName + '' ON ['' + s.name + ''].['' + o.name + ''] '' + ISNULL(''(['' + co.NAME + '']) '', '' '') + ''TO ['' + p.name + ''];'' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.objects AS o ON o.object_id = d.major_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 1 /*OBJECT OR COLUMN*/'
EXEC (@sqlstm)
fetch next from rolldbname into @dbname
END
close rolldbname
deallocate rolldbname
go
-- CREATE REMOVE Object PERMISSIONS SCRIPT
Declare @sqlstm nvarchar(max)
Declare @dbname sysname
Declare rolldbname cursor for

Select name from master.dbo.sysdatabases where dbid>=5
open rolldbname
fetch next from rolldbname into @dbname
While @@fetch_status=0
BEGIN
PRINT '-- Permissions for ' + @dbname
 SET @sqlstm=
 'Print''-- Role Members for [?]''
Print''Use ['+@DBName+ '];''
Use ['+@DBName+ '] SELECT
''REVOKE '' ++ '' '' + PermissionName + '' ON SCHEMA::['' + s.name + ''] TO ['' + p.name + ''];'' AS [--RemoveSQL]
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.schemas AS s ON s.schema_id = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 3 /*''SCHEMA''*/
UNION ALL
SELECT
''REVOKE '' ++ '' '' + PermissionName + '' TO ['' + p.name + ''];'' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 0 /*DATABASE*/
UNION ALL
SELECT
''REVOKE '' ++ '' '' + PermissionName + '' ON ['' + s.name + ''].['' + o.name + ''] '' + ISNULL(''(['' + co.NAME + '']) '', '' '') + ''TO ['' + p.name + ''];'' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.objects AS o ON o.object_id = d.major_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 1 /*OBJECT OR COLUMN*/'
EXEC (@sqlstm)
fetch next from rolldbname into @dbname
END
close rolldbname
deallocate rolldbname
go