Solved

Pervasive SQL 8 Database recovery from Log files

Posted on 2006-06-12
7
1,068 Views
Last Modified: 2012-06-27
My pervasive SQL 8 server hard drive crashed yesterday (June 11, 2006). I made a system backup back in May 22nd 2006. I successfuly restored the system from the backup but the transactions between May 23rd and Yesterday are gone.

I did a disk recovery of the failed hard drive but the only data successfully recovered were the Log Files in the PVSW\Bin\MDKE\Log directory. Are these the transaction logs? If so, can I use these logfiles to restore the lost data and commit them to the database? Any help is much appreciated.
0
Comment
Question by:aznxbandit
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:Julianva
ID: 16892057
there is a tool called sql log rescue - ive downloaded and used the trial version its works very well

search for it on the net.
0
 

Author Comment

by:aznxbandit
ID: 16892222
Julian, I downloaded the sql log rescue but it looks like it  only works on Microsoft SQL.
I'm using PervasiveSQL v8. Any other suggestions?
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 16893012
A few questions:
Did you have "Transaction Logging" or "Transaction Durability" turned on?  
Did you have "Archival Logging" turned on?
0
 

Author Comment

by:aznxbandit
ID: 16895733
Yes. I see that all of the above are turned on.
0
 
LVL 18

Accepted Solution

by:
mirtheil earned 500 total points
ID: 16898981
I found the following in the PSQL docs.  Hopefully it'll help.  If it doesn't then I have a feeling you might be out of luck in terms of restoring more than what you've got backed up.
Here's what I found:
Using Archival Logging
This section explains the procedures you must follow to set up Archival Logging, make backups, and restore data files. It is divided into the following sub-topics:

General Procedures
Setting up Archival Logging
Roll Forward Command
General Procedures
For Archival Logging to work properly, you must follow a clearly defined procedure to set it up, and another procedure in the event that a restore from backup is necessary.


--------------------------------------------------------------------------------
 Caution
If any steps of the procedures are omitted or compromised, you may not be able to restore your data to its pre-crash state.
--------------------------------------------------------------------------------

 To use Archival Logging properly

Turn on Archival Logging, if it is not already in effect. See Setting up Archival Logging for the detailed set-up procedure.
Shut down the database engine.
Backup the data files.
After a successful backup, delete all existing archival logs.

--------------------------------------------------------------------------------
 Caution
Delete the corresponding log files before you resume working with the data files. Synchronizing the backup data files and the corresponding log files is a critical factor of successful recovery.
--------------------------------------------------------------------------------

Restart the database engine.
 To restore data files from backup and apply changes from the archival logs


--------------------------------------------------------------------------------
 Note
You cannot use this procedure to roll forward the archival logs if you experienced a hard disk crash and your archival logs and data files were both located on the lost hard disk.
--------------------------------------------------------------------------------

When the computer re-starts after the system failure, ensure that the database engine is not running, and ensure no other database engine is accessing the data files you wish to restore.
Restore the data files from backup.
Start the database engine, ensuring that no applications of any kind are connected to the engine.

--------------------------------------------------------------------------------
 Caution
It is crucial that no database access occurs before the archival logs have been applied to the data files. Make sure no other database engine accesses the files. You must roll forward the archival logs using the same engine that encountered the system failure.
--------------------------------------------------------------------------------

Issue the Roll Forward command as described in Roll Forward Command .
After the Roll Forward completes successfully, stop the database engine and make a new backup of the data files.
After you have successfully backed up the data files, delete the archival log files. You may now re-start the database engine and allow applications to access the data files.
Setting up Archival Logging
Setting up Archival Logging requires two steps:

turning on the Archival Logging feature
specifying the files to archive and their respective log files

--------------------------------------------------------------------------------
 Note
To perform these procedures, you must have full administrative permissions on the machine where the database engine is running or be a member of the Pervasive_Admin group on the machine where the database engine is running.
--------------------------------------------------------------------------------

 To turn on Archival Logging

Click Control Center from the Pervasive program on the Start menu.
In Pervasive PSQL Explorer, expand the Engines node in the tree (click the expand icon to the left of the node).
Right-click on the database engine for which you want to specify archival logging.
Click Properties.
Click Data Integrity in the tree to display the settings for that category of options.
Click Archival Logging Selected Files.
Click OK.
A message informs you that the engines must be restarted for the setting to take effect.

Click Yes to restart the engine.
 To specify files to archive

You specify the files for which you want the MicroKernel to perform Archival Logging by adding entries to an archival log configuration file you create on the volume that contains the files. To set up the configuration file, follow these steps:

Create the directory \BLOG in a real root directory of the physical drive that contains data files you want to log. (That is, do not use a mapped root directory.) If your files are on multiple volumes, create a \BLOG directory on each volume.
For example, if you have data files located on C:\ and D:\, and both drives are physical drives located on the same computer as the database engine, then you would create two BLOG directories, as below:

C:\BLOG\

D:\BLOG\

 

--------------------------------------------------------------------------------
 Note

