Misc (non block) Tools

Copy Prod to Test Tool

These scripts make a copy of a production Rock environment by copying the filesystem (web folder) and database to a pre-configured "Test" environment. The environment is a local IIS and a remote SQL server. The local IIS has both the production Rock and a RockTest website. The remote SQL server has both a production RockDB and a RockDB_Test database.

Why all this trouble? Because you can create a clone of your production system in about 10 minutes -- in a repeatable manner with the running of one script.

⚠️ WARNING! Although this SQL script does disable all but a few Rock Jobs, and appends ".test" to all the email addresses, it keeps your current SMTP transport working as is. This might be a problem something starts churning and starts sending emails out. You have been warned.

❗ NOTE: You will need to replace any folder locations, database names, domain names, user names, etc. with your own.

Environments and Setup

Production

This preconfigured environment consists of:

The local IIS Rock website running in a local folder (C:\inetpub\wwwroot\) configured for two hosts (rock.yourdomain.com and rockweb.yourdomain.com)
A remote server (YOURSQLSERVER) running SQL 2012 database called "RockDB"

Test Environment

This preconfigured environment consists of:

The local IIS website (RockTest) running in a local folder (C:\inetpub\wwwroot-test\) with its own (RockTest) application pool and configured for two hosts (rocktest.yourdomain.com and rockwebtest.yourdomain.com)
A remote server (YOURSQLSERVER) running SQL 2012 database called "RockDB_Test"

Remote SQL Server

This is a regular MS SQL server with a folder that has the local script that performs the db operations. Both your prod and test databases will be on this server.

E: drive. (Although we put the SQL prep script on the E: drive you can put it wherever you choose -- in that case just adjust the scripts accordingly.)

Scripts

Here are the files and folders you should have in your RockProdToTest folder:

\---RockProdToTest
    |   RockProdToTest.bat
    |   RockProdToTest.ps1
    |   RunAfterUpdate.bat
    |
    \---Assets
            theme.css
            web.ConnectionStrings.config

The RockProdToTest.bat which runs the powershell script.
The RockProdToTest.ps1 powershell script.
RunAfterUpdate.bat which checks your Rock theme.css and replaces it with the one that references the warning theme.
A local Assets folder with:
theme.css (custom, includes orange warning borders)
web.ConnectionStrings.config (which points to the RockDB_Test database)

And on the remote SQL server:

E:.
+---Backups
    \---RockTest
            RockDB_Test.bak  (this is where the backup will be placed)
            RockTestPrep.sql

The E:\Backups\RockTest\ folder for backup data
The E:\Backups\RockTest\RockTestPrep.sql SQL script which does all the db backup, copy and update operations.

What It Does

In a nutshell, the PowerShell script does the following:

1. Deletes the old Test database backup (on the remote server)
2. Runs the SQL script (on the remote server) which does the following:
	2.1 Backup [RockDB] to RockDB_Test.bak
	2.2 Drops any old [RockDB_Test] 
	2.2 Restores backup to [RockDB_Test]
	2.3 Updates [RockDB_Test] settings:
		2.3.1 OrganizationWebSite to http://rockwebTest.yourdomain.com/
		2.3.2 PublicApplicationRoot to http://rockwebTest.yourdomain.com/
		2.3.3 CommChannelServer to be 'localhost'
		2.3.4 CommChannelPort to 25
		2.3.5 CommChannelActive to True
		2.3.6 CommChannelUser to ''
		2.3.7 CommChannelPassword to ''
		2.3.8 CommChannelSSL to false
		2.3.9 SiteDomains from 'rock.yourdomain.com' to 'rockTest.yourdomain.com'
		2.3.10 SiteDomains from 'rockweb.yourdomain.com' to 'rockwebTest.yourdomain.com'
		2.3.11 UpdateServerUrl to http://update.rockrms.com/F/rockalpha/api/v2/
		2.3.12 NOT FINISHED - Deactivate any other Communication Transports
3. Stops the Test website and application pool.
4. Deletes the old Test website folder.
5. Copies the rock website folder to the Test website folder.
6. Copies the Rock theme.css to theme_orig.css in the Test website.
7. In the Test website, replaces the connection string using the one in the Assets folder
8. In the Test website, replaces the Rock theme.css using the one in the Assets folder
9. Starts the Test website and application pool.

Scripts

❗❗❗

You will need to replace any folder locations, database names, domain names, user names, etc. with your own in all these scripts.

