Product:
Cognos Controller 10.3
Microsoft Windows 2012 R2 Server Standard
Microsoft SQL 2016 database server
Problem:
How copy a controller database to test database?
Solution:
Take a backup of your existing controller prod database in Microsoft SQL.
You can export the backup by use of Microsoft SQL Server Management Studio or from a command line on the SQL Express server with this command:
SqlCmd -U admin -P cognos123 -S .\SQLEXPRESS -Q “BACKUP DATABASE [ControllerLive] TO DISK=’C:\backup\ControllerLive.bak’ “
Above is if you use SQL login admin with password cognos123, and the database name to take backup of is named ControllerLive. Enter the command in Notepad++ and save as a ANSI cmd file – that you then run from an administrative command prompt on the SQL server.
SqlCmd -E -S .\SQLEXPRESS -Q “BACKUP DATABASE [ControllerLive] TO DISK=’C:\backup\databases\ControllerLive.bak’ “
Above is when you use Windows authentication and the logged in Windows user on the SQL server is executing the SQL backup. You need to update the path to reflect your environment.
This BAK file is then copied to the server where you have your test environment.
Best is to restore the BAK file with use of Microsoft SQL Server Management Studio. But if you know the logical name of the database you can use a script like this:
SqlCmd -E -S .\SQLEXPRESS -Q “RESTORE DATABASE [ControllerTEST] FROM DISK=’C:\backup\databases\ControllerLive.bak’ WITH RECOVERY, MOVE ‘ControllerLive’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\ControllerTest.mdf’, MOVE ‘ControllerLive_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\ControllerTest.ldf’ “
You need to update the path to the correct ones for your environment.
After the Controller Database is restored you in most cases need to connect the SQL login with this SQL query that you run in Microsoft SQL Server Management Studio:
Sp_change_users_login ‘update_one’,’frango’,’frango’
Replace frango with the login name you use for your controller database.
Now the controller database is setup as ControllerTest, you need to go to the Cognos Controller server and add this database to Controller.
Inside IBM Cognos Controller Configuration mark the Database Connections tab and click on the NEW icon.
Enter database type to SQL Server.
Enter name to ControllerTest (or what you find most useful for the user to see)
Enter Provider to SQLNCLI11.1
Enter User ID to frango (or the SQL name to login to the database)
Enter Password to xxxxx (the password you have set on the SQL login)
Enter Initial catalog to ControllerTest (the name of the database in SQL)
Enter Data source to servername of the SQL server (and do not use FQDN)
Click on save.
Click on Check.
Click on Run, to start the DBCONV program.
Click on Connect to see version of the database.
If the Current version and upgrade to version number is not the same for Controller DB, then click on upgrade button.
Click on Close.
Go to Report Server and click on the green Check icon.
Then Controller will check if the new database exists in Cognos Connection.
If not, a REPAIR button will appear inside Cognos Controller Configuration.
Click on the REPAIR button (this will only work if the Windows user that is logged into the controller server is system admin inside Cognos Connection).
Report server connections have been created successfully.
Now you should restart the Windows server where Cognos controller are to ensure that the batch routine recognize the new database.
Then when this is done the super user of Cognos Controller should start their Controller client and login to see that the new database is working.
More information:
http://www-01.ibm.com/support/docview.wss?uid=swg22001683
https://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/
http://www-01.ibm.com/support/docview.wss?uid=swg21347969&aid=5