Restoring an OutSystems SQL Server database backup into another server

Sometimes, if you have an OutSystems Server platform installation (either running or freshly installed) you may want to clone an existing environment from one server  to another.

My objective with this post is to have a detailed recipe on how to quickly clone a production Server to a development server.

This document assumes that you have already a full backup file from the  OutSystems SQL Server database.

In Italic, you can find actions that were copied from Outsystems site without confirmation or that didn’t need any change or completion from me.

I’m using Outsystems 7, SQL server 2008 R2 and SQL Server Management Studio (SMS).

1. Stop all Outsystems services and IIS on the Server platform.
   – Control panel/Internet Information services/[your server]/Web Sites/Default Web Sites/Stop.
   – Outsystems/Administration Tools/Stop  Services(Confirm in Control Panel/Services that all services are stopped).
2. If there is already an OutSystems database and is still in use, kill the SQL processes that connect to it.
   Try to get the Outsystems database offline and then check if any Outsystems or other services that use SQL Server are still running.  
3. Restore the OutSystems database backup into the SQL Server database instance.  ( [instance]/Databases/restore Database…).
   It’s easier if both SQL servers have the same versions.
   If both SQL servers doesn’t have the same version, you have two options:
   1. Upgrade the lower SQL Server version  to the version greater than or equal to the higher SQL Server version .
   2. Script to a file the database Objects and restore them on the server (to avoid).
4. The below restore options had worked for me:
   RESTOREDATABASE [outsystems]
   FROMDISK=N’C:\…[your path]…\outsystems.bak’
   WITHFILE= 1,
   MOVEN’outsystems’TON’C:\…[your path]…\outsystems.mdf’,
   MOVEN’outsystems_log’TON’C:\…[your path]…\outsystems.LDF’,
   NOUNLOAD,
   REPLACE,
   STATS= 10
   
5. Delete the OSadmin, OSruntime and OSlog users from the OutSystems catalog security users
   (you can find them in [instance]/Databases/outsystems/Security/Users).
   When using SQL Server 2005, you’ll need to delete the associated schemas first.
   When using SQL Server 2008, this is automatic and you will be asked if you want to do so, by the SMS.
   If exists any objects ( views, tables, stored procedures, etc.) in the OutSystems catalog dependent of any of these users change the object owner to dbo (evaluate the application level impact of this change)
6. Delete the OSadmin, OSruntime and OSlog users from the server security logins  (you can find them in [instance]/Security/Logins).
7. Clean Up of Selective Deployment Zones (not applicable in this case, otherwise see documents listed in the end of the Post)
8. Confirm in the ossys_server table of the OutSystems catalog that there are no IP addresses pointing to different environment servers. If they are, please change the enable column to false. There must be one that points to the Server (or localhost if it is a single server installation) and has the enable column at true. This will ensure deployment in at least one Server and prevent deployment to other servers other then the one being installed, if configured.
   (use command delete from ossys_server;) 
9. Run the OutSystems Server configuration utility (you can find it in Start/Programs/OutSystems/Administration Tools/Configuration Tool).
   1. Make sure any SQL Management or Enterprise Manager applications are closed to avoid table locks.
   2. Setup the SQL server, Server Instance  and catalog to the just restored one.
   3. Click on “Grant permissions” to grant the permissions for the users OSadmin, OSruntime and OSlog. A login dialog box pops up requesting an SQL or Windows login, depending of the login type you have in your database. Use your administrative SQL or Windows user login (or any other User with administrative privileges) and click OK. Do this and it will create the OSadmin, OSruntime and OSlog users on the SQL Server and set the appropriate permissions onto the just restored database catalog.
   4. Confirm the user’s login by clicking the links Test Connection in all users.
10. Then click the DB Schema “Create/Upgrade Database” button and wait until a message box with “Platform Database model was successful” shows up.
11. Click the “Apply and Exit” button to exit the Server configuration utility. Before closing, the application will perform some action and ask you to do some of the tasks you will find listed next. It’s up to you if you want it to do those action for you.
12. Start IIS on the Server platform.
    1. Internet Information services/[your server]/Web Sites/Default Web Sites/Start.
13. Start Deployment Controller Service, Deployment Service and Log Service.
    Do not start Scheduler Service nor SMS Connector Service at this point.
    1. Control panel/Administrative Tools/Services.
14. Open the “Service Center” in a browser and in [Administration/Environment  Configuration] change:
    Hostname: [name of your new server]
    (The name that is typed in the URL of the internet browser to access the server, i.e.: http://www.outsystems.com/ or http://dbserver/)
    Running Mode: Development or Production
15. Confirm the Servers list on the Server Configuration page [Administration/Front-end Servers]. There should be only listed the server of the current installation and not any other IP address from another environment or installation. Delete any extra Server from the list.
16. In the following points, if you are having problems when publishing the eSpaces, then restart the OS services (point 12). The services will then assume the new name configurations. Just in case, restart the services.
17. In case of a environment replication, check every Web Reference effective value in the eSpace details page and the Site Properties for URLs or values pointing to another environments and installations services or servers. Change them accordingly.
18. If any Web Reference effective value was changed you will need to republish the eSpace in question.
19. In case of a fresh Server install, or the database brings a different factory, then you should publish all eSpaces to properly deploy them in the application server.
20. To republish all the system eSpaces, go to Factory/Solutions/System Components and click the publish button on the last published version.
21. To republish a particular eSpace, go to Factory/eSpaces/[your eSpace] and click the publish button on the last published version.
22. Finally, if all the applications are running and there’s no web references or other integrations linking to different environments, its safe to start the Scheduler Service and the SMS Connector Service.