A development database is one of the most valuable tools that a Maximo admin can have. It provides a platform for testing version upgrades, patches and customizations without interrupting or endangering the production platform.
For a development database to be valid test environment, it should be kept reasonably up-to-date with the state of the production database. If the development database is on the same server as the production database, it's pretty straight forward to restore a production backup copy to the development container.
But when the development database is on another server, it's a little trickier. This page outlines a technique to copy a production Maximo database to a development server with source logins and passwords intact.
Jump to section: Prerequisites Backup from Source Restore to Destination Automation
Prequisites (back to top)On the source database server, these two stored procedures are used to export a file that will be used to setup logins with passwords on the destination server. To install, open Query Analyzer, paste in the first stored procedure's text and execute. Repeat for the second one. This only needs to be done once.
| sp_hexadecimal.sql |
|
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_hexadecimal' AND type = 'P')
DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUT AS DECLARE @charvalue varchar(255) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO |
| Download SP_HEXADECIMAL.ZIP (1 kb) |
| sp_help_revlogin_maximo.sql |
|
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_help_revlogin_maximo' AND type = 'P')
DROP PROCEDURE sp_help_revlogin_maximo GO CREATE PROCEDURE sp_help_revlogin_maximo @login_name sysname = NULL AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (255) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (255) IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa' AND name NOT IN ('MAXIMO','DEFLT','REPL_PUBLISHER', 'REPL_SUBSCRIBER','SYSADM','SYSSQL') AND name NOT LIKE '%\%' ELSE DECLARE login_curs CURSOR FOR SELECT name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_name AND name NOT IN ('MAXIMO','DEFLT','REPL_PUBLISHER','REPL_SUBSCRIBER','SYSADM','SYSSQL') AND name NOT LIKE '%\%'OPEN login_curs FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin_maximo script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' PRINT 'DECLARE @pwd sysname' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT(varchar, ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = ' + @txtpwd PRINT @tmpstr SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @encryptopt = ' END ELSE BEGIN -- Null password SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', NULL, @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + '''skip_encryption_old''' ELSE SET @tmpstr = @tmpstr + '''skip_encryption''' PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO |
| Download SP_HELP_REVLOGON_MAXIMO.ZIP (2 kb) |
Once the above stored procedures are installed on the source production database server, you're ready to run the 'source' section that will build a login file and create a database backup file.
On the source machine, open Query Analyzer and run:
EXEC sp_help_revlogin_maximo
Click into the result pane and save the output as BUILD_LOGINS.SQL. This file
will be used on the destination machine to create logins with passwords.
Clear the Query Analyzer window and run the following script, after setting
your local backup path and database name:
| backup_database.sql |
|
USE master
BACKUP DATABASE maximodb TO DISK = 'i:\backups\maximodb.bak' WITH INIT GO |
| Download BACKUP_DATABASE.ZIP (1 kb) |
When the backup completes, copy the database backup file, MAXIMODB.BAK, and the BUILD_LOGINS.SQL script to the destination database server.
Restore to Destination (back to top)On the destination database server, substitute your database name and database device paths and run this script to setup the development database container:
| setup_destination.sql |
|
/* Drop old database */
USE master DROP DATABASE maximodb /* Generate list of present logins to drop and drop them */ EXEC xp_execresultset 'select ''EXEC sp_droplogin ''+loginname FROM syslogins WHERE loginname NOT IN (''sa'') AND loginname NOT LIKE ''%\%'' ORDER BY loginname','master' /* When debugging, be sure that logins: DEFLT, MAXIMO, SYSADM, SYSSQL, report and updater got dropped, above */ /* Setup standard Maximo required logins */ USE master EXEC sp_addlogin MAXIMO, MAXIMO EXEC sp_addlogin SYSADM, SYSADM EXEC sp_addlogin SYSSQL, SYSSQL EXEC sp_addlogin DEFLT, DEFLT /* Create new database container */ USE master CREATE DATABASE maximodb ON ( NAME = maximodbdata, FILENAME = 'e:\data\maximodbdata.mdf') LOG ON ( NAME = maximodblog, FILENAME = 'f:\logs\maximodblog.ldf') /* Set up new database per PSDI specs - case sensitive! */ USE maximodb EXEC sp_changedbowner MAXIMO EXEC sp_addalias SYSADM,dbo EXEC sp_adduser SYSSQL,SYSSQL EXEC sp_adduser DEFLT,DEFLT EXEC sp_adduser guest GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO SYSSQL GO /* Check the work so far */ EXEC sp_helplogins EXEC sp_helpuser |
| Download SETUP_DESTINATION.ZIP (1 kb) |
Clear the Query Analyzer window and run the BUILD_LOGINS.SQL script to create the logins and passwords as the appeared on the source server. Use only the BUILD_LOGINS.SQL script that was generated on your source server as this file is unique to your installation. It's not available for download here and you shouldn't cut-and-paste the below sample:
| SAMPLE build_logins.sql |
|
/* sp_help_revlogin_maximo */
DECLARE @pwd sysname -- Login: ADAM SET @pwd = CONVERT (varchar, 0x2131235F2727395F5557564A5E523824) EXEC master..sp_addlogin 'ADAM', @pwd, @encryptopt = 'skip_encryption_old' -- Login: BAKER SET @pwd = 0x0100E52A50350D874CD4B396EAD77259C983E397923E6A09B39 -> 80D874CD4B396EAD77259C983E397923E6A09B398 EXEC master..sp_addlogin 'BAKER', @pwd, @encryptopt = 'skip_encryption' -- Login: CHARLIE SET @pwd = 0x01008D0BFE1BCE05AF4F257CE412E2B188D663C20CD54577454 -> 3CE05AF4F257CE412E2B188D663C20CD545774543 EXEC master..sp_addlogin 'CHARLIE', @pwd, @encryptopt = 'skip_encryption' -- Login: DELTA SET @pwd = CONVERT (varchar, 0x2131214A212C482F58505E2C5D35252A) EXEC master..sp_addlogin 'DELTA', @pwd, @encryptopt = 'skip_encryption_old' |
When the BUILD_LOGINS.SQL script completes, substitute your database name and database device paths and run the following to restore the database backup made earlier:
| restore_database.sql |
|
/* Restore the *.BAK backup to the database */
USE master RESTORE DATABASE maximodb FROM DISK = 'i:\backups\maximodb.bak' WITH MOVE 'maximodbdata' TO 'e:\data\maximodbdata.mdf', MOVE 'maximodblog' TO 'f:\logs\maximodblog.ldf', REPLACE GO |
| Download RESTORE_DATABASE.ZIP (1 kb) |
When the database restore completes, substitute your database name and run the following script to resynchronize the logins and userid:
| resync_logins.sql |
|
/* Resync users to logins */
USE maximodb EXEC sp_helpuser -- should show many NULL LoginNames -- as the SIDs are out of sync /* The following resync section reconciles the Logins and Users */ DECLARE @username nvarchar(255) DECLARE orphanuser_cur cursor FOR SELECT username = name FROM sysusers WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL ORDER BY name OPEN orphanuser_cur FETCH NEXT FROM orphanuser_cur INTO @username WHILE (@@fetch_status = 0) BEGIN PRINT @username + ' user name being resynced' EXEC sp_change_users_login 'Update_one', @username, @username FETCH NEXT FROM orphanuser_cur INTO @username END CLOSE orphanuser_cur DEALLOCATE orphanuser_cur GO /* End of resync section */ /* Check results */ EXEC sp_helpuser -- now nearly all of the NULL LoginNames -- have been matched up -- with the usernames, except DBO and -- GUEST. Then there's that -- NULL alias at the bottom to fix /* Fix the NULL alias */ USE master EXEC sp_droplogin SYSADM USE maximodb EXEC sp_dropalias 'SYSADM' USE master EXEC sp_addlogin SYSADM, SYSADM USE maximodb EXEC sp_addalias SYSADM, dbo /* Check results again */ USE maximodb EXEC sp_helpuser -- That fixed the alias but there -- are still two NULL logins -- one on DBO and one on GUEST EXEC sp_changedbowner MAXIMO -- This will set user DBO to -- login Maximo, the remaining -- NULL on guest is OK /* Patch miscellaneous items */ USE maximodb GRANT SELECT ON matusetrans TO public GO /* Final review */ EXEC sp_helpuser EXEC sp_helplogins /* Then take a look to see if the data is there */ SELECT COUNT(wonum) FROM workorder /* Finished */ |
| Download RESYNC_LOGINS.ZIP (1 kb) |
At this point, you should have a copy of your production database in a development environment with logins and passwords intact. This method was originally used with SQL Server V6.5, and should work with V6.5, V7.0 and 2000. With the release of SQL Server 2000, DTS has become a viable alternative to the above method.
Automation (back to top)If you'd like to automate the running of the scripts, you can use command files on the source and destination machines. On the source machine, you might use:
| source.cmd |
|
@ECHO OFF
CLS REM Run this on the source database server ECHO Preparing to backup database backup and logins. ->Press CTRL C now to abort, or PAUSE REM Build a source list of logins and passwords ECHO Backing up logins and passwords... ECHO Backing up database... osql /U sa /P sa_passsord /S source_server -d master /q "EXIT(sp_help_revlogin_maximo)" /w 999 /o i:\backups\build_logins.sql REM Make backup file osql /U sa /P sa_password /S source_server -d master /i backup_database.sql ECHO Finished PAUSE |
| Download SOURCE.ZIP(1 kb) |
On the destination machine, you might use:
| destination.cmd |
|
@ECHO OFF
REM Run this on the destination server only after REM SOURCE.CMD has been run REM and the database .BAK backup file and BUILD_LOGINS.SQL REM file are available ECHO Preparing to restore database backup and logins. ECHO Press CTRL C now to abort, or PAUSE REM Setup destination server ECHO Performing setup... osql /U sa /P sa_password /S destination_server -d master /i setup_destination.sql /o setup_destination.log REM Build logins ECHO Building logins... osql /U sa /P sa_password /S destination_server -d master /i i:\backups\build_logins.sql /o build_logins.log REM Restore .BAK file from source server to destination server ECHO Restoring database backup... osql /U sa /P sa_password /S destination_server -d master /i restore_database.sql /o restore_database.log REM Resync logins with users and perform misc cleanups ECHO Resyncing logins... osql /U sa /P sa_password /S destination_server -d master /i resync_logins.sql /o resync_logins.log ECHO Finished PAUSE |
| Download DESTINATION.ZIP(1 kb) |
Of course, if you do use OSQL's /P option to specify a password, you must make sure that the command files are secure. A more secure method, at the cost of automation, would be to omit the /P options, causing each OSQL line to prompt for the password.
Each of the steps in the DESTINATION.CMD will write to a log file.
Although I have just checked the above on a Maximo V4.1.1, Patch Level 05 installation, running on SQL Server 2000 SP3, I strongly advise that you verify the scripts and tailor them to suit your installation. The 'source' scripts won't change the source system, but the 'destination' scripts definitely will change the system that they're run on.
Disclaimer: as with all of the tips and scripts, be sure that you understand what they do before trying them. I make no guarantees - use the scripts at your own risk!
