SQL FAQ - What do I need for a successful Microsoft SQL Server database restore?
Data is the lifeblood of the modern company, easily the most valuable asset after your employees. And being that the majority of your actionable data is stored in databases, they need to be protected against loss, theft or corruption.
But in the event that something terrible happens, what do you need to have handy in the event that your SQL Server database fails?
1. A full backup
Just like any other file on the network, the key to a successful database recovery is to have a full, current backup of the MDB and MDF files. You should be taking incremental copies of the database files at least once a day, combined with a full backup at least once a week.
2. A log file backup
A 100% accurate database restore is reliant on having a completely up-to-date set of log files that corresponds with data access and update operations. For industries that are heavily regulated, like finance and banking, the log files are essential to prove compliance.
Microsoft is keen to point out that you must have “enough” log backups available for a restore sequence, including the tail-log backup, before beginning the process.
3. Sufficient spare disk capacity
In order to test the restored data is correct, you will need to restore the database elsewhere first. This will then give you a chance to test the restored data and ensure that there is no corruption, and that you have an up-to-date set of records that gives a true indication of state before the crash/deletion/corruption.
The issue of logs
SQL Server maintains extensive logs to record who accessed records, what was changed and when. These logs provide an accurate record of every single database activity, which further underscores the accuracy of the data stored in the database.
SQL Server logs are an essential auditing tool, both internally for ensuring database integrity, and externally for tracking user actions and ensuring that records have not been tampered with. Which is why the built-in SQL Server recovery tool insists on having a copy of the log files to perform anything other than a Simple backup.
No log files means no option when it comes to restoring a SQL Server database, such as database mirroring, lossless media recovery or point-in-time restores. Because of the way SQL Server works, any transactions that remain incomplete at the point of backup will be rolled back automatically when the backup is restored. Without the ability to restore transaction log files you will also find that any work carried out on the database since the last backup will need to be repeated.
Configuring a backup routine
It goes without saying then that a good backup routine will save copies of database files and transaction logs for maximum flexibility in the event of a disaster. The built-in task scheduler contains useful guidance on creating a regular log backup job using the SQL Server Agent. Don’t forget to include the tail log which contains details of any log records which have not yet been backed up, keeping the log chain intact.
What happens if the logs are not backed up
In the event that log files are not properly backed up, or have become corrupted, you face two choices:
- Perform a simple restore and manually recreate any missing records.
- Attempt a low-level data restore to capture data direct from the disk.
As mentioned above, log files are a critical compliance issue for some industries, so option 1 is unsuitable. In fact, few businesses can afford to recreate lost work, leaving option 2, low-level file recovery, as the only viable choice.
Although there are file recovery tools available to try and locate these missing log files, the sensitive nature of compliance means that your business can and should seek professional assistance with the data recovery operation to reduce the risk of permanent data loss or corruption.
Remember – to perform a successful SQL Server restore, you will need:
- A full backup of the database.
- A full backup of the transaction logs.
- Sufficient disk space to accommodate the restored files for testing.
Access to a file recovery specialist if any of the backup files are missing or corrupt.