|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss SQL Server or Oracle database administration related to SalesLogix databases. View the code of conduct for posting guidelines.
|
|
|
|
How to create a new database based on an existing one?
Posted: 04 Jul 08 10:29 AM
|
We have an SLX db here that we've been using and customising for years, and now we need to create a 2nd database for a division of ours.
What's the best way of duplicating an existing database, and clearing out all the users, accounts, contacts, and opportunities etc?
Installing a blank one from a CD and re-applying bundles isn't really an option I don't think, due to the huge number of customisations.
Thanks
|
|
|
|
Re: How to create a new database based on an existing one?
Posted: 05 Jul 08 5:14 AM
|
Here's something Todd Hardin put together, copy your database and then run this on it :
-- Author: Todd Hardin, Customer Systems Inc.
--
-- Revisions:
-- 2004/11/23 First release for SLX v6.2
--
-- Purpose:
-- Clean-out a previously configured SLX database for re-import of all data
-- Non-system data tables are truncated.
-- Special handling is implemented for the hybrid system-user table: ADDRESS
--
-- Note that the definition of "clean-out" may vary for each situation and this
-- script should be adjusted accordingly.
--
-- Assumptions:
-- User is logged into MS-SQL using SYSDBA account
--
-- CAUTION:
-- The user is advised to first run this script in TruncateMode = OFF in order to review
-- the list of tables that will be truncated.
--
-- The user is advised to make a full backup of the database before executing this script.
--
-- NOTE: Assumes user is logged in as SYSDBA user
--
--
print 'Begin CleanDB Script'
declare @TruncateMode varchar(16)
select @TruncateMode = 'OFF' -- {ON | OFF}
Print 'TruncateMode is: ' + @TruncateMode
Print ' '
if @TruncateMode = 'ON'
Begin
-- Special handling for the Address table
-- SLX stores two address records each for:
-- A. Users
-- B. Offices
-- You must have these address records in the address table
-- or SLX will not function correctly due to inner joins
-- performed by SLX.
-- This step extracts the User and Office records from ADDRESS table into
-- a work table used at end of script.
--
-- Drop Temp address table if exists
print 'Dropping temporary address work table if it exists'
if exists (select * from dbo.sysobjects where id = object_id(N'[zTempAddress]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [zTempAddress]
-- Create temp address table, populate with SYSTEM and USER addresses records only
print 'Creating Temporary address table - populate with User and Office address records'
select address.* into zTempAddress from address
where addressid in
(
select userinfo.addressid from userinfo
UNION
select userinfo.homeaddressid from userinfo
UNION
select branchoptions.addressid from branchoptions
UNION
select branchoptions.shippingid from branchoptions
)
End -- @TruncateMode = 'ON'
-- declare tablelist cursor for select table_name from information_schema.tables
-- exclude tables that should not be truncated
-- IMPORTANT NOTE: your specific needs may vary - please edit (uncomment) accordingly
-- Various types of tables to NOT truncate:
-- A. Work tables used by this script
-- B. SLX SYSTEM configuration tables
-- C. SLX USER configuration tables
-- D. PRODUCT definition tables
-- E. Defect, Ticket, Campaign, etc. - feature configuation tables
-- F. Custom tables you have created should not be truncated
-- To allow a table to be cleared (Truncated),
-- use comment to remove the table name from the truncation exclusion list.
declare tablelist cursor for
select table_name from information_schema.tables where
table_schema = 'sysdba' and
table_type = 'base table' and
table_name not in
(
'ZTEMPADDRESS', -- Do NOT truncate - special work table for this script
-- 'ACCOUNTINGSYSINFO', -- optional: possibly customized for account application integration
'ADMINROLES',
'AGENTS',
'ALERT',
'ALERTNOTIFY',
-- 'AREACATEGORYISSUE', -- optional: do not truncate, probably customized
'BRANCHOPTIONS',
'CALCULATEDFIELDDATA',
-- 'CAMPAIGN', -- optional: do not truncate, probably customized
-- 'CAMPAIGNTASK', -- optional: do not truncate, probably customized
'COLLECTIONDEFS',
'COLLECTIONFILES',
'COLLECTIONPATH',
'COLLECTIONSTATS',
'COLLECTIONUPDATES',
'CONCURRENTUSER',
'CONTROLCLASSES',
'CUSTOMSETTINGS',
-- If Defect Feature has already been configured, you may wish to retain
-- 'DEFECT', -- optional: possible custom values to retain
-- 'DEFECTACTIVITYITEM', -- optional: possible custom values to retain
-- 'DEFECTACTIVITYRATE', -- optional: possible custom values to retain
-- 'DEFECTHISTORY', -- optional: possible custom values to retain
-- 'DEFECTLINKACTSTATUS', -- optional: possible custom values to retain
-- 'DEFECTPROBLEM', -- optional: possible custom values to retain
-- 'DEFECTPRODUCT', -- optional: possible custom values to retain
-- 'DEFECTSOLUTION', -- optional: possible custom values to retain
-- 'EVENT', -- Administrator defined Events (holidays, etc.)
-- 'EXCHANGERATE', -- Currency Exchange rates
-- 'INDEXDEFINITION', -- SpeedSearch index definitions
-- 'INDEXSCHEDULE', -- SpeedSearch schedule per index defined
'JOINDATA',
'LANGUAGETRANSLATION',
-- 'LEADSOURCE', -- optional: possible custom leadsources defined
-- 'LIBRARYDIRS', -- optional: possible custom Sales Library folders and docs
-- 'LIBRARYDOCS', -- optional: possible custom Sales Library folders and docs
'LICENSES',
-- 'LITERATURE', -- optional: possible custom literature defined for litrequest feature
'LOOKUP',
'META_CLASS',
'META_CLASSFUNCTION',
'META_FUNCTION',
'NOTIFICATIONCONFIG',
-- 'PACKAGEPRODUCT', -- optional: possible custom products with prod package defintions
-- 'PACKAGES', -- optional: possible custom package (product packaging) definitions
'PICKLIST',
'PLUGIN',
'PLUGINATTACHMENT',
'PLUGINEXTS',
-- 'POSTALCODE', -- optional: not used by SLX, but possibly used by custom code
-- 'PROCEDURECONTRIBUTOR', -- optional: possible custom support proceedures defined
-- 'PROCEDURES', -- optional: possible custom support proceedures defined
-- 'PRODUCT', -- optional: possible custom products defined
-- 'PRODUCTPROGRAM', -- optional: possible custom product programs defined
'PROJECT',
'PROJECTPLUGIN',
-- 'QUOTA', -- optional: possible custom sales quotas per user defined
'REALIGNPROFILE',
'REMOTETASKS',
-- 'RESOURCELIST', -- optional: possible custom calendar resources defined (rooms, equipment, etc.)
'RESYNCTABLEDEFS',
'SEC_FUNCTIONOWNER',
'SECCODE',
'SECCODEJOINS',
'SECFUNCTIONS',
'SECPROFILE',
'SECRIGHTS',
'SECRIGHTSCACHE',
'SECTABLEDEFS',
'SITE',
'SITEKEYS',
'SITEOPTIONS',
-- 'SKILLCATEGORY', -- optional: possible custom skillcategories defined per support user
'SLXBUNDLE',
'SLXTRIGGERS',
'SLXWEBACTION',
'SLXWEBALIAS',
'SLXWEBDATA',
'SLXWEBOVERRIDE',
'SLXWEBQUERY',
'SLXWEBSERVER',
'SLXWEBTEMPLATE',
'SLXWEBUSERINFO',
'SUBSCRIPTIONRULES',
'SYNCFILETRACKING',
'SYNCJOBHISTORY',
'SYNCSEQUENCING',
'SYNCSERVER',
'SYNCSERVICEFILE',
'SYNCTRANSFER',
'SYSTEMINFO',
-- 'TICKETACTIVITYRATE', -- optional: possible custom configuration values
-- 'TICKETAREAOWNER', -- optional: possible custom configuration values (per user)
-- 'TICKETPROBLEMSOLUTIONTYPE', -- optional: possible custom configuration values
-- 'TICKETPROBLEMTYPE', -- optional: possible custom configuration values
-- 'TICKETSOLUTIONTYPE', -- optional: possible custom configuration values
-- 'URGENCY', -- optional: possible custom support problem urgency levels
'USERCALENDAR',
'USERINFO',
'USEROPTIONDEF',
'USEROPTIONS',
'USERSECFUNCTIONS',
'USERPRODUCT',
'USERPROFILE',
'USERSALES',
'USERSECURITY',
'USERSUBSCRIPTION',
'USERVIEW',
'WORKAREA',
)
order by table_name
declare @TableName sysname
declare @TableCounter int
select @TableCounter = 0
open tablelist
fetch next from tablelist into @TableName
while @@fetch_status = 0
begin
select @TableCounter = @TableCounter + 1
print 'Processing Table: ' + @TableName
exec('declare @RowCount bigint select @RowCount = count(*) from ' + '[' + @TableName + ']' + ' Print ''Record Count for table: '' + ''' + @TableName + ''' + '' is '' + CAST(@RowCount as varchar(10)) ')
if @TruncateMode = 'OFF'
Begin
print 'TruncateMode is OFF, skipping actual truncation task'
End
if @TruncateMode = 'ON'
Begin
print 'Truncate Mode is ON, executing truncate statement'
exec('Truncate Table ' + @TableName)
exec('declare @RowCount bigint select @RowCount = count(*) from ' + '[' + @TableName + ']' + ' Print ''Record Count for table: '' + ''' + @TableName + ''' + '' is '' + CAST(@RowCount as varchar(10)) ')
End
print ' '
fetch next from tablelist into @TableName
end
close tablelist
deallocate tablelist
-- Re-populate the now empty Address table with USER and SYSTEM address records
if @TruncateMode = 'ON'
Begin
print 'Recovering Office and User address records'
-- must use EXEC because table does not actually exist until script is run
-- query analyzer validation happens before script is run, thus table is missing.
insert into Address select * from zTempAddress
-- Drop Temp address table
if exists (select * from dbo.sysobjects where id = object_id(N'[zTempAddress]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [zTempAddress]
End
print 'CleanDB Script Complete - please review output for errors' |
|
|
|
Re: How to create a new database based on an existing one?
Posted: 05 Jul 08 9:19 AM
|
Just one more thing -- as a precaution - set a new site code on the copy just in case at some point in the future you need to recombine databases.
Better to have done it and not needed it than vice versa 
ws |
|
|
|
Re: How to create a new database based on an existing one?
Posted: 06 Jul 08 9:02 PM
|
As an alternative, you can:
1. Install the blank SALESLOGIX database from the installation DVD.
2. Create the appropriate Sync folders for it.
3. Run the FIXSYSDBA SQL script.
4. Change the Sync logging path on the DB via SQL.
5. Create one or more projects on your original database to hold all of your modifications.
6. Create "bundles" for each of your projects.
7. Add a new connection for the new DB via Connection Manager.
8. And: Install the bundles on the new database.
|
|
|
|
Re: How to create a new database based on an existing one?
Posted: 06 Jul 08 10:20 PM
|
It all sounds so easy when you put it like that! But ...
Part 5 is the tough bit! In a heavily customised database (as is the case here), you can't just look for all the 'foreign' plugins and add them. That would miss fields, tables, picklists and probably several other areas that don't immediately come to mind.
Phil |
|
|
|
Re: How to create a new database based on an existing one?
Posted: 07 Jul 08 8:22 AM
|
How about creating a backup of your database and restore it to another database. I used to do this all the time to bring all my production data into my dev environment before I started a new project.
After you do this, you need to go into the restored one and change the sync profiles.
I would start with a blank database, create a new one from CD as suggested above and restore your production database to that one. Once you do this the logins will be the same as the production database, just the database name will be different.
Good luck,
TjD |
|
|
|
Re: How to create a new database based on an existing one?
Posted: 07 Jul 08 8:45 AM
|
Todd, I think he needs an emty database with no accounts, etc.
Phil, And I know its easy to say we should have complete knowledge of everything we've put into a db, but given the above situation, (run on sentence) Mike's method is the best.
|
|
|
| |
|
Re: How to create a new database based on an existing one?
Posted: 08 Jul 08 5:26 AM
|
Thanks, that looks great! The address table did stump me at the start too
I've added in a bunch of exclusions, mainly due to some custom work and running Eventix, and some I think because we're on 7.2.
There are still a few things which worry me, although it seems to be working fine. One of them is that adding a new server license has generated a new site-code, but SITEKEYS still only has the old sitecode, and adding records seems to be fine (and using the old site's codes). I guess resetting the sitekeys doesn't really matter, it's just the OCD in me that wants all keys to start from A000001 :D
-- additionally discovered tables not to truncate:
'ACCOUNTINGSYSINFO',
'C_CAMPAIGNCONFIG',
'EVXBILLOPTIONS',
'EVXBILLPAYMETHOD',
'EVXEXPENSETYPE',
'EVXTIMEZONE',
'EXCHANGERATE',
'INT_WORDLIST',
'INTEREST',
'KSYNC_RELS',
'PROJECTITEM',
'PROJECTPROFILE',
'PY3_SCOREWEIGHTINGS',
'SALESDASHBOARD',
'SLXMEMODATA',
'SLXOLEDBPLUGIN',
'SLXOLEDBPLUGINDATA',
'SLXQUERYPLAN',
'SLXQUERYPLANHINT',
'SLXSQLDEFAULT',
'SLXSQLSCALARFN',
'SLXWEBGROUP',
'SLXWEBGROUPITEM',
'SLXWEBREPORT',
'SLXWEBUSERDEVICE',
'SYNCSECTABLES',
'USERFEATURESECURITY',
'USEROPTION',
'VIRTUALFILESYSTEM',
'DASHBOARDOPTIONS',
'EMAILPROFILE',
'FEATURESECURITY',
'INDEXDEFINITION',
'INDEXSCHEDULE',
'KSYNC',
'KSYNCTABLES',
'MM_MAINTABLE',
'MM_MAINTABLE_FIELD',
'MM_MAINTABLE_REL'
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|