Data recovery from Microsoft SQL server databases is based in three fundamental models comprising full recovery, bulk recovery and simple recovery. The need for database recovery arises when the database server disk drive gets crashed or the working database gets corrupt.
Full recovery: – Full database recovery is recommended in both the cases as the users may need access to records from current and previous financial year data. However this process needs to be supported by frequent backup of the database from the server. It is due to the reason that full recovery is done only up to the point of latest backup.
Bulk recovery: – This type of recovery needs backup logs of the previous operations performed. The technician will be able to select the end point up to any desired date based on the log file. For example, assume that the SQL server database has lost records from 1st November till 15th November 2015, but all the other records are intact. Then the technician needs to input the backup log files between these two dates so that only those backup records in this period will be restored to the database.
Simple recovery: – This is similar to full recovery model, except that logs are omitted from the backup procedure.
Our team of database recovery experts has the expertise to perform all the three types of database recovery based on your requirements. We can also configure your database server to take automated backup with logs to specific locations in a backup server or an external medium like USB HDD, DVD or tape drive.
Options with database recovery
The default format is to overwrite the existing records, indexes, primary keys, foreign keys and other Meta data related to tables, queries and other objects in the database when data is recovered from a backup file. If you wish to avoid overwriting, you need to uncheck the box in the recovery dialog box.
It is possible to preserve the replication settings with an option called KEEP_REPLICATION. This option is chosen when the technician wishes to duplicate copies of the records to track changes made by each user in it. If two backup versions are same, the system can inform the backup operator and it can be avoided. While restoring data the ADMIN user can restrict access to certain confidential tables and records to specific users by marking them as RESTRICTED. This option is highly useful in preserving the hierarchy of accessibility to information based on user roles and responsibilities.
Uncommitted transactions: – Any CREATE_RECORD or UPDATE_RECORD in the SQL database has to be completed with COOMIT_TRANS command to write it into the table as a record. When a transaction remains uncommitted, the transaction is said to be incomplete. It is possible for the ADMIN user to discard all such transactions by choosing the option to ROLL_BACK uncommitted transactions during recovery process. It saves a slot of disk space. With this option the database is ready to use immediately after recovery.
EDITABLE transactions: – When the users need to make EDITS to incomplete transactions and commit them, the ADMIN chooses the option to recover all the uncommitted records also. In this case the database still remains non operational in nature. It is possible for users to create additional transaction logs before committing them.
Read only database: – When the ADMIN chooses this mode, no transactions can be committed into the tables in the database by the users. All uncommitted transactions are unconditionally rolled back, but their status is saved into a log file for the user reference.
Apart from the native SQL Server recovery tools, it is possible to use external recovery tools for performing the same tasks. But all the procedures must conform to certain standards related to SQL server databases. These rules are related to multiplexing, encryption, duplication, block-size, stripes and infrastructure configuration.
Multiplexing: – This process clubs multiple data channels into a single channel. This is done for increasing the data backup and restore speed. This is a legacy standard which remained applicable during the days of tape drive backup. Today’s standards recommend avoiding this method for the simple reason that it can prevent the option for De-duplication when data is restored from backup.
Encryption: – Encryption of records enables creation of unique tables during backup process. When this option is given to the tables by encryption software, it removes the ability of de-duplication from the system.
Block size: – The default block size 512 bytes. This is said to be most optimum for data-domain systems. But it possible to change the value from 512 bytes up to 64Kili bytes by the ADMIN during backup and restore operations.
Stripes: – Stripes refer to the number of parallel data channels which can be created during the backup process. More number of stripes means increased data backup speed. However the maximum number of stripes depends on the capacity of storage device. The ADMIN user will set stripe size based on the storage media parameters.
Infrastructure: – Performance attributes should be considered while creating and editing databases and log files. This process can directly affect the efficiency of backup process. Data transfer needs are to be maintained at a specific rate which should be equal to the bandwidth of the network.
Data transfer speed: – backup to single device used to be the practice so far by the standard SQL server systems. But the need to increase the speed of backup has made it mandatory to use multiple devices for backup. This can increase the speed of backup up to 170MB/Sec. While restoring the data from backup devices it is possible to maintain the same speed.
Replication: – Replication of backup is a recommended process when the users wish to maintain options for recovery in case of disk crashes. This process needs a dedicated replication server to which all the backup copies can be replicated once at the end of working day. The frequency of replication needs to be determined by the ADMIN user and it can be automated.