Top 25 FAQs on Backup and Disaster Recovery

SQL Server can be backed up using 3 methods. They are full backup (duplicates the full database), differential backup (copies only the modified data after the last backup), and transaction log backup (transaction log records are copied after the last log backup).

Transaction log backups, when used with a full database backup (and optionally differential backups), enable point-in-time recovery. The best backup strategies and consistency in execution ensure your data is protected from threats and system crashes.

Open SQL Server Management Studio, right-click the server instance option and choose Properties. After that, open the Database Settings page. There, you can see the Backup and Restore tab. To view the default backup compression level, select the Compress backup option under this tab. Here, you can enable or disable backup compression as the default criteria for new backups. 

To reduce the size of SQL Server backup files, use ZSTD compression. You can enable it by running the following commands.

BACKUP DATABASE DatabaseName
TO DISK = ‘C:\Backup\DatabaseName.bak’
WITH COMPRESSION (ALGORITHM = ZSTD);

If you want to set ZSTD as the default compression method, set the backup compression algorithm server configuration option to 3. 

EXEC sp_configure ‘backup compression algorithm’, 3;
RECONFIGURE; 

After this, any backup created with the WITH COMPRESSION option will automatically use ZSTD compression. Note that this option is available with new SQL servers only.

Yes, you can. The SQL server is built so that it can safely capture current changes. Just keep in mind that big backups can slightly slow down your users’ connection speeds. Also, you cannot run major database structural changes at the same time. However, basic operations like reading and writing data are still doable during backup.

An OS error appears when the Windows subsystem cannot finish the file I/O request initiated by the SQL Server Database Engine. When this happens, the system stops the backup operation abnormally and forwards the specific error code.

Common OS Error Codes
OS Error 5 (Access to the target backup folder is denied  due to the lack of write permission)
OS Error 3 / Error 123 (The system cannot find the path specified in your backup script )
OS Error 112 (There is not enough space available on the destination disk drive)
OS Error 32 / Error 170 (A third-party software package is using the file)
OS Error 1117 / Error 665 (I/O device error / File system limitations)

First, create a new database instance, and during the creation request, specify the backup to be restored. The backup data is transferred to the newly created instance, and a DNS address will be displayed. Once the restore finishes, change your application settings to use the new endpoint. 

Storing the information in an encrypted format is one of the successful practices for secure data backup. This method changes the data into an unreadable code using cryptographic algorithms and digital keys. Encryption protects data during transmission and storage from threats and illegal retrieval.

Many backup software programs have integrated encryption capabilities. External encryption tools are also available.

For effective backup, you should follow some strategies. Plan for full backup on weekends or night hours when user activity is low. This may help make use of the full potential of the system and network. Reducing the size of databases using compression also enhances backup performance.

A common cause of your backup failing is the shortage of storage space.
A poor network connection, incorrect permissions, timeout issues, and corrupted databases are some other problems that the administrator encounters during backup. 
Continuous monitoring of backup processes helps to detect errors and disruptions early.

Database backup creates a structured copy of data, including master DB and transaction files. This facilitates recovery in the event of system failures or digital threats. Backup can be stored in both on-premise and in cloud storage.

The cloud computing platforms AWS, Azure, and GCP provide scalable backup solutions. Data from a variety of resources, including virtual machine backup, databases, cloud apps, and on-premises storage systems, can be copied to these public clouds. The cloud vendors act as a centralized point to manage schedules, retention periods, and compliance requirements.

Setting a difficult password and changing it periodically can help.
Add multi-layer security (MFA) to user accounts.
Regularly review access logs for unpermitted activity.
Outsource backup services to reliable and experienced providers.

Backup software will skip files if the file path is longer than the limit set by the provider. Or sometimes it may be the presence of special characters included in the file names. You can adjust the path or rename the files to get them copied by the backup tool. 

You might have forgotten to include some important databases, configurations, and dependencies in your backup. Without restoring these components, even though the reports show a completion message, the application won’t function properly. Checking for the inclusion of all the elements and regularly performing restore tests helps you ensure successful restoration.

To fix a partially successful backup, open the software’s log or View Report and check for the skipped files. 
Close all active applications before backup, since open apps may lock some data. 
Confirm that your destination drive or cloud account has enough free space, and make the file names short
Fix security permissions and restart your device.
Run the backup manually again.

An email backup solution covers copying all mailbox contents, including emails, the address book, the calendar, tasks, and attachments. The folder structures, user preferences, and formatting are also copied to ensure complete restoration.

All components required for a website to function properly, including databases and files. Databases contain your site’s content, pages, comments, and user data.
Whereas files consist of your website files, plugins, themes, and images.

It is recommended to take a copy whenever you add new content or update existing content on your website. If you are having a dynamic website like an e-commerce website or an education/news portal, you should run backups at least once a day. This offers you confidence that all recent updates are protected and reduces the risk of losing data if a problem occurs.

Google Cloud offers limited data retention options, which are not helpful for long-term requirements. This means you may not recover data from a particular point in time. But you can do it easily with an external backup solution. However, GCP offers data protection by storing on multiple servers, which helps avoid loss of data if one server fails. The trash bin also helps by keeping deleted files for up to 30 days. Definitely not helpful after that period.

Microsoft 365 includes built-in redundancy and recovery features. But these may not provide sufficient protection against accidental deletions, ransomware attacks, malicious changes, or long-term data retention needs. 

An external backup system keeps another copy of your data. This allows you to recover business information even if the original data becomes unavailable or corrupted. External backups also reduce the risk of dependence on a single provider for both production and backup data.

RTO is the maximum length of time for complete restoration after a disaster. 

RPO denotes how much data loss can be afforded by the organization. It is measured as a time factor calculated from the moment of disaster to the last backup.

If you have taken the last backup 12 hours before the outage, then the RPO is 12.

IT Disaster Recovery (ITDR) involves the restoration of entire IT systems, including applications and data, after a disruption due to cyber attacks, hardware failures, natural disasters, or human errors. 

If the damage is severe, the recovery process may involve the following to resume normal operations.
 Restoring data from backups
 Rebuilding affected systems
 Recreating configurations
 Re-establishing network connectivity and security controls

Having backup copies of data is not enough to manage the recovery process. It starts with creating a strategic disaster recovery plan and putting it into action. You need to do regular restore testing to make sure it works when needed. During the planning stage, it is important to understand how a disruption would affect the business and decide on the best recovery strategy.

It helps a business get back to its previous state quickly after an unexpected disruption. It comprises what needs to be done to restore critical systems and services and keep downtime to a minimum, so that the restoration process doesn’t disrupt the daily functioning of the operations. A good recovery plan helps restore day-to-day operations to run smoothly again as soon as possible.

To ensure maximum uptime and efficient recovery, critical systems should be designed with redundancy. By doing so, the backup components can automatically take over if a primary component fails. This helps keep services running with minimal disruption.

Having continuous monitoring helps detect failures as they occur. Early identification of errors and behavior changes, and automatic triggering of failover mechanisms, help organizations reduce downtime and maintain the availability of applications and services.