On Linux, the log directory must be named blog and must be
created in the directory specified by the PVSW_ROOT environment
variable (by default, /usr/local/psql).

--------------------------------------------------------------------------------



In each \BLOG directory, create an empty BLOG.CFG file. You can use any text editor, such as NotePad, to create the BLOG.CFG file. On Linux, the file must be named blog.cfg (lowercase).
In each BLOG.CFG file, create entries for the data files on that drive for which you want to perform Archival Logging. Use the following format to create each entry:
\path1\dataFile1[=\path2\logFile1]

 
   
 path1  The path to the data file to be logged. The path cannot include a drive letter.  dataFile1  The name of the data file to be logged.  path2  The path to the log file. Because the log file and the data file can be on different drives, the path can include a drive letter.  logFile1  The name of the log file. If you do not specify a name, the default value is the same directory and file name prefix as the data file, but replace the file name suffix with ".log". You may specify a different physical drive, so that the log and the data files are not on the same drive. Each data file being logged requires a different log file.  




 
A single entry cannot contain spaces and must fit completely on one line. Each line can contain up to 256 characters. If you have room, you can place multiple entries on the same line. Entries must be separated by white space. You must terminate the end of the line with a Return character.


 

--------------------------------------------------------------------------------
 Caution

You must use a different log file for every data file that you
wish to log. If you use the same log file for more than one data file, the
MicroKernel cannot use that log file in the event that a roll-forward is
needed.

--------------------------------------------------------------------------------



 
If you do not provide a name for a log file, the MicroKernel assigns the original file name plus a.LOG extension to the log file when you first open it. For example, for the file B.BTR, the MicroKernel assigns the name B.LOG to the log file.


 

--------------------------------------------------------------------------------
 Caution

You are not required to log every file in your database.
However, if your database has referential integrity (RI) rules defined,
you must log all or none of the files involved in each RI relationship. If
you log only a sub-set of the files involved in a given RI relationship,
rolling the archival logs forward after a system crash may result in
violations of your RI rules.

--------------------------------------------------------------------------------



Examples
The following examples show three sample entries in the BLOG.CFG file on drive C. All three entries produce the same result: activity in the file C:\DATA\B.BTI is logged to the file C:\DATA\B.LOG.

\data\b.bti

\data\b.bti=\data\b.log

\data\b.bti=c:\data\b.log

The next example directs the engine to log activity in the file C:\DATA\B.BTI to the log file D:\DATA\B.LGF. This example shows that archival log files do not have to reside on the same drive as the data file and do not require the .LOG extension. (The .LOG extension is the default.)

\data\b.bti=d:\data\b.lgf

 

--------------------------------------------------------------------------------
 Tip

Writing the log to a different physical drive on the same computer
is recommended. If you experience a hard disk crash, having the log
files on a different physical disk protects you from losing your log files
and your data files at the same time.

--------------------------------------------------------------------------------



The next example shows a BLOG.CFG file that makes the MicroKernel log multiple data files to a different drive (drive D:), assuming this BLOG.CFG file is on drive C:

\data\file1.mkd=d:\backup\

\data\file2.mkd=d:\backup\file2.log

\data\file3.mkd=d:\backup\file3.log

Roll Forward Command
The Btrieve Maintenance utility (GUI or BUTIL command line) provides a command allowing you to roll forward archival log files into the data files. The BUTIL -ROLLFWD command recovers changes made to a data file between the time of the last backup and a system failure. If a system failure occurs, you can restore the backup copy of your data file and then use the BUTIL -ROLLFWD command, which applies all changes stored in the archival log to your restored data files. Do not use this command unless you have restored data files from backup.


--------------------------------------------------------------------------------
 Note
You cannot take advantage of the ROLLFWD command unless you both enable the MicroKernel's Archival Logging Selected Files option and back up your files before a system failure occurs.
--------------------------------------------------------------------------------

You can also use the ROLLFWD command to produce an output file of logged operations. The ROLLFWD command can produce the output file either before you roll changes forward or at the same time as the roll forward.

You can roll forward a single file, all data files on a volume, all data files on a drive, or a list of files, volumes, and/or drives.

Using the GUI
To run the Cache Engine, access the Pervasive commands on the Start menu and click Cache Engine.
Within the Maintenance window, choose Data 4 Roll Forward... The Roll Forward dialog box (Figure 8-1) appears.
Figure 8-1    Roll Forward Dialog
   



Select the specific operation type: single file, list of files, volume name, or drive letter. When you select either volume name or drive letter, you must insert a back slash (\) or forward slash (/) at the end (for example, \\server\vol1\ or D:\).
You can generate a log file, called a dump file, of all the Btrieve operations required to perform the roll forward tasks.
By default, this file is not created. Select the Generate Dump File check box to generate a file. You can also specify the following options.

