Imagine a situation where you have lost your sensitive data due to a power outage. Despite trying hard, you are unable to find your lost database. What would you do? Database recovery becomes inevitable in any such circumstance. Before that, adopting protective measures to avoid outages is equally crucial. Here is detailed discussion on the ways to prevent the ill-effects of power outages on your database followed by methods to avert the data loss during a blackout.
Dealing with the power outages
Any kind of short term or long term lapse in electricity can lead to a complete failure of your PC or laptop. This eventually leads to significant loss of your digital data. If your system consists of the database of your organization, the outages can expose it to grave risk of data loss.
The system may shutdown abruptly due to short-term outages, such as power flicker, frequent voltage fluctuations short-circuit, etc. Using uninterrupted power supply (UPS) is the best resolution to handle this situation.
Likewise, long-term outages such as rolling blackouts, damaged power line causing long hours of blackout, power loss due to disaster like fire, earthquake, etc. To protect your data from falling prey to these adverse circumstances, you must create regular backups of your SQL database.
Taking Backup of your SQL database using SSMS
The steps below will help you to backup your database on frequent basis. It will help you to recover your data in the event of a power outage.
- Launch SQL Server Management Studio.
- Connect to a SQL Server instance.
- In the Object Explorer pane on the left, click ‘+’plus sign to expand Databases node
- Select the database that you want to backup
- Right-click the selected database
- Point to Tasks and click Backup to Back Up Database dialog box
- The selected database will appear first in the drop down list
- You may change the database from the drop down list to any other database existing on the server
- In the Backup type drop down list, select Full. Taking a Full backup is essential before you carry out a differential or transaction log backup
- Select Database in the Backup Component
- In the Destination section, choose Disk in the drop down list corresponding to Back up to
- Check the default location of the backup file, and Add or Remove the file as per your suitability
- Review the settings provided under Media Options and Backup Options on the left panel of the dialog box.
- Click OK to start the backup.
- Click OK once the backup completes successfully.
Taking Backup of your SQL database using T-SQL Command
You may also create a database backup with the help of T-SQL command in SSMS Query Window. Here is an example:
BACKUP DATABASE [SQL_TEST_DB]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TEST_DB.bak’
WITH NOFORMAT, NOINIT, NAME = N’SQL_TEST_DB-Full Database Backup’,
SKIP, STATS = 10
GO
In the example above:
BACKUP DATABASE command includes the database that we need to back up, here it is SQL_TEST_DB.
TO DISK gives the default location of the database backup
NAME gives the name of the database backup
NOFORMAT prevents the formatting or overwriting the backup file
NOINIT parameter appends the backup to the existing backup set
SKIP It bypasses the checks for the existing backup sets
STATS Gives the progress report of the backup process. A value of 10 indicates the reporting of progress after the completion of every 10% of the process.
Recovering the SQL database after power outage
If you have the recent backup of the database, you can restore and recover it easily in case a sudden power outage occurs. Here are the steps to recovery.
- Launch SSMS and connect to your SQL Server instance.
- In the Object Explorer, right-click Databases node and select Restore
- Under Source, select Device and then, click three dots (…) to get your backup file
- Click Add, locate your .bak file, and then click OK.
- Next, select OK to close the Select backup devices
- Click OK to start the database restore process from the backup file.
- Once the process completes, you will get the message, Database ‘database name’ restored successfully.
As an alternate solution, you may restore the database after a power outage through T-SQL command as well.
USE [master]
RESTORE DATABASE [SQL_TEST_DB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TEST_DB.bak’ WITH FILE = 3, NOUNLOAD, STATS = 5
GO
Using Third party software for SQL database recovery
You may frown upon the manual methods to restore the SQL database after a power outage or some other problem leading to abrupt system shutdown. The good news is that you can do the SQL recovery task with the help of a third-party software as well. Stellar Repair for MS SQL can work actively in this context.
Why use Stellar Repair for MS SQL
Here are some of the prominent reasons to prefer this tool over others available in the market.
- Uses latest and robust algorithm to recover data from damaged MDF or NDF files
- Can recover deleted data from a corrupted database
- Restores and recovers .bak files with high-degree precision
- Provides the option for Standard Scan and Advanced Scan for easy SQL recovery
- Can restore SQL tables with PAGE and ROW compressions
- Shows a preview of all the recoverable items in a tree-like structure
- Allows for selective recovery of the SQL database objects
- Recovers all the objects in the SQL database including views, tables, indexes, keys, etc.
Steps to recover SQL Database using Stellar Repair for MS SQL
Follow these simple steps to perform the SQL recovery after a power outage
- Launch the SQL database recovery tool from Stellar
- Choose the SQL database file that you wish to recover
- Click the Repair button on the screen
- Choose from Standard Scan or Advanced Scan
- Get a glimpse of the recoverable items in the SQL database
- Verify the data and save the file from the File menu.
- In the resulting dialog box, save recovered database to Live or New database
- You may also save the recovered database in CSM, XLS or HTML formats
- Click Next after saving the database
- Connect to your SQL Server instance by providing the required credentials.
- Click Next.
- Select Standard Saving or Fast Saving mode to save the database you just recovered
- Click Save to get the status of the table saving process, it will be Completed, Processing, Pending, or Aborted.
- As the saving process completes, you will get the message, File saved successfully.
You can now gain access to the recovered database and its items in the location you specified.
How to get Stellar Repair for MS SQL?
To get the SQL recovery software, you can visit the Stellar Info website. The good thing is that it supports SQL 2022 and all the lower versions. You may choose either Corporate, Technician or Toolkit versions for database recovery depending on your requirements. So what are you waiting for? Buy Stellar Repair for MS SQL today and get a permanent solution to your SQL database damage due to power outages.
Conclusion
Power outages can pose a major risk to the healthy state of SQL databases. You must adopt measures to protect your system from abrupt system shutdown because of unpredictable blackouts. A healthy database backup is the foremost necessity to ensure prompt and complete data recovery.
In case, a severe issue occurs, you should proceed to restore data from the damaged database with the help of backup file. You may adopt manual or automated ways for this purpose. For instance, the easy-to-use GUI interface of SQL Server Management Studio can help you create an SQL database backup and restore data through the backup. Another option would be to use T-SQL commands for these two jobs.
If you find these manual ways cumbersome or cannot way for the process to complete, an automated tool could help you out. Third party SQL recovery software like Stellar Repair for MS SQL is a widely used tool in this context. It is available from the official Stellar website in three different versions. Buy the most feasible one as per your need and preferences.