❗❗❗

RockProdTooTest.bat

powershell.exe -NoProfile -ExecutionPolicy RemoteSigned -File RockProdToTest.ps1
PAUSE

Assets\theme.css

@import url("theme_orig.css");

#content-wrapper header .navbar-default {
    background-color: orange;
  }

body {	
    border: 3px dashed orange;
}

RockProdToTest.ps1

Write-Host -ForegroundColor Yellow "Deleting old backup file, if it exists..."
Invoke-Command -ComputerName YOURSQLSERVER -ScriptBlock {If (Test-Path E:\Backups\RockTest\RockDB_Test.bak){Remove-Item E:\Backups\RockTest\RockDB_Test.bak -Force}}

Write-Host -ForegroundColor Yellow "Running DB operations..."
Invoke-Command -ComputerName YOURSQLSERVER -ScriptBlock {sqlcmd -S YOURSQLSERVER -E -i "E:\Backups\RockTest\RockTestPrep.sql" -o "E:\Backups\RockTest\RockTestPrep.log" }

Write-Host -ForegroundColor Yellow "Stopping test website..."
C:\windows\syswow64\inetsrv\appcmd.exe stop site "RockTest" 
C:\windows\syswow64\inetsrv\appcmd.exe stop apppool "RockTest" 

Write-Host -ForegroundColor Yellow "Deleting old wwwroot-test files..."
Get-ChildItem C:\inetpub\wwwroot-test\RockWeb\* -Recurse | Remove-Item -Recurse -Force
Get-ChildItem C:\inetpub\wwwroot-test\Rock\* -Recurse | Remove-Item -Recurse -Force

Write-Host -ForegroundColor Yellow "Copying wwwroot files to wwwroot-test..."
Copy-Item C:\inetpub\wwwroot\RockWeb\* -Destination C:\inetpub\wwwroot-test\RockWeb\ -Recurse -Force

Write-Host -ForegroundColor Yellow "Updating connectionString file..."
Copy-Item Assets\web.connectionStrings.config -Destination C:\inetpub\wwwroot-test\RockWeb\web.ConnectionStrings.config -Force

Write-Host -ForegroundColor Yellow "Updating Theme file..."
Copy-Item C:\inetpub\wwwroot-test\RockWeb\Themes\Rock\Styles\theme.css -Destination C:\inetpub\wwwroot-test\RockWeb\Themes\Rock\Styles\theme_orig.css -Force
Copy-Item Assets\theme.css -Destination C:\inetpub\wwwroot-test\RockWeb\Themes\Rock\Styles\theme.css -Force

Write-Host -ForegroundColor Yellow "Starting test website..."
C:\windows\syswow64\inetsrv\appcmd.exe start apppool "RockTest"
C:\windows\syswow64\inetsrv\appcmd.exe start site "RockTest"

Write-Host -ForegroundColor Yellow "All Done!"

RunAfterUpdate.bat

@echo off

echo.---------------------------------------------------------------------
echo.This is for use when the Rock Update has replaced the theme.css file.
echo.Therefore you might consider running this after you run a Rock Update
echo.on your test server.
echo.---------------------------------------------------------------------
echo.

set /p ReplaceTheme= Do you want to reset the alpha theme [y/n]?

if /i {%ReplaceTheme%} NEQ {y} (
   echo OK, quitting. 
   echo.
   pause
   exit /b
)

set Theme=C:\inetpub\wwwroot-test\RockWeb\Themes\Rock\Styles\theme.css

FOR /F "usebackq" %%A IN ('%Theme%') DO set size=%%~zA

echo.
echo.File size is %size%

