Cleaning up a database for local use

The below is a script that I’ve built that helps with cleaning up sensitive information and frees up space.

This script should never be ran on a production database as it shrinks it at the end. Read the comments in it and then look through the script before blindly running it. Let me know if I missed something!

Happy coding 🙂

–set the parameters below and then search this file for ‘–MODIFY’
–that will dictate other areas that need to be changed for your specific environment

–MODIFY
declare @CRMPATH nvarchar(100) = ‘https://localhost/bbappfx_staging/’,
@PREVIOUSBBISPATH nvarchar(100) = ‘https://production/bbis’,
@BBISPATH nvarchar(100) = ‘https://localhost/bbis_staging’,
@REPORTSERVER nvarchar(100) = ‘http://localhost/reportserver/’,
@PLUGINSERVICE nvarchar(100) = ‘http://localhost/pluginservice’,
@REPORTFOLDERPATH nvarchar(100) = ‘/Blackbaud/AppFx/BBCRM_Staging’,
@REPORTFOLDERDISPLAYNAME nvarchar(100) = ‘/Staging’,
@IMPORTURLORPATH nvarchar(100) = ‘\\testserver\import’,
@IMPORTUSERNAME nvarchar(100) = ‘user’,
@IMPORTPASSWORD nvarchar(100) = ‘password’,
@DBEMAILPROFILENAME nvarchar(100) = ‘CRM’,
@SELECTEDDATABASE nvarchar(100) = ‘BBInfinity’,
@EMAILDOMAIN nvarchar(100) = ‘test.com’,
@CLEANDATA bit = 0,
@CLEARSPACE bit = 0,
@CURRENTDATE datetime = getdate(),
@CHANGEAGENTID uniqueidentifier,
@ID uniqueidentifier;

if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

print ‘change the bbis integration links’;
update NetCommunityPluginService set URL = @PLUGINSERVICE + ‘/MasterService.asmx’
update NetcommunityIntegration set URL = @BBISPATH + ‘/login.aspx’
update SETTING set Value = @CRMPATH + ‘/AppfxWebService.asmx’ where ID =54
update SETTING set Value = @CRMPATH + ‘/bbncsvc/MasterService.asmx’ where ID =33
update SETTING set Value = @CRMPATH + ‘/AppfxWebService.asmx’ where ID =34
update CMSSITESETTING set VALUE = @BBISPATH where ENUMID = 11
update CMSSITESETTING set VALUE = @BBISPATH + ‘/Page.aspx’ where ENUMID = 10

print ‘change the production js files to point to the staging js files’
update SITELAYOUTS set CONTENT = cast(replace(cast(CONTENT as nvarchar(max)), @PREVIOUSBBISPATH, @BBISPATH) as ntext)

print ‘switch to a non-production bbis environment’
delete BBPAYENVIRONMENTTEMPLATE
delete CRMBBENVIRONMENT
exec dbo.USP_INSERT_UPDATE_CRMBBENVIRONMENT @Id=’ACC537E6-028D-4C72-9AC8-0E2BE19636AB’,@Text=N’Production’,@IsActive=0
exec dbo.USP_INSERT_UPDATE_CRMBBENVIRONMENT @Id=’3D5B03AE-85F5-4DF7-83FF-70D8EA9581B2′,@Text=N’Staging’,@IsActive=1

print ‘change the installation info report urls’;
update INSTALLATIONINFO set ReportWSURL = @REPORTSERVER + ‘ReportService2005.asmx?wsdl’ where ID =1
update INSTALLATIONINFO set REPORTRENDERURL = @REPORTSERVER where ID =1
update INSTALLATIONINFO set ReportDATASOURCE = @REPORTFOLDERPATH + ‘BBAppFxDB’ where ID =1

print ‘change the report folder urls’;
update REPORTROOTFOLDERLIST set FOLDERPATH = @REPORTFOLDERPATH
update REPORTROOTFOLDERLIST set DISPLAYNAME = @REPORTFOLDERDISPLAYNAME

print ‘reset BBIS supervisor password to “Password1″‘
update [dbo].[ClientUsers]
set [Password] = ‘I0YNvQIhTX5cnCCYuBq/XQ==’
where UserName = ‘Supervisor’

print ‘create server logins’

–MODIFY
use [BBCRM_Staging];

–MODIFY
if not exists
(
select *
from sys.sysusers
where [name] = ‘DOMAIN\SERVERNAME$’
)
create user [DOMAIN\SERVERNAME$] for login [DOMAIN\SERVERNAME$]

–MODIFY
exec sp_addrolemember N’BBAPPFXSERVICEROLE’, N’DOMAIN\SERVERNAME$’

