--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:CONNECT SQL2K12-SVR1
ALTER AVAILABILITY GROUP [SQL2K12-AG1] FAILOVER;
GO

:CONNECT SQL2K12-SVR1

-- Delete Backups
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'xp_cmdshell', 1; RECONFIGURE; 
EXEC xp_cmdshell 'DEL C:\SQLBackups\ConfigDB*';
EXEC xp_cmdshell 'DEL C:\SQLBackups\ERPDatabase*';
EXECUTE sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
GO

DECLARE @Drop_AGs NVARCHAR(MAX) = ''

SELECT @Drop_AGs = 'DROP AVAILABILITY GROUP ' + QUOTENAME(name) + ';
'
FROM sys.availability_groups;

EXECUTE (@Drop_AGs);
GO
DROP ENDPOINT [Hadr_endpoint]
GO

IF DB_ID('ConfigDB') IS NOT NULL
BEGIN
	ALTER DATABASE [ConfigDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ConfigDB];
END
IF DB_ID('ERPDatabase') IS NOT NULL
BEGIN
	ALTER DATABASE [ERPDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ERPDatabase];
END

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'ERPLogin')
BEGIN
	DROP LOGIN [ERPLogin]
END
GO

USE [master]
GO
CREATE LOGIN [ERPLogin] WITH PASSWORD=N'12345', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE DATABASE [ERPDatabase]
GO
ALTER DATABASE [ERPDatabase] SET RECOVERY FULL 
GO

CREATE DATABASE [ConfigDB]
GO
ALTER DATABASE [ConfigDB] SET RECOVERY FULL 
GO

USE [ConfigDB]
GO
CREATE USER [ERPLogin] FOR LOGIN [ERPLogin]
ALTER ROLE [db_owner] ADD MEMBER [ERPLogin]
GO

USE [ERPDatabase]
GO
CREATE USER [ERPLogin] FOR LOGIN [ERPLogin]
ALTER ROLE [db_owner] ADD MEMBER [ERPLogin]
GO

USE [ConfigDB]
GO
CREATE TABLE InsertCount (RowID int identity primary key)
GO
INSERT INTO InsertCount DEFAULT VALUES;
GO

BACKUP DATABASE [ConfigDB] TO DISK=N'NUL'
GO
BACKUP DATABASE [ERPDatabase] TO DISK=N'NUL'
GO

IF DB_ID('AdventureWorks2012') IS NOT NULL
BEGIN
	IF ((SELECT state_desc FROM sys.databases WHERE name = 'AdventureWorks2012') != 'ONLINE')
		RESTORE DATABASE [AdventureWorks2012] WITH RECOVERY;
END
GO

EXEC sys.sp_configure N'contained database authentication', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

USE [msdb]
GO

/****** Object:  Job [ConfigDB Log Backup]    Script Date: 3/24/2012 7:12:47 PM ******/
EXEC msdb.dbo.sp_delete_job @job_name='ConfigDB Log Backup', @delete_unused_schedule=1
GO

USE [master]
GO
IF OBJECT_ID('BackupTransactionLogs') IS NOT NULL
BEGIN
	DROP PROCEDURE BackupTransactionLogs;
END
GO


:CONNECT SQL2K12-SVR2

DROP ENDPOINT [Hadr_endpoint]
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'DWUser')
BEGIN
	DROP LOGIN [DWUser]
END
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'ERPLogin')
BEGIN
	DROP LOGIN [ERPLogin]
END
GO

IF DB_ID('ConfigDB') IS NOT NULL
BEGIN
	ALTER DATABASE [ConfigDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ConfigDB];
END
IF DB_ID('ERPDatabase') IS NOT NULL
BEGIN
	ALTER DATABASE [ERPDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ERPDatabase]
END
IF DB_ID('AdventureWorks2012') IS NOT NULL
BEGIN
	ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks2012]
END
GO

EXEC sys.sp_configure N'contained database authentication', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

USE [msdb]
GO

/****** Object:  Job [ConfigDB Log Backup]    Script Date: 3/24/2012 7:12:47 PM ******/
EXEC msdb.dbo.sp_delete_job @job_name='ConfigDB Log Backup', @delete_unused_schedule=1
GO

USE [master]
GO
IF OBJECT_ID('BackupTransactionLogs') IS NOT NULL
BEGIN
	DROP PROCEDURE BackupTransactionLogs;
END
GO

:CONNECT SQL2K12-SVR3

DROP ENDPOINT [Hadr_endpoint]
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'DWUser')
BEGIN
	DROP LOGIN [DWUser]
END
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'ERPLogin')
BEGIN
	DROP LOGIN [ERPLogin]
