Tuesday, August 13, 2013
Thursday, July 18, 2013
Friday, May 10, 2013
Wednesday, May 08, 2013
Script User and Role Object Permissions in SQL Server
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'CSODMGMT\SQLREOnly'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
/*
credit : http://www.sql-server-performance.com/2002/object-permission-scripts/2/
*/
SET @DatabaseUserName = 'CSODMGMT\SQLREOnly'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
/*
credit : http://www.sql-server-performance.com/2002/object-permission-scripts/2/
*/
Wednesday, April 24, 2013
sp_kill_user
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_kill_user] Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
@username nvarchar(1000) = 'dw_user'
)
as
begin
SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
GO
/****** Object: StoredProcedure [dbo].[sp_kill_user] Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
@username nvarchar(1000) = 'dw_user'
)
as
begin
SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE loginame = @username
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
Monday, April 15, 2013
Thursday, April 11, 2013
Wednesday, April 03, 2013
Table Partitioning on a varchar field
exec sp_who3
exec sp_whoisactive
drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]
CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO
drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
[user_id] [int] NULL,
[swimlane] [nvarchar](100) NULL,
[portal] [nvarchar](200) NULL,
[status_id] [int] NULL,
[user_type_id] [int] NULL,
[gender] [varchar](100) NULL,
[user_country] [varchar](100) NULL,
[user_state] [nvarchar](50) NULL,
[user_zipcode] [nvarchar](50) NULL,
[birth_dt] [datetime] NULL,
[hire_dt] [datetime] NULL,
[term_dt] [datetime] NULL,
[timezone] [int] NULL,
[date_stamp] [datetime] NULL,
[user_country_profile_base] [varchar](100) NULL,
[user_state_profile_base] [nvarchar](50) NULL,
[user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])
GO
exec sp_whoisactive
drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]
CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO
drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
[user_id] [int] NULL,
[swimlane] [nvarchar](100) NULL,
[portal] [nvarchar](200) NULL,
[status_id] [int] NULL,
[user_type_id] [int] NULL,
[gender] [varchar](100) NULL,
[user_country] [varchar](100) NULL,
[user_state] [nvarchar](50) NULL,
[user_zipcode] [nvarchar](50) NULL,
[birth_dt] [datetime] NULL,
[hire_dt] [datetime] NULL,
[term_dt] [datetime] NULL,
[timezone] [int] NULL,
[date_stamp] [datetime] NULL,
[user_country_profile_base] [varchar](100) NULL,
[user_state_profile_base] [nvarchar](50) NULL,
[user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])
GO
Thursday, January 31, 2013
Database last restore status report
use master
go
drop procedure sp_db_restore_status_report
go
create procedure sp_db_restore_status_report
as
begin
/*
select *
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
order by a.name
SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
Create table #tmp (db nvarchar(1000), restore_Date datetime)
insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
--select * From #tmp
if(OBJECT_ID('tempdb..#tmp_total')>1)
drop table #tmp_total
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
) as restored_older_24_hours
into #tmp_total
--select * From #tmp_total
DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);
SET @tableHTML =
N'
N'' +
' ;
print @tableHTML
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
end
go
drop procedure sp_db_restore_status_report
go
create procedure sp_db_restore_status_report
as
begin
/*
select *
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
order by a.name
SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
Create table #tmp (db nvarchar(1000), restore_Date datetime)
insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a WITH (nolock)
where a.name like 'ces_%'
--select * From #tmp
if(OBJECT_ID('tempdb..#tmp_total')>1)
drop table #tmp_total
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
) as restored_older_24_hours
into #tmp_total
--select * From #tmp_total
DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);
SET @tableHTML =
N'
Database Restore Report from '+@@SERVERNAME+'
' +N'
Total Databases | Restored in last 2 hours | Restored in last 8 hours | ' +Restored in last 24 hours | ' +Restored over 24 hours |
---|
print @tableHTML
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
end
Wednesday, January 09, 2013
sp_delete_files
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_delete_files] Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
,@ext = 'trc'
,@minutesgoback = -60
*/
ALTER proc [dbo].[sp_delete_files]
(
@folder varchar(100)
,@ext varchar(10)
,@minutesgoback int = -6000
)
as
begin
set nocount on;
declare @run int = 1 , @reccount int = 0
declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)
--set @folderlogs = @folder +'\logs\'
set @folderlogs = @folder
------------------------------------------------
set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
--print @sql
if(OBJECT_ID('tempdb..#tmp_tbl')>0)
drop table #tmp_tbl
CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))
INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql
DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
--select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
--From #tmp_tbl
--where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
---------------------------------------------------
declare tmp_cur cursor for
SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
open tmp_cur
Fetch next from tmp_cur into @filename
while @@fetch_status = 0
begin
print @filename
set @tablename = REPLACE(@filename,'.'+@ext,'')
print 'deleteing '+@tablename
set @filename = ltrim(RTRIM(@filename))
set @sql = 'del '+ @folder+ @filename
if @run = 0
print @sql
if @run = 1
EXECUTE master.dbo.xp_cmdshell @sql
Fetch next from tmp_cur into @filename
print '---'
end
close tmp_cur
deallocate tmp_cur
------------------------------------------------------
end
GO
/****** Object: StoredProcedure [dbo].[sp_delete_files] Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
,@ext = 'trc'
,@minutesgoback = -60
*/
ALTER proc [dbo].[sp_delete_files]
(
@folder varchar(100)
,@ext varchar(10)
,@minutesgoback int = -6000
)
as
begin
set nocount on;
declare @run int = 1 , @reccount int = 0
declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)
--set @folderlogs = @folder +'\logs\'
set @folderlogs = @folder
------------------------------------------------
set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
--print @sql
if(OBJECT_ID('tempdb..#tmp_tbl')>0)
drop table #tmp_tbl
CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))
INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql
DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
--select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
--From #tmp_tbl
--where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
---------------------------------------------------
declare tmp_cur cursor for
SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())
open tmp_cur
Fetch next from tmp_cur into @filename
while @@fetch_status = 0
begin
print @filename
set @tablename = REPLACE(@filename,'.'+@ext,'')
print 'deleteing '+@tablename
set @filename = ltrim(RTRIM(@filename))
set @sql = 'del '+ @folder+ @filename
if @run = 0
print @sql
if @run = 1
EXECUTE master.dbo.xp_cmdshell @sql
Fetch next from tmp_cur into @filename
print '---'
end
close tmp_cur
deallocate tmp_cur
------------------------------------------------------
end
Subscribe to:
Posts (Atom)