I can’t imagine there are many organisations that use BMC Service Desk Express but don’t have a development / test environment. Personally, I always prefer to have three environments:
- Development: Where I am trying out new concepts, customisations, business rules etc.
- Test/Training: A permanant training environment for new staff and a staging area where new customisations can be tested and approved by the business.
- Production
Now in an ideal world your Production and Test/Training environment would be either identical or, in the case of just prior to a new release, slightly out of sync. This is normally achieved by restoring a backup of the production database over the test/training database. Whilst this is relatively easy if the Production and Test/Training environments are on the same physical network, this can become slightly more involved if this is not the case. What I want to do with this post is provide a simple backup and restore procedure that works in any SQL Server environment.
Backup
This is the easy bit – the only catch is that frequently your Test/Training environment doesn’t have the disk capacity of your production SQL server and, due to the way that Service Desk Express polls the database, the transaction logs of an SDE database can be huge! So assuming that your DB is called SDE and you are using SQL Server 2005 your code would look something like:
USE SDE
BACKUP LOG SDE WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(SDE_log,2)
GO
BACKUP DATABASE SDE TO DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSDE_YYYY_MM_DD_x.bak'
GO
where YYYY is the year, MM is the month, DD is the day and x is simply an incrementing number.
Restore
Once we get our backup from one server to the other we then need to restore it. Restoring a normal database is easy – restoring an Service Desk Express database is also easy but unfortunately requires a couple of preparation steps:
Services
Service Desk Express uses a number of Windows Services to perform essential background tasks and these services connect to the database to perform their function. You can’t restore a database without first closing the connections to it. For this reason, and only on a Test/Training or Development Service Desk Express application server, I deliberately configure these services slightly differently:
Warning: Do NOT do this on a production system!
From the desktop click Start > Run and type Services.msc and press Return. From the Services window scroll down to the SDE Indexing Service and select it. Select Actions > Properties from the Services window menu and from the Properties window change the Startup Type to Manual and click Stop. Once the service has successfully stopped click OK to return to the Services window. Repeat this process for the following services: SDE Integration Engine; SDE Job Processor – SDEBR; SDE Mail Processor – SDEBR; and SDE Push Service – SDEPPS. Close the Services window.
Now if we click Start > Run and type IISRESET and press Return we can completely restart IIS flushing out all the metadata without the services reconnecting to the database. Now we can restore our database:
RESTORE DATABASE SDE FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSDE_YYYY_MM_DD_x.bak'
GO
If you have issues with this statement the chances are that your data and log files in your production environment are on a different drive and consequently you need to use the WITH MOVE command to move them to the correct place:
RESTORE DATABASE SDE FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSDE_YYYY_MM_DD_x' WITH MOVE 'SDE_data' TO 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData', MOVE 'SDE_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData'
GO
Fixing Everything Else
If only it was that simple and then you could just log in and all would be perfect! Unfortunately it is far from the case:
Owners, Logins and Users
Your first problem is that the _SMSYSADMIN_ account needs to be the owner of the SDE database unless it is a SQL Server sysadmin. It’s your choice but the former is more secure than the latter:
EXEC sp_ChangeDBOwner '_SMSYSADMIN_'
or
EXEC sp_AddSrvRoleMember '_SMSYSADMIN_', 'sysadmin'
Your next problem is that when you restore a db from one server to another the roles and users don’t automatically sort themselves out. As such you need to match them up using the following SQL:
EXEC sp_Change_Users_Login 'Auto_Fix', '_SMDBA_'
EXEC sp_Change_Users_Login 'Auto_Fix', 'SMSYSEXEC'
EXEC sp_Change_Users_Login 'Auto_Fix', 'MGUSER20xx'
EXEC sp_Change_Users_Login 'Auto_Fix', '_SMSYSADMIN_'
If you are using Service Desk Express 9.6 or above then you also need to run:
EXEC sp_Change_Users_Login 'Auto_Fix', 'SYSTEM ADMINISTRATION'
EXEC sp_Change_Users_Login 'Auto_Fix', 'ADMINISTRATOR'
EXEC sp_Change_Users_Login 'Auto_Fix', 'SELFSERVICE'
EXEC sp_Change_Users_Login 'Auto_Fix', 'SYSTEMACCOUNT'
EXEC sp_Change_Users_Login 'Auto_Fix', 'HELP DESK'
If you are using Service Desk Express 9.2 or below then you also need to run:
EXEC sp_Change_Users_Login 'Auto_Fix', 'MAGIC'
EXEC sp_Change_Users_Login 'Auto_Fix', 'EXTERNAL_SUPPORT'
Service Desk Express obtains its SYSADMIN password by decrypting the result from the NAMSYSWORK table (< v9.60) or by decrypting the result from the registry (v.9.60 and above). So unless your SYSADMIN password is the same in Production as it is in the Test/Training you will need to update this value. The tool to update this value is the “Set System Admin Password” utility available by clicking Start > All Programs > BMC Service Desk Express > Set System Admin Password. The problem is that this application wont work unless all the Service Desk Express logins are migrated from Production! So how do you do that?
Well the easiest route is to use the DBLogins.exe available from …. This does the hard work for you BUT only works if the Production and Test/Training systems are on the same physical network. Another option is to use sp_help_revlogin – a stored procedure that is created following this knowledge base article. This works very well even if you servers are not on the same network. The only issue with this is that, by default, it will transfer EVERY login whether used by Service Desk Express or not! So an alternative approach is to use a little bit of SQL code that seems to work rather well:
WARNING: The code below will ONLY work with Service Desk Express 9.00 and above.
DECLARE cur CURSOR
READ_ONLY
FOR SELECT [CODE], 'Staff' FROM _SMDBA_._PERSONNEL_ UNION SELECT [CODE], 'Group' FROM _SMDBA_._GROUPS_
DECLARE @Code NVARCHAR(30), @Type NVARCHAR(30), @SqlStatement NVARCHAR(4000), @SysAdminPwd NVARCHAR(100)
SET @SysAdminPwd = 'Enter Your SysAdmin Password Here'
OPEN cur
FETCH NEXT FROM cur INTO @Code, @Type
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF NOT EXISTS(SELECT loginname from master.sys.syslogins where name = @Code and dbname = 'master')
BEGIN
IF @Type = 'Group'
BEGIN
SET @SqlStatement = 'CREATE LOGIN [' + @Code + '] WITH PASSWORD=N''' + @SysAdminPwd + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_ExecuteSQL @SqlStatement
END
ELSE
BEGIN
SET @SqlStatement = 'CREATE LOGIN [' + @Code + '] WITH PASSWORD=N''welcome'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_ExecuteSQL @SqlStatement
END
END
-- Once the login exists we just fix it up with the user in the db
EXEC sp_Change_Users_Login 'Auto_Fix', @Code
END
FETCH NEXT FROM cur INTO @Code, @Type
END
CLOSE cur
DEALLOCATE cur
GO
The above code is a lot simpler than it looks. Essentially all it does is gets all the users/groups from the _PERSONNEL_ and _GROUPS_ tables and then checks to see if a SQL login exists for each of them. If it doesn’t then it creates it whilst if it does then it simply fixes it up. Couple of things to point out though:
- You need to enter your SYSADMIN password as all groups passwords are the same as the SYSADMIN password.
- Any staff logins it creates will be created with a password of welcome
Once this is complete you can run the Set System Admin Password utility.
Are we there yet?
Surely we must be there now? I’m afraid not quite. So what is left. Well unless you took a backup whilst all of the Windows Services were turned off and no one had logged into Service Desk Express since a restart (sounding unlikely isn’t it) , then you will have backed up the process/meta data that Service Desk Express uses which we need to get rid of:
DELETE FROM SMSYSPROCESS
Theoretically, you can now log in (assuming everything above went according to plan) but…your business rules wont work properly, your Integration Engine packages will fail and if you are using Web Services then they will fail too! A couple more housekeeping tasks plus their explainations:
DELETE FROM dbo.NAMSYSSERVERS
DECLARE @OldAppServerName NVARCHAR(100), @NewAppServerName NVARCHAR(100), @NewIPAddress NVARCHAR(100)
SET @OldAppServerName = 'Enter your OLD app server name here'
SET @NewAppServerName = 'Enter your NEW app server name here'
SET @NewIPAddress = 'Enter your NEW app server IP address here'
DELETE FROM dbo.NAMSYSPROPERTIES WHERE [NAME] LIKE 'namMBLQParams|' + @OldAppServerName + '%'
UPDATE dbo.INTEGRATION_ENGINES SET SERVER_NAME = @NewAppServerName
UPDATE dbo.WSSERVERS SET NAME = @NewAppServerName, IPADDRESS= @NewIPAddress WHERE NAME = @OldAppServerName
After running this lot you will need to manually start the Service Desk Express Windows Services described at the top of this post and that will automatically fix up your business rules and stop your Integration Engine and Web Services packages failing. You should now be good to go.
Summary
So as you can see it is somewhat convoluted transfering a database from one server to another and hopefully this post has gone someway to providing a solution to it. Hope it helps. As always any comments, positive or negative, are always welcome.
JOATIT 