END
GO

IF DB_ID('ConfigDB') IS NOT NULL
BEGIN
	ALTER DATABASE [ConfigDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ConfigDB];
END
IF DB_ID('ERPDatabase') IS NOT NULL
BEGIN
	ALTER DATABASE [ERPDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ERPDatabase]
END
IF DB_ID('AdventureWorks2012') IS NOT NULL
BEGIN
	ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks2012]
END
GO
EXEC sys.sp_configure N'contained database authentication', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

USE [msdb]
GO

/****** Object:  Job [ConfigDB Log Backup]    Script Date: 3/24/2012 7:12:47 PM ******/
EXEC msdb.dbo.sp_delete_job @job_name='ConfigDB Log Backup', @delete_unused_schedule=1
GO

USE [master]
GO
IF OBJECT_ID('BackupTransactionLogs') IS NOT NULL
BEGIN
	DROP PROCEDURE BackupTransactionLogs;
END
GO

:CONNECT SQL2K12-SVR4

DROP ENDPOINT [Hadr_endpoint]
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'DWUser')
BEGIN
	DROP LOGIN [DWUser]
END
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'ERPLogin')
BEGIN
	DROP LOGIN [ERPLogin]
END
GO

IF DB_ID('ConfigDB') IS NOT NULL
BEGIN
	ALTER DATABASE [ConfigDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ConfigDB];
END
IF DB_ID('ERPDatabase') IS NOT NULL
BEGIN
	ALTER DATABASE [ERPDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ERPDatabase]
END
IF DB_ID('AdventureWorks2012') IS NOT NULL
BEGIN
	ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks2012]
END
GO

EXEC sys.sp_configure N'contained database authentication', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO


USE [msdb]
GO

/****** Object:  Job [ConfigDB Log Backup]    Script Date: 3/24/2012 7:12:47 PM ******/
EXEC msdb.dbo.sp_delete_job @job_name='ConfigDB Log Backup', @delete_unused_schedule=1
GO

USE [master]
GO
IF OBJECT_ID('BackupTransactionLogs') IS NOT NULL
BEGIN
	DROP PROCEDURE BackupTransactionLogs;
END
GO

:CONNECT SQL2K12-SVR4

DROP ENDPOINT [Hadr_endpoint]
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'DWUser')
BEGIN
	DROP LOGIN [DWUser]
END
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'ERPLogin')
BEGIN
	DROP LOGIN [ERPLogin]
END
GO

IF DB_ID('ConfigDB') IS NOT NULL
BEGIN
	ALTER DATABASE [ConfigDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ConfigDB];
END
IF DB_ID('ERPDatabase') IS NOT NULL
BEGIN
	ALTER DATABASE [ERPDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ERPDatabase]
END
IF DB_ID('AdventureWorks2012') IS NOT NULL
BEGIN
	ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks2012]
END
GO

EXEC sys.sp_configure N'contained database authentication', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO


USE [msdb]
GO

/****** Object:  Job [ConfigDB Log Backup]    Script Date: 3/24/2012 7:12:47 PM ******/
EXEC msdb.dbo.sp_delete_job @job_name='ConfigDB Log Backup', @delete_unused_schedule=1
GO

USE [master]
GO
IF OBJECT_ID('BackupTransactionLogs') IS NOT NULL
BEGIN
	DROP PROCEDURE BackupTransactionLogs;
END
GO

:CONNECT SQL2K12-SVR5

DROP ENDPOINT [Hadr_endpoint]
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'DWUser')
BEGIN
	DROP LOGIN [DWUser]
END
GO

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'ERPLogin')
BEGIN
	DROP LOGIN [ERPLogin]
END
GO

IF DB_ID('ConfigDB') IS NOT NULL
BEGIN
	ALTER DATABASE [ConfigDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ConfigDB];
END
IF DB_ID('ERPDatabase') IS NOT NULL
BEGIN
	ALTER DATABASE [ERPDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ERPDatabase]
END
IF DB_ID('AdventureWorks2012') IS NOT NULL
BEGIN
	ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks2012]
END
GO

EXEC sys.sp_configure N'contained database authentication', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO


USE [msdb]
GO

/****** Object:  Job [ConfigDB Log Backup]    Script Date: 3/24/2012 7:12:47 PM ******/
EXEC msdb.dbo.sp_delete_job @job_name='ConfigDB Log Backup', @delete_unused_schedule=1
GO

USE [master]
GO
IF OBJECT_ID('BackupTransactionLogs') IS NOT NULL
BEGIN
	DROP PROCEDURE BackupTransactionLogs;
END
GO