Table 8-4    Roll Forward GUI Options  Only Create Dump File  Indicates that only the dump file is to be created, and the roll forward operation is not to be performed.  
Dump File Name  Contains the name of the dump file, which must begin with a slash and not contain a drive letter or server/volume name.  
Data Buffer Length  Indicates the number of data buffer bytes to write to the dump file for each Btrieve operation.  
Key Buffer Length  Indicates the number of key buffer bytes to write to the dump file for each Btrieve operation.  
Display Numbers as HEX  If you select this option, the numbers in the dump file output are formatted as hexadecimal. If you do not select this check box, the numbers are displayed in decimal format.  
Verbose  Includes additional information like user name, network address, and time stamp in the dump file.  



--------------------------------------------------------------------------------
 Note
If the key buffer or the data buffer is not an input parameter for the particular Btrieve operation, nothing is written to the dump file.
--------------------------------------------------------------------------------

Click Execute to generate the dump file and/or perform the roll forward operation. If the data is valid, the Roll Forward Status dialog box (Figure 8-2) appears.
Figure 8-2    Roll Forward Status Dialog
   



As files are processed, they are added to the scrolling list box which displays the file name and the Pervasive PSQL status code returned from the roll forward operation.

If an error occurs during processing, the Roll Forward Continue on Error dialog box appears. This dialog box allows you to continue without being prompted again, to continue and be prompted again, or to stop processing files.

Figure 8-3    Roll Forward Continue on Error Dialog
   



Using the Command Line
This section explains the syntax for the command line usage of Roll Forward.

BUTIL -ROLLFWD <sourceFile | volume | drive | @listFile>
[</L[dumpFile] | /W[dumpFile]> [/T<dataLength>]
[/E<keyLength>]       [/H] [/V] [/O<ownerList | owner>|*]]
[/A] [/S]

 
   
 sourceFile  The fully qualified name of a data file for which to roll forward changes. For Windows 32-bit platforms, you do not need to specify the name of the path if the data file resides in the same directory as your current directory.  volume  A volume for which to roll forward changes. End the volume name with a backslash or forward slash, as in SYS:\, //SERVER/SYS/, or \\SERVER\SYS:\.  drive  A drive letter for which to roll forward changes. End the volume name with a backslash (\) or forward slash (/), as in F:\ or F:/.  listFile  The fully qualified name of a text file containing the paths of files, volumes, or drives for which to roll forward changes. Separate these paths with a carriage return/line feed. If the Maintenance utility encounters an error, the utility stops rolling forward the current file, but does not roll back the changes already made. If you specify the /A option, the utility continues rolling forward with the next file.  /LdumpFile  Produces an output file, but does not roll forward.  /WdumpFile  Rolls forward and produces an output file.  dumpFile  The file name of the output file to which the Maintenance utility writes a list of logged operations. The default is \BLOG\BROLL.LST, relative to the root of the physical drive. The file name cannot contain a drive letter or volume name and must start with a forward slash (/) or backslash (\). The Maintenance utility places the file on the same volume as the BLOG.CFG file.  /TdataLength  Specifies the length of the operation's data buffer to write to the output file. If you do not specify this option, the utility does not include data buffer contents in the output file.  /EkeyLength  Specifies the length of the operation's key buffer to write to the output file. If you do not specify this option, the utility does not include key buffer contents in the output file.  /H  Instructs the utility to show numbers in the output file in hexadecimal notation. If you do not specify this option, numbers in the output file are in ASCII format. This option affects the format of the Entry Count, Op Code, Key Number, and Data Length fields.  /V  Instructs the utility to include additional information (such as the user name, network address, and time stamp) in the output file.  /O  Specifies the owner name of the data file, if required. An owner name is required if you request an output file of logged operations and the backup copy of the data file has an owner name for read-only access. If more than one file has an owner name, the respective owner names must be separated by commas. See Owner Names for more information.  /A  Specifies that if you are rolling back more than one file and the Maintenance utility encounters an error, the utility continues rolling forward with the next file.When you do not specify this option, the utility stops rolling forward if it encounters an error. The utility does not roll back the changes already made.Note: When you use the /A option, you might want to redirect output to a file, as described in Redirecting Error Messages and Command Files .  /S (NetWare only)  By default, the Maintenance utility stops at each full screen of output and waits for a keystroke before continuing. With the /S option, the utility continuously scrolls output on the screen. You cannot use /S on the command line if you specify a command file, but you can specify /S with a command inside a command file.  





 

--------------------------------------------------------------------------------
 Note

If the key buffer or the data buffer is not an input parameter for
the particular Btrieve operation, nothing is written to the dump file.

--------------------------------------------------------------------------------



Examples
Example A The following example recovers changes to the CLASS.MKD file from the default archival log and log location.

butil -rollfwd sys:\pvsw\demodata\class.mkd

Example B This example recovers changes and outputs them to all files on the sys: volume with the following options:

use default dump file
dump 32 bytes of the data buffer
dump 4 bytes of the key buffer
dump in hex mode
butil -rollfwd sys:\ /W /H /T32 /E4

Example C The following example does not perform roll forward but only outputs the changes to the files listed in files.txt with the following dump options:

use sys:\temp\files.lst as the dump file
use verbose mode
data files have owner names: own123 and own321
do not dump data or key buffer
butil -rollfwd @sys:\temp\files.txt /L\temp\files.lst /
V /Oown123,own321

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now