if %size% GTR 500 ( 
   echo.
   echo %Theme% is greater than 500 bytes
   copy %Theme% C:\inetpub\wwwroot-test\RockWeb\Themes\Rock\Styles\theme_orig.css 
   copy Assets\theme.css %Theme%
) else (
   echo.
   echo Because the %Theme% 
   echo is less than 500 bytes I'm not going to replace it.
   echo (I'm assuming you basically made a mistake in saying yes.)
)

echo.
PAUSE

RockTestPrep.sql

--
-- Backup production DB
--
BACKUP DATABASE [RockDB] TO DISK = N'E:\backups\RockTest\RockDB_Test.bak' WITH NOFORMAT,
  INIT,
  NAME = N'RockDB_Test_Backup',
  SKIP,
  NOREWIND,
  NOUNLOAD,
  STATS = 10
GO

--
-- DROP current test DB
--
USE [master]
IF DB_ID('RockDB_Test') is not null
BEGIN
  EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'RockDB_Test'
  ALTER DATABASE [RockDB_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DROP DATABASE [RockDB_Test]
END
GO

--
-- Restore DB into Test
--
RESTORE DATABASE [RockDB_Test] FROM DISK = N'E:\backups\RockTest\RockDB_Test.bak' WITH FILE = 1,
  MOVE N'RockDB' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\RockDB_Test.mdf',
  MOVE N'RockDB_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\RockDB_Test_log.ldf',
  NOUNLOAD,
  STATS = 5
GO
USE [RockDB_Test]
GO
ALTER DATABASE [RockDB_Test] MODIFY FILE (NAME=N'RockDB', NEWNAME=N'RockDB_Test')
GO
ALTER DATABASE [RockDB_Test] MODIFY FILE (NAME=N'RockDB_log', NEWNAME=N'RockDB_Test_log')
GO

--
-- Update OrganizationWebSite
--
USE [RockDB_Test]
GO

-- Do this otherwise your updates may fail (depending on your DB settings, etc.)
SET ANSI_NULLS, QUOTED_IDENTIFIER ON

DECLARE @OrganizationWebSiteId int = (SELECT Id from [Attribute] WHERE [Key] = 'OrganizationWebSite')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @OrganizationWebSiteId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'http://rockwebtest.yourdomain.com/'
  WHERE [AttributeId] = @OrganizationWebSiteId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
    ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (1
        ,@OrganizationWebSiteId
        ,null
        ,'http://rockwebtest.yourdomain.com/'
        ,'E257083E-B0C2-479B-880B-E8702A6E25A3'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update PublicationApplicationRoot to test
--
DECLARE @PublicApplicationRootId int = (select Id from [Attribute] WHERE [Key] = 'PublicApplicationRoot')
IF EXISTS ( SELECT [Id] FROM [AttributeValue]  WHERE [AttributeId] = @PublicApplicationRootId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'http://rockwebtest.yourdomain.com/'
  WHERE [AttributeId] = @PublicApplicationRootId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
    ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (1
        ,@PublicApplicationRootId
        ,null
        ,'http://rockwebtest.yourdomain.com/'
        ,'A416AD46-028C-4EEC-A23D-FB2093AEB1A8'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update Mandrill Transport to deactivate it
--
DECLARE @MandrillTransportEntityTypeId int = (SELECT TOP 1 [Id] from [EntityType] WHERE [Name] = 'Rock.Communication.Transport.MandrillSmtp')
DECLARE @MandrillTransportActiveAttributeId int = (SELECT Id from [Attribute] WHERE [EntityTypeId] = @MandrillTransportEntityTypeId AND [Key] = 'Active' )
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @MandrillTransportActiveAttributeId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'False'
  WHERE [AttributeId] = @MandrillTransportActiveAttributeId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
  ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@MandrillTransportActiveAttributeId
        ,0
        ,'False'
        ,'AF366435-6BE5-4985-9F22-8620FFAFDCA8'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update Mailgun Transport to deactivate it
--
DECLARE @MailgunTransportEntityTypeId int = (SELECT TOP 1 [Id] from [EntityType] WHERE [Name] = 'Rock.Communication.Transport.MailgunSmtp')
DECLARE @MailgunTransportActiveAttributeId int = (SELECT Id from [Attribute] WHERE [EntityTypeId] = @MailgunTransportEntityTypeId AND [Key] = 'Active' )
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @MailgunTransportActiveAttributeId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'False'
  WHERE [AttributeId] = @MailgunTransportActiveAttributeId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
  ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@MailgunTransportActiveAttributeId
        ,0
        ,'False'
        ,'F53DB647-DE86-4E0A-BF85-968110D7D86C'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update SMTP Transport to deactivate it
--
DECLARE @SMTPTransportEntityTypeId int = (SELECT TOP 1 [Id] from [EntityType] WHERE [Name] = 'Rock.Communication.Transport.SMTP')
DECLARE @SMTPTransportAttributeId int = (SELECT Id from [Attribute] WHERE [EntityTypeId] = @SMTPTransportEntityTypeId AND [Key] = 'Active' )
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @SMTPTransportAttributeId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'False'
  WHERE [AttributeId] = @SMTPTransportAttributeId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
  ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@SMTPTransportAttributeId
        ,0
        ,'False'
        ,'FCF41882-BE77-4817-BF40-5B01B9213602'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update Twilio Transport to deactivate it
--
DECLARE @TwilioTransportEntityTypeId int = (SELECT TOP 1 [Id] from [EntityType] WHERE [Name] = 'Rock.Communication.Transport.Twilio')
DECLARE @TwilioTransportAttributeId int = (SELECT Id from [Attribute] WHERE [EntityTypeId] = @TwilioTransportEntityTypeId AND [Key] = 'Active' )
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @TwilioTransportAttributeId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'False'
  WHERE [AttributeId] = @TwilioTransportAttributeId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
  ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@TwilioTransportAttributeId
        ,0
        ,'False'
        ,'62F159FC-FD0F-41E3-90B5-58D9C0D78EC3'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update CommChannelServer to be 'localhost'
--
DECLARE @CommChannelServerId int = (SELECT Id from [Attribute] WHERE [Guid] = '6CFFDF99-E93A-49B8-B440-0EF93878A51F')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @CommChannelServerId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'localhost'
  WHERE [AttributeId] = @CommChannelServerId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
  ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (1
        ,@CommChannelServerId
        ,0
        ,'localhost'
        ,'BD18DBE8-E8C0-4A5E-823D-BECBCDABF419'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update CommChannelPort to 25
--
DECLARE @CommChannelPortId int = (SELECT Id from [Attribute] WHERE [Guid] = 'C6B13F15-9D6F-45B2-BDB9-E77D29A32EBF')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @CommChannelPortId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = '25'
  WHERE [AttributeId] = @CommChannelPortId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
    ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@CommChannelPortId
        ,0
        ,'25'
        ,'F671DE32-F2FD-40B6-A7E4-84D42233972A'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update CommChannelActive to True
--
DECLARE @CommChannelActiveId int = (SELECT Id from [Attribute] WHERE [Guid] = '9ED9A07A-7074-4DD1-8B73-384E52C6DDE3')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @CommChannelActiveId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'True'
  WHERE [AttributeId] = @CommChannelActiveId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
    ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (1
        ,@CommChannelActiveId
        ,0
        ,'True'
        ,'FCF41882-BE77-4817-BF40-5B01B9213602'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update CommChannelUser
--
DECLARE @CommChannelUserId int = (SELECT Id from [Attribute] WHERE [Guid] = '2CE8D3AC-F851-462C-93D5-DB82F48DDBFD')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @CommChannelUserId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = ''
  WHERE [AttributeId] = @CommChannelUserId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
    ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@CommChannelUserId
        ,0
        ,''
        ,'1A7C18C0-1751-4F83-B3E3-7059990B5B24'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update CommChannelPassword
--
DECLARE @CommChannelPasswordId int = (SELECT Id from [Attribute] WHERE [Guid] = 'D3641DA0-9E50-4C98-A994-978AF308E745')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @CommChannelPasswordId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = ''
  WHERE [AttributeId] = @CommChannelPasswordId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
        ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@CommChannelPasswordId
        ,0
        ,''
        ,'9CC1623D-1A25-4554-AA8E-3593930F3739'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update CommChannelSSL
--
DECLARE @CommChannelSSLId int = (SELECT Id from [Attribute] WHERE [Guid] = 'B3B2308B-6CD2-4853-8220-C80D861F5D3C')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @CommChannelSSLId)
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'False'
  WHERE [AttributeId] = @CommChannelSSLId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
        ([IsSystem]
        ,[AttributeId]
        ,[EntityId]
        ,[Value]
        ,[Guid]
        ,[CreatedDateTime]
        ,[ModifiedDateTime]
        ,[CreatedByPersonAliasId]
        ,[ModifiedByPersonAliasId])
     VALUES
        (0
        ,@CommChannelSSLId
        ,0
        ,'False'
        ,'210A1472-6AAD-43F0-B346-FB5EBE9D4928'
        ,GetDate()
        ,GetDate()
        ,1
        ,1)
END

--
-- Update SiteDomains
--
UPDATE  [SiteDomain]
SET [Domain] = 'rocktest.yourdomain.com'
WHERE [Domain] = 'rock.yourdomain.com'

UPDATE  [SiteDomain]
SET [Domain] = 'rockwebtest.yourdomain.com'
WHERE [Domain] = 'rockweb.yourdomain.com'

DECLARE @SiteId int;

-- Add the "rocktest" host domain
IF NOT EXISTS ( SELECT [Id] FROM [SiteDomain] WHERE [Guid] = '5999D316-E062-4DE2-9556-8333A2D97C42' )
BEGIN
   SET @SiteId = ( SELECT [Id] FROM [Site] WHERE [Guid] = 'C2D29296-6A87-47A9-A753-EE4E9159C4C4' )
   INSERT INTO [SiteDomain]
     ([IsSystem]
      ,[SiteId]
      ,[Domain]
      ,[Guid])
      VALUES
      (0
      ,@SiteId
      ,'rocktest.yourdomain.com'
      ,'5999D316-E062-4DE2-9556-8333A2D97C42')
END

-- Add the "wwwtest" host domain
IF NOT EXISTS ( SELECT [Id] FROM [SiteDomain] WHERE [Guid] = '9A314D39-3122-43A6-8488-DFE81E5E0580' )
BEGIN
   SET @SiteId = ( SELECT [Id] FROM [Site] WHERE [Guid] = 'F3F82256-2D66-432B-9D67-3552CD2F4C2B' )
   INSERT INTO [SiteDomain]
     ([IsSystem]
      ,[SiteId]
      ,[Domain]
      ,[Guid])
      VALUES
      (0
      ,@SiteId
      ,'wwwtest.yourdomain.com'
      ,'9A314D39-3122-43A6-8488-DFE81E5E0580')
END



--
-- Update UpdateServerUrl to be 'test'
--
DECLARE @UpdateServerUrlId int = (SELECT Id from [Attribute] WHERE [Guid] = '306E7E7C-9416-4098-9C25-488380B940A5')
IF EXISTS ( SELECT [Id] FROM [AttributeValue] WHERE [AttributeId] = @UpdateServerUrlId )
BEGIN
  UPDATE  [AttributeValue]
  SET   [Value] = 'http://update.rockrms.com/F/rockalpha/api/v2/'
  WHERE [AttributeId] = @UpdateServerUrlId
END
ELSE
BEGIN
  INSERT INTO [AttributeValue]
    ([IsSystem]
     ,[AttributeId]
     ,[EntityId]
     ,[Value]
     ,[Guid]
     ,[CreatedDateTime]
     ,[ModifiedDateTime]
     ,[CreatedByPersonAliasId]
     ,[ModifiedByPersonAliasId])
     VALUES
     (0
     ,@UpdateServerUrlId
     ,NULL
     ,'http://update.rockrms.com/F/rockalpha/api/v2/'
     ,'5CB48974-6BB6-435B-A04A-2BF9B7CD778E'
     ,GetDate()
     ,GetDate()
     ,1
     ,1)
END


--
-- Clear all non-admin people's emails:
--
UPDATE [Person] SET [Email] = CONCAT([Email], '.test' ) WHERE [Email] != '' AND IsSystem != 1 AND ( ( [Email] != '[email protected]' ) OR ( [Email] != '[email protected]' ) OR ( [Email] != '[email protected]' ))


---
--- Deactivate all Service Jobs except: Job Pulse, Rock Cleanup, Process Workflows, 
---    Calculate Person Duplicates, Group Sync, Calculate Metrics, Calculate Group Requirements
---
UPDATE [ServiceJob] SET [IsActive] = 0 WHERE [Guid] NOT IN
(
'CB24FF2A-5AD3-4976-883F-DAF4EFC1D7C7'
,'1A8238B1-038A-4295-9FDE-C6D93002A5D7'
,'35EABBDB-1EFA-46F1-86D4-4199FFA2D9A7'
,'C386528C-3AC6-44E8-884E-A57B571B65D5'
,'57B539BC-7C4D-25BB-4EEB-39DF0EF62EBC'
,'3425AE05-354C-4C0E-AF5E-50CD6A7F8740'
,'ADC8FE8B-2C7D-46A4-885D-3EBB811DC03F'
)

---
--- Prefix all the Site Name's with "TEST "
---
UPDATE [Site] SET [Name] = CONCAT('TEST ', [Name] )

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Misc (non block) Tools

Copy Prod to Test Tool

Environments and Setup

Production

Test Environment

Remote SQL Server

Scripts

What It Does

Scripts

RockProdTooTest.bat

Assets\theme.css

RockProdToTest.ps1

RunAfterUpdate.bat

RockTestPrep.sql

Clone this wiki locally