SQL databases act as the lifeline for modern-day business applications. Their use is widespread across assorted data storage, management, and retrieval industries. However, despite the accompanying security measures to safeguard data, SQL database corruption remains a possibility.
The damage may occur because of several reasons, such as system crash, cyber attacks, hardware failure, power outages, or even human failures. To recover from this situation, having healthy backups is essential. Let’s learn more about it.
Type of SQL Database Backups
Maintaining regular and healthy backups is an ideal solution to protect your database from corruption. You can have multiple types of SQL Server backups, useful for different situations. Here are the widely used ones:
Full Backup
A full SQL server backup makes a copy of the entire SQL database. This is the most straightforward type of SQL server backup available since it includes:
- All Meta data available in the relational database, such as name, file paths, creation date, database options, and more
- Used data pages of the data files
- A part of the transaction log
Other types of SQL server backups use a full SQL server backup as the base for advanced backup strategies. Since it involves taking a complete backup, the SQL Server is likely to consume considerable disk space. Therefore, the best time for a full backup is when you have the least workload, such as at night.
Differential Backup
This backup type uses a full backup as the baseline to backup the new and updated files uploaded since the recent full backup. Every consequent differential backup compares the dataset with the full backup taken initially. It leads to changes in the log files, schema objects, etc. since the last backup. As it takes place only on the updated files, it takes less time than a full backup. To make sure that a differential backup does not exceed a full backup, running a full backup at regular intervals is essential
Transaction Log Backup
It takes a backup of the transaction log files of a database. The log file under discussion stores all the changes and transactions taking place in a SQL Server database that are so far uncommitted. A transaction log backup can perform point-in-time recovery to restore data from a corruption or accidental data loss up to a specific time.
It is a suitable option for real-time backup and during the process, it also preserves the entire data history so that every transaction becomes recoverable. Backing up transaction logs becomes essential in cases where you use a full or bulk-logged recovery model.
Pre-recovery steps before proceeding with the backups
If the database becomes corrupt, initiating a systematic recovery process immediately becomes crucial. Following some pre-recovery steps is equally important. This will minimize data loss and lead to successful restoration.
- Identify the level of corruption, whether minor or severe, and the common symptoms of database corruption
- Check if SQL Server is maintaining the error logs automatically by using the below T_SQL command:
EXEC sp_readerrorlog
- Check database consistency by using the DBCC CHECKDB command.
DBCC CHECKDB (‘Database_Name’) WITH NO_INFOMSGS, ALL_ERRORMSGS;
In case it discovers any error, it will suggest a relevant fix, such as REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD options depending on the error type.
- Find out if any full, differential or transaction log backup already exists in your backup storage location.
To check this, open SQL Server Management Studio, open your database and then navigate to Tasks > Restore > Database
- If database corruption has stopped the transaction, ensure that no further loss occurs by taking a transaction log backup.
Data Recovery using full backup
Here are the steps to recover data using a full database backup with the help of SQL Server Management Studio (SSMS). We will start by creating a full backup.
- Launch SSMS and connect to your instance
- From the object explorer on the left, expand Databases
- Select the database that you want to back up
- Right-click the selected database > Tasks > Backup
- In the resulting Backup Database dialog box, under Source section, select Database
- Choose Backup type as ‘Full’ from the dropdown list
- Select Database as Backup Component
- From the Destination section, select Disk option to backup your database
- Accept the default filename given in the textbox or click Add to choose a different filename
- Click OK.
This will create your full backup for data recovery. Now, to restore the database from full backup, navigate to the following:
- Object Explorer > Databases > Right-click Selected Database
- Click Tasks > Restore > Database
- In the Restore Database dialog box, select the source database and the destination database
- Click the Restore column under Backup sets to restore
- In the Options page, make the necessary changes as per your restore requirements
- Click OK to restore the database.
Data Recovery using differential backup
We will start by creating a differential database to restore a corrupt database from a differential backup. As a major prerequisite for this task, you will need a previous full database backup. If no prior backup exists, perform a full database backup first before creating differential backups. Here are the steps:
- Connect to your SQL Server instance.
- In the Object Explorer, expand databases
- Select the Database that you want to back up
- Right-click the selected database
- Click Tasks > Backup to get the Back Up Database dialog box
- Verify the Database name from the Database list box
- Next, from the Backup type list box, select Differential
- In the Backup component section, click Database
- Accept the default backup filename given in the text box, or click Add to choose a different backup filename
- Click OK to start the backup process.
This will create your differential backup for data recovery. Next, to restore the database from your differential backup, navigate to the following:
- Object Explorer > Databases > Right-click Selected Database
- Click Tasks > Restore > Database
- In the Restore Database dialog box in the General page, click Database to choose the Database from the dropdown list
- Alternatively, click Device to Add a new database backup file.
- In the Destination section, choose the Database you want to restore if the already populated database is a different one.
- In the Backup sets to restore grid, choose the backups you want to restore
- Next, on the left pane, click the Options page
- Make the necessary changes according to your requirements and click OK.
- Click the Restore column under Backup sets to restore
- Click OK to restore the database.
Data Recovery using transaction log backup
Before creating a transaction log backup, make sure you have at least one full backup. After that, you can back up your transaction log any time. Admins recommend creating frequent log backups to truncate the transaction log and reduce potential data loss. Before you start taking log backup, you need to have database engine permissions and backup device permissions. Once you get the permission, proceed with the following steps to create a transaction log backup.
- Connect to your SQL Server instance
- In the Object Explorer, expand Databases
- Select the database for which you want to create a transaction log backup
- Right-click the selected database
- Click on Tasks > Backup
- In the resulting Backup Database dialog box, verify the database name or choose a different database from the dropdown list
- Make sure the recovery model is either FULL or BULK_LOGGED.
- Click the Copy Only Backup checkbox to create a copy-only backup.
- Click the Backup options page
- Accept the default backup name as given in the Name text box, or write a different name
- Enter the description if you want or leave it
- Specify when the Backup set will expire by providing relevant entries next to After (days) and On (date)
- Again, click the General page option and choose the backup destination, by selecting Disk or URL
- Click Media Options to page to make necessary changes.
- After providing all the necessary details, click OK.
This will create your transaction log backup. You can use it to restore the database anytime. Here are the steps to restore a database using a transactional log backup file.
- Connect to your SQL Server instance
- In the Object Explorer, expand Databases
- Choose the database that you wish to restore through transaction log backup
- Right-click the chosen database
- Click Tasks > Restore > Transaction Log to open the Restore Transaction Log dialog box
- Make the requisite changes to the provided options and click OK
This will restore your database with the help of your transaction log.
Conclusion
These methods are the widely used ones to create a backup, followed by their use to restore the data from a corrupt database. Before trying any of them, make sure you have all the requisite permissions. Nevertheless, if you have limited time and want accurate results in a few minutes, you can use third-party automated SQL Recovery software for backup and recovery. Stellar Repair for MS SQL is a recommended tool to help your cause.
