Andy's Database Scripts

Mirkwood Footsteps Uploaded Media By Size.sql

/* SELECT * FROM sys.views WHERE name LIKE '%BySize%' */

DECLARE @Grouping AS TABLE (
	 BusinessId INT
	,SizeInBytes BIGINT
)

INSERT INTO @Grouping
(BusinessId, SizeInBytes)
SELECT
	 BusinessId
	,SizeInBytes
FROM 
	[mw].[ActivityGroupMemberUploadedMediaBySize]
		
--
INSERT INTO @Grouping
(BusinessId, SizeInBytes)
SELECT
	 BusinessId
	,SizeInBytes
FROM 
	[mw].[ActivityGroupUploadedMediaBySize]
INSERT INTO @Grouping
(BusinessId, SizeInBytes)
SELECT
	 BusinessId
	,SizeInBytes
FROM 
	[mw].[EmailUploadedMediaBySize]


INSERT INTO @Grouping
(BusinessId, SizeInBytes)
SELECT
	 BusinessId
	,SizeInBytes
FROM 
	[mw].[NoteUploadedMediaBySize]
	
-- MW
SELECT 
	 BusinessId AS [Mirkwood Business Id]
	,SUM(SizeInBytes) / 1000000000.0 AS SizeInBytesGrouped 
FROM 
	@Grouping
GROUP BY
	BusinessId
ORDER BY
	SizeInBytesGrouped DESC


-- FS

SELECT
	 BusinessId AS [Footsteps Business Id]
	,(SUM(SizeInBytes) / 1000000000.0) AS SizeInBytesSum
FROM 
	[fs].[ObservationUploadedMediaBySize] 
GROUP BY
	BusinessId
ORDER BY
	SizeInBytesSum DESC
	
	

Restore Production Database to Staging.sql

/* Should be run on UATDB! */

-- 1. Add your SQLCLUS2 username and password here, then run from here to 2.
	DECLARE @Password VARCHAR(MAX) = ''
	DECLARE @Username VARCHAR(MAX) = '666316-SQLCLUS2\AMepham'

	DECLARE @Command VARCHAR(MAX) = 'net use E: \\666316-SQLCLUS2\E\BACKUP\666316-SQLCLUS2\BabyTech2\FULL ' + @Password + ' /user:' + @Username
	EXEC xp_cmdshell @Command

	USE [master]

	ALTER DATABASE [BabyTech2] 
	SET AUTO_UPDATE_STATISTICS_ASYNC OFF

	ALTER DATABASE [BabyTech2] 
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE

	DECLARE @Files TABLE (	Name NVARCHAR(MAX)	)
	
	INSERT INTO @Files
	EXEC xp_cmdshell 'dir E: /o:-s-d /b'
	
	IF ((SELECT TOP 1 Name FROM @Files WHERE Name LIKE '%666316-SQLCLUS2_BabyTech2_FULL%') IS NULL) BEGIN
		RAISERROR('Unable to find backup, please debug this script!', 16, 1);
	END
	
	DECLARE @Path NVARCHAR(MAX) = 'E:\' + (SELECT TOP 1 Name FROM @Files WHERE Name LIKE '%666316-SQLCLUS2_BabyTech2_FULL%')
	
	PRINT @Path
	
	RESTORE DATABASE [BabyTech2] FROM DISK = @Path

-- 2. Run the below code AFTER the database has restored successfully.
	USE [BabyTech2];
	GO

	ALTER USER [AbacusR9] WITH LOGIN = [AbacusR9]
	ALTER USER [ADavis] WITH LOGIN = [ADavis]
	ALTER USER [CParrish] WITH LOGIN = [CParrish]
	ALTER USER [AMepham] WITH LOGIN = [AMepham]
	ALTER USER [Deployments] WITH LOGIN = [Deployments]
	ALTER USER [DMiller] WITH LOGIN = [DMiller]
	ALTER USER [DUsborne] WITH LOGIN = [DUsborne]
	ALTER USER [KNorman] WITH LOGIN = [KNorman]
	ALTER USER [Mark] WITH LOGIN = [MTaylor]

	DROP USER [666311-VM17\ReportingUser] 
	CREATE USER [666313-UATDB\ReportingUser] FOR LOGIN [666313-UATDB\ReportingUser]

	USE [master]

	ALTER DATABASE [BabyTech2] SET
	MULTI_USER

	ALTER DATABASE [BabyTech2] SET
	AUTO_UPDATE_STATISTICS_ASYNC ON

SQL Server Uptime.sql

DECLARE @server_start_time DATETIME, 
@seconds_diff INT, 
@years_online INT, 
@days_online INT, 
@hours_online INT, 
@minutes_online INT, 
@seconds_online INT ; 

SELECT @server_start_time = login_time 
FROM master.sys.sysprocesses 
WHERE spid = 1 ; 

SELECT @seconds_diff = DATEDIFF(SECOND, @server_start_time, GETDATE()), 
@years_online = @seconds_diff / 31536000, 
@seconds_diff = @seconds_diff % 31536000, 
@days_online = @seconds_diff / 86400, 
@seconds_diff = @seconds_diff % 86400, 
@hours_online = @seconds_diff / 3600, 
@seconds_diff = @seconds_diff % 3600, 
@minutes_online = @seconds_diff / 60, 
@seconds_online = @seconds_diff % 60 ; 

SELECT @server_start_time AS server_start_time, 
@years_online AS years_online, 
@days_online AS days_online, 
@hours_online AS hours_online, 
@minutes_online AS minutes_online, 
@seconds_online AS seconds_online ;