–MODIFY
exec sp_addrolemember N’BBAPPFXREPORTROLE’, N’DOMAIN\SERVERNAME$’

print ‘change import source’;
update IMPORTSOURCE set NAME = ‘Import’;
update IMPORTSOURCE set URLORPATH = @IMPORTURLORPATH;
update IMPORTSOURCE set USERNAME = @IMPORTUSERNAME;

exec DBO.USP_GET_KEY_ACCESS
update IMPORTSOURCE
set [PASSWORD] = ENCRYPTBYKEY(KEY_GUID(‘sym_BBInfinity’), convert(nvarchar, @IMPORTPASSWORD))
close SYMMETRIC key SYM_BBINFINITY;

print ‘change email alerts’;
update DATABASEMAILSETTINGS set PROFILENAME = @DBEMAILPROFILENAME;
update APPLICATIONURLPARTS set ROOT = @CRMPATH;
update APPLICATIONURLPARTS set SELECTEDDATABASE = @SELECTEDDATABASE;

print ‘load the application users’;
–MODIFY
declare @DISPLAYNAME nvarchar(255) = N’DOMAIN\SERVERNAME$’
–MODIFY
set @ID = ‘C59D8C5D-AB9A-4C1E-B81D-2221F0E0AC61′
–MODIFY
declare @USERSID varbinary(85) = null
–MODIFY
declare @WINDOWSUSERNAME nvarchar(255) = N’DOMAIN\SERVERNAME$’
declare @ISSYSADMIN bit = 1
if not exists (select null from dbo.APPUSER where USERSID = @USERSID)
insert into APPUSER (ID, USERSID, ISSYSADMIN, DISPLAYNAME, WINDOWSUSERNAME, ADDEDBYID, CHANGEDBYID)
values (@ID, @USERSID, @ISSYSADMIN, @DISPLAYNAME, @WINDOWSUSERNAME, @CHANGEAGENTID, @CHANGEAGENTID)

if(@CLEANDATA = 1)
begin
print ‘cleaning the constituent data’
declare @CONSTITEUNTSTONOTCHANGE table(LOOKUPID nvarchar(100));

–add these specific constituents that we dont want to change
–these may be test constituents that we already have in the system
–MODIFY
insert into @CONSTITEUNTSTONOTCHANGE
values(‘100001’),
(‘100003’),
(‘100008’),
(‘100009’),
(‘100010’),
(‘100011’);

–update constituent names, ssn, and birthdate
update
CONSTITUENT
set
FIRSTNAME = ‘FirstName’ + cast(SEQUENCEID as nvarchar(10)),
MIDDLENAME = ‘M.’,
KEYNAME = ‘LastName’ + cast(SEQUENCEID as nvarchar(10)),
SSN = ”,
SSNINDEX = ”,
BIRTHDATE = ‘00000000’,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
LOOKUPID not in (select LOOKUPID from @CONSTITEUNTSTONOTCHANGE)

–update constituent email address
update
EA
set
EA.EMAILADDRESS = cast(SEQUENCEID as nvarchar(10)) + @EMAILDOMAIN,
EA.CHANGEDBYID = @CHANGEAGENTID,
EA.DATECHANGED = @CURRENTDATE
from
EMAILADDRESS EA
inner join CONSTITUENT C on C.ID = EA.CONSTITUENTID
where
C.LOOKUPID not in (select LOOKUPID from @CONSTITEUNTSTONOTCHANGE)

–delete phone numbers
delete PHONE where CONSTITUENTID not in (select C.ID from CONSTITUENT C inner join @CONSTITEUNTSTONOTCHANGE U on U.LOOKUPID = C.LOOKUPID)

–update addresses
update
A
set
A.ADDRESSBLOCK = cast(SEQUENCEID as nvarchar(10)) + ‘ dirt road’,
A.CITY = ‘nice city’,
A.CHANGEDBYID = @CHANGEAGENTID,
A.DATECHANGED = @CURRENTDATE
from
ADDRESS A
inner join CONSTITUENT C on C.ID = A.CONSTITUENTID
where
C.LOOKUPID not in (select LOOKUPID from @CONSTITEUNTSTONOTCHANGE)
end

if(@CLEARSPACE = 1)
begin
exec USP_GLOBALCHANGE_BATCHREPORTDELETE
@CHANGEAGENTID = null,
@ASOF = null,
@SELECTIONID = null,
@DATEINTERVAL = 1,
@DATEPART = 2

exec dbo.USP_GLOBALCHANGE_BUSINESSPROCESSOUTPUTDELETE
@CHANGEAGENTID = null,
@ASOF = null,
@SELECTIONID = null,
@DATEPART = 2,
@DATEINTERVAL = 1

