Recovering a Failed SQL Server Master Database
The following describes the action required to recover Studies that were created in Ponemah after a SQL Server Master database failure occurs.
Failure of the master database is rare and most likely caused by a failing hard drive. It is important that a new Ponemah system is setup in order to restore the studies.
Backup SQL Server Data Folder on Corrupt System (done by customer)
All Study databases are located in the Data folder where SQL Server is installed. The location of SQL Server is dependent on both the version of Ponemah and the version of the Windows operating system.
Turn off SQL Server Service
To copy all the study database files and the Ponemah application database, the SQL Server service must be turned off otherwise some of the databases will be in use and will not copy.
Start the Services control panel from Control Panel -> Administrative Tools -> Services. Locate the SQL Server (P3PLUS) service. Select that line, right-click to access the menu options and select Stop. The Status column will change from Started to display nothing.
Backing up Data Folder for Ponemah 5.00 or Greater
This version of Ponemah uses SQL Server 2008 Express. Depending on the operating system being 32 bit or 64 bit, SQL Server is installed using the 32 bit components which dictates the root installation folder. The sample folders below assume that SQL Server was installed on drive C:\.
For 32 bit operating system the folder is:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
For the 64 bit operating system the folder is:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Using a portable hard drive, copy all the files located in the Data folder. These files will be used on the with a new installation of Ponemah to restore the Study data.
Setup a Clean Ponemah System (done by DSI)
It is important to setup a new, clean installation of Ponemah to restore studies. The version of Ponemah should be the same version that the studies were created on since there can be changes to the study database format between versions.
Once the new installation of Ponemah is created, blank study databases can know be created. As each study is created, the database is added to the Master database and to the Ponemah Studies database that the application can recognize.
Create Blank Studies
For each study that was on the failed system, a new study needs to be generated in Ponemah on the new system. This is done by going in Study -> New Study. Enter the name of the Study Name and select a study type. It does not matter if TOX Study or Non-TOX Study is selected since the study database files will be replaced with the original database files that have the correct study configuration.
NOTE: From the Data folder that was backed up on the failed system, the Study names can be obtained from the database filenames. Each study will have two files, the study database file (extension .MDF) and a study database log file (extension .LDF).
In the example below 20120913 is a study name.
Restoring Original Studies
To restore the Studies with the original databases, the study database that were backed up can now be copied over into the new Ponemah system.
The steps to do this are:
- Turn off the SQL Server service. Refer to the earlier section in this document.
- Copy all the study .MDF and .LDF from the backup device into the SQL Server Data folder. Refer to Backing up Data Folder earlier in this document for the folder location.
- Do not copy these files from the backup folder since they are not needed:
- Turn the SQL Server Service back on.
NOTE: depending on the severity of the failure, not all study databases may be able to be restored.
Backing up Studies
Ponemah now has all the studies restored. Now, backups can be made of the study database through Ponemah.
To backup each study, first the study needs to be the active study in Ponemah. From Study -> Manage Studies select the desired study to backup.
Exit this dialog and then select Study -> Backup Study. This will display a Browse dialog that allows placing the study in a backup location. This will create a study backup file with the file extension .SDYBKUP.
Any of the study backup files can know be loaded into any Ponemah system that has the same or newer version of Ponemah.
Getting Ponemah Application Log (done by DSI)
To satisfy GLP requirements for Audit logs, the Ponemah application audit log is contained in the Ponemah database in the SQL Server data folder.
The Ponemah application database may not always be accessible dependent on the severity of the failure of the system.
On the new system these are the actions needed to get the application log:
- Turn off the SQL Server (P3PLUS) Service.
- From the backup data folder copy P3Plus_500.MDF and P3Plus_log.LDF files into the newly created SQL Server data folder.
- Turn back on the SQL Server (P3PLUS) service.
- Start Ponemah
- Ponemah may not start due to the LSS_P3Password no matching the one in the Windows user account. If that is the case go to Getting Application Log Using SQL Server Management Studio to access the Application log.
- If Ponemah starts access the application log from Options -> Application Logs...
- Select Then select a data range of interest and enter a filename. This will generate an Excel file that can be archived for Audit log.
Getting Application Log Using SQL Server Management Studio
This assumes that the new Ponemah system is on a network and that it can be accessed by a remote computer that has SQL Server Management tools installed, otherwise SQL Server Management Tools will need to be installed on the same system as Ponemah.
From the system that has SQL Server Management Studio install, follow the steps below:
- Connect to the P3Plus database instance. This is the computer name followed by \P3Plus as displayed by the sample below:
- Once in the Management tool, expand Databases from the Object Browser.
- Locate the Ponemah application database which will be in the form of P3Plus_500 (with the last 3 digits designating the version of Ponemah.
- Expand that database and select Tables.
- Select the table SystemLog and right-click and Select Top 1000 Rows.
- This will generate a query window with the results.
- Change the query SELECT TOP 1000 to 1000000 to make sure that all entries are in the results.
- Add in the query SORT BY ColumnID.
- Select !Execute on the toolbar.
- The result window know contains the audit log information.
- See sample below:
- Left-click on the left-hand side of the results window to select all data in window.
- Right-click and select Copy with Headers.
- Open Excel and select paste to insert the audit log into Excel.
This completes the recovering of the study databases and extracting the Ponemah Application log.
Comments
0 comments
Please sign in to leave a comment.