The overarching parts of an SQL server which could be affected by data loss are:

• Database file; the actual file containing managed data. Incredibly complex and highly organised
• Relational Database Concept; the way the data is organised – algorithms are used to carry this out in the most efficient way to ensure the speed of the database server is optimised
• Client/Server system; the main communicator with the operating system and usually manages many databases simultaneously
• Database Management System (DBMS); the heart of the system and manages all the above elements

With many SQL servers being responsible for the organisation and management of a number of databases; customer files, accountancy systems and product development data, the crash and loss of information can be disastrous.

How data loss can occur... There are many ways data loss can occur but the main three are:

• Storage device failure
• The file system
• Damage to the file itself

If the storage device is the issue, this is often the hard drive. The key then is to find a way to make it operational so the data can be recovered and copied. This takes place either through remote intervention or by sending off site. With an issue being at file system level, this unique way of storing and organising the user files needs to be repaired and the data stream assessed. Repair can sometimes happen automatically if the OS has this utility and whilst the data may then become accessible, the data stream may become permanently corrupted. The third problem may be that data loss has taken place within the actual file. These files are very complex and it could be so damaging that the whole database may not be available because of the structure of the system itself.

Data recovery... If the unthinkable happens, all may not be lost. Data recovery specialists can now recover a great number of files and possibly even take the server back to the point just before crash or corruption occurred. Hard drives can be repaired in clean rooms off site but remote repair is often the first method attempted because of the array of electronic tool kit aids the experts now have.

Recovery will begin with locating the problem database file. If this is inaccessible because the issue is the storage device itself, work will be undertaken to extract all the data from it. If it emerges the file has been deleted or truncated, repair will take place so the file system is then correctly pointing to the data stream. If there are no pointers, the task is to search all the drive to try to find the data.

The next step is to analyse the database file and to find what can be recovered. A report is produced which will then be used to copy the data onto another SQL server database. Backups will then be created.

Be prepared... If you’ve suffered data loss on your SQL server, you’ll want to ensure there’s as little chance of it happening again as possible. The most important steps to take are

• Know – or if you don’t know - identify where the most recent backups are kept
• Ensure the backup schedule and frequency is in line with general business operation
• Take the backups offsite
• Make sure your monitoring software is watching the server and is sending alerts

Reference: http://www.datarecoveryspecialists.co.uk/blog/sql-server-recovery-information