How to restore a database from backup?

A database is a structured collection of information, typically stored and set up for effortless electronic access, management, updating, and backup. Databases are usually controlled via a database management system (DBMS). The database itself, the DBMS, and all associated applications are typically called a "database system". Computer databases usually contain data records (or files) that comprise different business-critical information - customer data, sales transactions, financial or product information, etc.

Databases can be used to store, maintain, access, modify, update, and control all sorts of data. They can collect and store data regarding individuals, things, locations, etc. Specific data is comprised in one place to allow efficient monitoring and analysis.

Commonly, data within most database types is modeled in rows and columns through a series of tables to ease processing and data querying. Most databases used today rely on structured query language (SQL) to write and query data. (hence, the common name "SQL server database")

Different database types vary in data structure, schema, and most suited data types. However, all database types comprise the same five fundamental components.

• Hardware - Hardware is required to provide a physical device to run the database software. Such hardware can include computers, hard drives, and servers.
• Software - Database software (or "database application") gives responsible users control over the database. Database management systems (DBMS) allow users to manage and maintain various databases.
• Data files - Data files (or just "data) represent the raw information stored in the databases. Database admins strive to organize said data to give it context so different tools and users can understand it better within a bigger picture.
• Data access language - In order to control the database, users need to rely on a programming language. The programming language and the DBMS work together to allow management, modification, and access control of the data within a database. Potentially the most common database language is SQL.
• Procedures - The procedures are a set of rules that determine how the database operates and how it handles different data types.

Today's business landscape requires extensive data collection, storage, and analysis. From SMBs to enterprises, proper database management is necessary to find, gather, store, access, and analyze data. Collected and well-organized data can improve customer experience and service, streamline day-to-day operations, and help with strategic decision-making.

• Improved customer experience and service - Accurate, up-to-date customer data can help organizations improve customer services via personalized approaches and targeted marketing campaigns. Moreover, they can pinpoint areas for improvement to enhance support agent performance and provide a better customer experience. These, in turn, can lead to higher customer satisfaction and loyalty.
• Streamlined day-to-day and critical operations - Keeping all essential data in a centralized platform can streamline business operations. By eliminating the extensive hours spent on manual data entry and effort duplication, databases can help managers and team leaders increase productivity, reduce costs, and boost efficiency.
• Educated decision-making - Accurate, up-to-date, reliable data can identify trends, insights, and patterns to assist responsible parties in making informed, strategic decisions. This can reduce costs, increase profitability, and boost performance.
• Improved data security - DBMS provide companies with user access controls, data encryption, and database backup features to protect important information from loss, theft, or unauthorized access. This can ensure that organizations maintain the required integrity and confidentiality regarding their data to meet regulatory compliance and protect the brand's image and customers.

Sometimes, a database can suffer damage, corruption, or malfunction due to human error, system crashes, or cyberattacks. In such a scenario, you can restore a database to bring it back to a correct, consistent state. The process involves restoring (or "reloading) the most recent database backup and applying succeeding transaction log backups to restore operations.

You can restore a database in several ways depending on the inflicted damage and available database backups. In the best-case scenario, you can recover the database with minimal downtime and no data loss.

Database restoration requires reloading database backups (in any scenario). This is why database recovery is impossible without a reliable database backup file. Therefore, companies must establish a reliable database backup strategy to ensure that data and logs are regularly (and reliably) copied to another storage device or platform. (on-premises, off-premises physical, or cloud backup)

A well-designed and executed backup plan reduces the risk of data loss, minimizes downtime, and ensures a successful database reconstruction. Nevertheless, even if your company has a comprehensive recovery strategy, performing SQL Server-based and Windows backups is recommended before you attempt to restore a database.

Database backup is critical to ensure a successful and timely data recovery following a data breach, natural disaster, or cyber-attack. If your databases are somehow compromised, and you can't recover them, it can wreak havoc across your company network. If your business doesn't create regular database backup files, you won't be able to restore any data lost due to human error, system crashes, or malicious interference.

Another reason to ensure a restoration process for databases is data security. If your organization suffers a malware attack, you need to be able to distinguish critical data before and after the attack. And since you can't undo the attack, restoring all affected data with minimal downtime is the next best thing.

For example, suppose a ransomware attack encrypts all company data on your on-premises devices. Unless you have a full backup available for recovery, your employees won't be able to access any program files, applications, and service platforms to procure services for your customers. On the other hand, a reliable database backup will enable quick restoration to your backup system and counter the ransomware attack.

The restored database (or databases) will be as good as new, and you will be able to resume business processes almost instantaneously.

We discussed the importance of having a reliable backup file in a data-loss scenario. Available backups are the best way to counter potential data breaches and server crashes, but there are several crucial aspects to check before you restore a database backup.

• Database Ownership - The first checkmark is especially important for databases residing outside the Active Directory environment in which you've created it. In such cases, the database owner is typically set to a non-existing user. Here, it's convenient to set the owner to "sa" (short for "system administrator") as such a user exists on every SQL server. Even if the "sa" account is currently disabled, the database will still have a valid owner for the restoration instance. Keep in mind, that the "sa" user is automatically added to the "sysadmin" fixed server role. As such, it has all permissions on the designated database instance to perform any available activity. If the "sa" login is hacked, the attacker can significantly damage the database and your network. While you can't erase the "sa" login, you can disable it after you restore the database backup. You can manage this option in SQL Server Management Studio via the following path under the database: Properties -> Files -> Owner.
• Recovery model - Non-production environments usually don't require keeping databases in a bulk-logged or full recovery model. They typically prefer the simple recovery model to save up drive space. However, businesses usually want to restore a backup via the full or bulk-logged recovery models. To set this option in SQL Server Management Studio, follow this path: Properties -> Options -> Recovery model.
• File growth settings - Many databases keep their original growth settings - 10% or 1MB increments - which can be suboptimal for complex business environments. Companies may choose a fixed growth increment to ensure they can rely on the backup sets to restore a database efficiently (e.g., 1GB increments if your storage medium can handle it). This setting applies to both data file and transaction log backup. Moreover, if you enable Instant File Initialization (IFI) for your SQL Server service accounts on Windows to save data-writing time, remember that your transition log files still need all the zeroes out of every byte as the file grows.
• Delayed durability - Delayed Durability is a feature used to reduce the number of transaction log writes by comprising smaller writes to write them together. The approach is especially beneficial for development environments to speed up their work significantly. However, an error or an unexpected shutdown of SQL Server can lead to data loss in a database configured for delayed durability. Hence, you need to check the setting before restoring the database. You can set the feature to "Forced" in SQL Server Management Studio via the following path under the database: Properties -> Options -> Miscellaneous -> Delayed Durability.
• Compatibility level - Depending on the backup source and the current SQL Server you're restoring it to, you can face compatibility issues. SQL Server 2016 (and higher versions) can restore a database from as long ago as SQL Server 2005. (the compatibility level will be set to at least 100)To check the compatibility level of a potential database backup recovery and your SQL Server instance, you can use the cardinality estimator in SQL Server 2014.You can set the compatibility level option in SQL Server Management Studio via the following path under the database: Properties -> Options -> Compatibility level.
• Query store - "Query Store" automatically captures queries, plans, and runtime statistics to retain them for future review. If you wish to upgrade databases and change the compatibility level to test query plans between different cardinality estimator versions, it's recommended to turn on "Query Store" and let the feature track said plans. As long as your older plan exists, you can enforce it after changing the compatibility level. Again, the feature is especially convenient for development environments to streamline comparing query plans. You can set the "Query Store" option to "Read-write" in SQL Server Management Studio via the following path under the database: Properties -> Query Store -> General -> Operation Mode (Requested).
• Page verification - From SQL Server 2005 onwards, the data page verification algorithm uses a checksum instead of a torn-page detection, making it highly efficient for both production and non-production databases. You can set the option to CHECKSUM in SQL Server Management Studio via the following path under the database: Properties -> Options -> Recovery -> Page Verify.

SQL Server Management Studio is an integrated environment used to manage any SQL server infrastructure. Via SSMS, users can access, manage, configure, administer, and develop all SQL server components (including SQL server backup and restore for different Microsoft SQL server solutions).

There are three required steps to backup and restore a backup using SQL Server Management Studio:

Create test database files

• Launch SQL Server Management Studio -> connect to your SQL server instance.
• Open a "New Query".
• Run a Transact-SQL (T-SQL) code to create the test database:
• Refresh the "Databases" node in "Object Explorer" to explore the new database.

USE [master];

GO

CREATE DATABASE [SQLTestDB];

GO

USE [SQLTestDB];

GO

CREATE TABLE SQLTest (

    ID INT NOT NULL PRIMARY KEY,

    c1 VARCHAR(100) NOT NULL,

    dt1 DATETIME NOT NULL DEFAULT GETDATE()

);

GO

USE [SQLTestDB]

GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');

INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');

INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');

INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');

INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');

GO

SELECT * FROM SQLTest;

GO

• Launch SSMS -> connect to your SQL server instance.
• Expand the "Databases" node found in "Object Explorer".
• Right-click the database you wish to back up -> hover over "Tasks" -> select "Back up...".
• Under "Destination", confirm that the path to your chosen backup file location is correct. If you want to change the path, select "Remove" to remove the current path, and click "Add" to type in a new path.
• Select "OK" to create a backup of an existing database.

• Launch SSMS -> connect to your SQL server instance.
• Right-click the "Databases" node found in "Object Explorer" -> select "Restore Database..."
• Select "Device:" -> select the ellipses "(...)" to locate the desired database backup file.
• Select "Add" -> navigate to where the designated .bak file is located -> select the backup file -> select "OK".
• Click "OK" to close the "Select backup devices" dialog box.
• Click "OK" to restore the database backup file.

The RESTORE ... WITH REPLACE feature allows users to overwrite the existing database when doing a recovery. In some scenarios, you may see an error message stating, "The tail of the log for the database .. has not been backed up." This can occur because the function allows you to write over an existing database during a restore without first backing up the tail of the transaction log.

The WITH REPLACE option instructs the SQL Server to discard any active transaction log contents and proceed with the restore. Using either T-SQL commands or SSMS can yield the following error message:

"The tail of the log for the database "X" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log."

The following command will restore the database while disregarding any active data in the current transaction log:

• RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'WITH REPLACEGO

Restore a database via SSMS - To restore a database using SSMS, select "Overwrite the existing database" on the options page for the restoration process. Here, you can choose from three recovery state box options:

RESTORE WITH RECOVERY - This option will leave the database ready to use by rolling back uncommitted transactions; additional transaction logs cannot be restored.

RESTORE WITH NORECOVERY - This option will leave the database non-operational and won't roll back uncommitted transactions; additional transaction logs can be restored.

RESTORE WITH STANDBY - This option will undo uncommitted transactions but save the undo actions in a standby file so that recovery effects can be reversed.

You can also backup and restore a SQL server database via the mysqldump client utility. The feature can dump a database and include SQL statements to rebuild it. By default, the dump file will include the SQL commands to restore all tables and data. The general syntax for the operation is as follows: sudo mysqldump -u [user] -p [database_name] > [filename].sql

• Replace [user] with your specific name and password for the user database (if required).
• The [database name] refers to the path and filename of the database.
• The > command specifies the output.
• [filename] refers to the path and filename for the saved dump file.

You can also back up an entire Database Management System via the following command: mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

Also, if you want to include several database files in the backup file, use the following command: sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql

1. Create a new database

On the database system host, use MySQL to create a new database. Make sure to name it the same as the lost database you wish to restore. This will create a foundation file that mysqldump will use as a destination database to restore the data. Since the dump file already has the commands required to rebuild the database, you only need to create an empty, new one.

1. Restore MySQL Dump

To restore a backup file via MySQL, enter the following command: mysql -u [user] -p [database_name] < [filename].sql

• Again, you'll need to include specific [database name] and [filename] in the path. Often, the [database name] can reside in a root directory on the host machine; in such a case, you may not need to add a new path. However, you must ensure you've specified the exact path for the dump file you wish to restore, including the server name. (if required)

You can also backup and restore a MySQL database via the phpMyAdmin option (if you're running it). You can use the export function to create a backup and the import function to restore it. The short guide below describes how to restore a backup via phpMyAdmin.

1. Create a MySQL database backup file

• Open "phpMyAdmin" -> from the directory tree on the left, select the database you want to back up.Typically, a directory structure will open in the right-hand window; the directory tree on the left will highlight all the assets under the main database.
• Click "Export" from the menu across the top of the display.
• From the "Export Method" section, select "Quick" to copy the entire database. You can also choose "Custom" to select individual tables or other options. The "Format" field must be set to "SQL" unless you are certain you need to change it.
• Click "Go". If you've selected the "Quick" backup option, the web browser on your machine will download a database copy into a specified Downloads folder. You can then copy the backup and store it in a secure location.
• Clear the information from the old database

• Before you restore a backup file, it's crucial to clear out old data. If you skip this step, the overwriting process may fail during recovery. This will likely create duplicate tables, leading to errors and configuration conflicts.
• Open "phpMyAdmin" -> on the left navigation pane, select the database you wish to restore.
• Click the "Check All" box on the bottom -> from the "With selected" drop-down menu, choose "Drop".
• The feature will prompt you to confirm your choices -> click "Yes". This action will clear all existing data and allow for an efficient backup restoration.
• Restore the database backup file

• On the menu across the top, select "Import".
• The first option here is labeled "File to import"; a couple of lines down, you will find the line "Browse your computer" -> from that option, click the "Choose File" button.
• Navigate to where you've stored the export file via the dialog box. Then, leave all options set to default unless you've created a backup with special options.
• Click "Go".

Whether restoring entire system databases, differential backups of a specific SQL server database, or simply testing your database backup files, organizations can face numerous data integrity, security, and compliance issues.

Below, we explore the most common challenges during a restore operation so you can prepare accordingly and avoid them.

Ransomware attacks pose one of the most significant threats to data availability during backup and recovery processes. As ransomware evolves alongside enterprise and cybersecurity technology, its threat can only be minimized but never eradicated.

Companies must employ a proactive security infrastructure to detect and neutralize suspicious behavior that threatens backup resources. A good practice to follow here is the 3-2-1 Rule of Backup, but you should also consider EDR, proactive threat hunting, and threat analysis solutions.

Suppose you want to restore a SQL Server Database Engine backup file. You follow the specific restoration steps and complete the process with minimal downtime. However, once you get your systems back online, you see that the recovered data is either missing or corrupted.

Since there are numerous reasons for a corrupted backup - a faulty backup device, sneaky malware, a misconfigured database box, etc. - you can't know which earlier backup is accurate and won't pose additional issues.

To avoid corrupted database restores, organizations must be proactive when protecting necessary backups. You must take the time to understand how much data you can afford to lose during restoration and what data is critical (hence requiring maximum protection) to design an appropriate backup schedule. Moreover, you should calculate the expenses regarding each specific backup set and fit them into your budget to ensure data protection against the most devastating data-loss scenarios.

Suppose you restore a differential backup of a SQL database to your primary systems. Even if the recovery process is deemed successful, you must ensure that all recovered data is consistent with the lost data during a breach or a system crash. To battle this challenge, companies must combine several methodologies to verify and audit the restored database:

• Define data consistency to ensure all recovered data is accurate, complete, and conforms to the database schema rules and constraints.
• Use backup validation tools to perform multiple checks and tests on all backup files and the restored data to verify the integrity and completeness of the recovery process.
• Use checksums and hashes to compare the data before and after restoration to detect any changes or errors or verify that the data sets match.
• Use data quality tools to access and improve the quality of recovered data based on accuracy, completeness, timeliness, and consistency criteria and metrics.
• Use dedicated data comparison tools to compare data in several databases, tables, or files to identify differences or discrepancies or to verify that data sets before and after they restore a backup are equivalent.

Organizations need reliable backup sets to restore databases following a data loss event. Even if the process is successful, you must ensure that restored data is protected efficiently. Depending on your environment and solution usage, you must:

• Implement strong on-premises and cloud permissions to assign access controls (from read-only mode to full access) to responsible users.
• Employ data encryption for all employee, operational, financial, and user databases.
• Introduce multi-factor authentication to secure system database access for remote workers and BYOD devices.

The two primary questions to determine your backup frequency are:

• How critical is your data?
• How quickly do you need to restore it?

Based on how you answer these questions, there are several backup schedules you can follow:

• A weekly full backup (every 168 hours), with daily differential backups (every 24 hours) - suitable for non-mission-critical databases or ones with just a few weekly transactions.

• Daily full backup (every 24 hours) - suitable for databases with more frequent transactions than the example above, but said transactions are still re-creatable via manual records or other resources.

• Full backup every 24 hours, with a differential backup every 6 hours - suitable for databases that house more changes during a typical working day compared to the previous example.

• Full backup every 24 hours, differential backups every 4 hours, and transaction log backup every 1 hour - suitable for databases hosting critical applications writing dozens or hundreds of transactions per hour.

• Full backup every 24 hours, differential backups every 3 hours, and transaction log backup every 15 minutes - suitable for the busiest mission-critical system databases.

Data-loss events of unexpected downtime can halt business processes and hurt your company's productivity, customer satisfaction, and revenue. It's crucial to understand the reasons for potential data loss and plan for them in advance. Generally, potential catalysts for a database recovery include:

Hardware failure

Every modern organization relies on hardware - computers, laptops, tablets, smartphones, routers, etc. - to operate efficiently. However, manufacturing defects, physical damage, wear and tear, and other factors can cause a hardware failure and render databases on a device inaccessible. Moreover, power surges, harsh conditions, and outages can damage different hardware components and result in data loss.

User error

Accidental deletion is among the most common reasons for data loss. In addition, incorrect configuration can cause both hardware and software failure, which, in turn, can result in corrupted or lost databases.

Software failure

Software apps can crash or become exploitable by unauthorized third parties. Without a proper database backup file, companies can experience severe data loss or downtime. Moreover, your entire company operating system can crash and result in further data loss and hindered business processes.

Cyber attacks

Modern cyber threats evolve with each passing year. Viruses, worms, and ransomware attacks can all hurt your network and data. If a malicious attack strikes your database files, you may lose them for good unless you have reliable backup files to restore them from.

Migration to different or new hardware

Migration to new hardware can be due to the total crash of existing database hardware. In other cases, you may need to restore a database backup from a test environment to a productive system, even when there are no issues in either of them.

The most efficient way to minimize database recovery downtime is by familiarizing yourself with all procedures involved in the process. You can simulate periodic breakdowns or crashes and calculate and define the required time and actions to get back online. This way, if a failure does happen, you will be prepared to mitigate it quickly and efficiently.

Creating a Disaster Recovery Plan Document is recommended to remind you of all the steps to follow when restoring a database. Even if you can find many pre-designed DR Plan documents online, it's best to create a custom plan tailored to your company's specific needs, processes, and goals. The list can include:

• A step-by-step checklist of all actions during a recovery procedure.
• System configuration and passcodes for all involved hardware and software.
• Contacts of all technicians, network managers, and supervisors that may be of assistance if an issue arises.

Acronis Cyber Protect is a product of continuous updates and upgrades to establish it as a leader in providing the most efficient, affordable, and easy-to-pilot database backup and recovery solutions.

Acronis Cyber Protect offers a wide range of cybersecurity services to ensure complete company network protection - endpoint detection and response, anti-malware and antivirus, backup and recovery automation, top-tier data encryption, and more. Integrating all cybersecurity features with the Acronis Cloud can fortify every endpoint in any location, making it a perfect fit for remote, mobile, and hybrid work environments.

The solution aims to cut down on cumbersome tasks and minimize downtime so your teams can focus on business-critical projects, increase revenue, and ensure competitiveness regardless of your chosen industry.

As for MSPs, Acronis Cyber Protect Cloud offers advanced cybersecurity features - Security + EDR, automation, backup, disaster recovery, DLP, and more to unite backup and next-gen AI-based anti-malware, antivirus, and endpoint protection management in a single solution. The provided integration and automation ensure unmatched services for MSPs to reduce complexity and increase productivity while reducing operational costs.