–these next tables are tables that are in the database growth management section
–of crm that are larger than any of the other tables so lets get rid of the data
–drop any keys so we can truncate the any large tables that we dont are about having data in
–MODIFY ALL BELOW AS NEEDED
ALTER TABLE [dbo].[NETCOMMUNITYNEWSLETTERSUBSCRIPTION] DROP CONSTRAINT [FK_NETCOMMUNITYNEWSLETTERSUBSCRIPTION_LATESTEMAILJOBRECIPIENTID]
ALTER TABLE [dbo].[WPPHILANTHROPICGIFTCATEGORY] DROP CONSTRAINT [FK_WPPHILANTHROPICGIFTCATEGORY_WPPHILANTHROPICGIFTID]
ALTER TABLE [dbo].[WPREALESTATEHISTORY] DROP CONSTRAINT [FK_WPREALESTATEHISTORY_WPREALESTATEID]

truncate table NETCOMMUNITYNEWSLETTERSUBSCRIPTION
truncate table NETCOMMUNITYEMAILJOBRECIPIENT
truncate table USR_BBISCOERSESSION
truncate table CONSTITUENTATTACHMENT
truncate table REVENUEATTACHMENT;
truncate table STATS;
truncate table PROSPECTPLANATTACHMENT;
truncate table WPPHILANTHROPICGIFTCATEGORY;
truncate table WPPHILANTHROPICGIFT;
truncate table WPPOLITICALDONATION;
truncate table WPRELATIONSHIP_NPA_IND;
truncate table WPAFFLUENCEINDICATOR;
truncate table WPREALESTATEHISTORY;
truncate table WPREALESTATE;
truncate table BBPSLOGIN;
truncate table BBPSLOGINAUDIT;

–add keys back
ALTER TABLE [dbo].[NETCOMMUNITYNEWSLETTERSUBSCRIPTION] WITH NOCHECK ADD CONSTRAINT [FK_NETCOMMUNITYNEWSLETTERSUBSCRIPTION_LATESTEMAILJOBRECIPIENTID] FOREIGN KEY([LATESTEMAILJOBRECIPIENTID])
REFERENCES [dbo].[NETCOMMUNITYEMAILJOBRECIPIENT] ([ID])

ALTER TABLE [dbo].[NETCOMMUNITYNEWSLETTERSUBSCRIPTION] NOCHECK CONSTRAINT [FK_NETCOMMUNITYNEWSLETTERSUBSCRIPTION_LATESTEMAILJOBRECIPIENTID]

ALTER TABLE [dbo].[WPREALESTATEHISTORY] WITH CHECK ADD CONSTRAINT [FK_WPREALESTATEHISTORY_WPREALESTATEID] FOREIGN KEY([WPREALESTATEID])
REFERENCES [dbo].[WPREALESTATE] ([ID])
ON DELETE CASCADE

ALTER TABLE [dbo].[WPREALESTATEHISTORY] CHECK CONSTRAINT [FK_WPREALESTATEHISTORY_WPREALESTATEID]

ALTER TABLE [dbo].[WPPHILANTHROPICGIFTCATEGORY] WITH CHECK ADD CONSTRAINT [FK_WPPHILANTHROPICGIFTCATEGORY_WPPHILANTHROPICGIFTID] FOREIGN KEY([WPPHILANTHROPICGIFTID])
REFERENCES [dbo].[WPPHILANTHROPICGIFT] ([ID])
ON DELETE CASCADE

ALTER TABLE [dbo].[WPPHILANTHROPICGIFTCATEGORY] CHECK CONSTRAINT [FK_WPPHILANTHROPICGIFTCATEGORY_WPPHILANTHROPICGIFTID]

print ‘clear all audit tables’
declare @AUDITTABLES table(ID uniqueidentifier default newid() primary key, TABLENAME nvarchar(100), PROCESSED bit default 0);

insert into @AUDITTABLES(TABLENAME)
select TABLE_NAME from information_schema.tables WHERE TABLE_TYPE = ‘BASE TABLE’ and TABLE_NAME like ‘%AUDIT’;

while exists(select 1 from @AUDITTABLES where PROCESSED = 0)
begin
declare @TEMP_ID uniqueidentifier,
@TABLENAME nvarchar(100),
@SQL nvarchar(200);

select
top 1
@TEMP_ID = ID,
@TABLENAME = TABLENAME
from
@AUDITTABLES
where
PROCESSED = 0;

set @SQL = ‘truncate table ‘ + @TABLENAME;
exec sp_executesql @SQL

update @AUDITTABLES set PROCESSED = 1 where ID = @TEMP_ID;
end

print ‘shrinking the database. only do this on non-production databases’;
–MODIFY
dbcc shrinkdatabase(BBCRM_Training)
end

print ‘COMPLETE!’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s