Wednesday, 24 November 2021

3-2-1 Backup Rule for Data Protection in SQL – What Is It & Is It Relevant?

In the event of a disaster leading to database unavailability, restoring backups is the first step to ensure business continuity. However, situations may arise when attempts to restore the database fails. For instance, the tape drive used for storing backups may get damaged rendering the backup data corrupt and unusable. In fact, tape drive media failure is the most common cause why restore fails. To prevent this and ensure database can be restored with uncorrupted data, SQL users implement the 3-2-1 backup rule for data protection. This article discusses this data protection rule and if it is still relevant.

The 3-2-1 Backup Rule for Data Protection – What It Is?

The 3-2-1 rule of data protection ensures that database can be restored with uncorrupted data. The idea behind the rule is to have:

3 copies of backup: You must have at least three copies of your data. One is your production data plus two backup copies. The more copies you have, the less risk you have of losing data.

2 copies on different media: Ensure to store two copies of database backup on different media types. This is important because a backup media can fail. When you split your backup into different media and a device fails, you’ll have another to fall back on.

1 copy on offsite: Store one of the two backups offsite. Doing so ensures that if anything happens to one backup copy, it won’t (hopefully) affect the other copy.

Is the 3-2-1 Backup Rule of Data Protection Relevant for SQL Users?

The 3-2-1 backup is a good starting point for devising any disaster recovery plan, particularly for SQL users who aren’t backing up at all. But, the backup rule has certain shortcomings.

Data Can Be Compromised

Maintaining three copies of data is fine, as more copies ensure recovery is possible in case of any disaster. But keeping two copies on different media types has limitations. Having two copies stored in two storage media or devices means quicker access to the backup (if the primary fails), however, this might not always be the case.

What happens if a ransomware infects your secondary storage while the primary is already down? You may lose all the data unless you pay a ransom. And, with several organizations replacing tape backup with cloud storage, an ever-increasing number of databases becoming vulnerable to ransomware attacks.

According a report by Imperva, “46% of all on-premises databases are vulnerable to attack.” Imperva predicted that data breaches will continue to grow as nearly one out of two on-premises databases is vulnerable to attacks. And so, you need more comprehensive and stronger data protection strategies than ever before.

Faulty Interpretation of the 3-2-1 Backup Rule

Backing up on tape drives is more expensive than backing up data on the cloud. And, as the demand for storage space grows so does the need for storage cost. Though, tape is still used – but due to slow recovery time and high cost involved – users are moving data to offsite locations, such as the cloud. That’s where the problem starts.

As cloud-based services not necessarily store backups at the same storage facility, point “2” and “1” in the backup rule are ignored. In other words, moving offsite data to cloud can fulfil the purpose of point “2” – it can be used to store a backup copy that is incorruptible and used for recovery if the first copy is affected. But this way, you’ll have only a single copy which doesn’t offer the protection you need from ransomware or other cyber threats.

Air Gap Protection is Lost

Though tape-based storage can slow down your recovery due to bandwidth constraints, it provides air gap to prevent ransomware from affecting your backup copies. However, air gap protection is missing in the 3-2-1 rule.

Air gap is basically a way of protecting a backup copy by storing it on a network that is physically separate from the primary data.

It was easy to provide an air gap when using tape backups. You can place tape backups in a box and transport them to off-site locations, creating an air gap between your backup and primary data copy. This makes it harder for hackers to attack a database, as they cannot attack both primary and backup storage devices.

How to Overcome 3-2-1 Backup Rule Shortcomings?

Backup strategies like 3-2-1-1-0 or 4-3-2 offers additional protection against ransomware attacks. Let’s discuss in brief about these two strategies:

3-2-1-1-0 Backup Rule

Like the ‘3-2-1’ backup rule, the 3-2-1-1-0 data protection strategy also requires maintaining at least three copies of data, storing data on at least two different storage media, and storing one backup copy offsite. Plus, it requires two additional steps:

  • Keeping one tape backup copy offline or air gapped, as it requires storing tape backups off-site. Or you may store cloud backups with immutability, thereby preventing data from getting modified or changed.
  • Monitors data to help identify and correct any errors in the backups.

Essentially, the 3-2-1-1-0 backup rule ensures that you've an error-free offline backup copy you can use to recover data in case of system failure or cloud failure.

4-3-2 Backup Rule

Developed by IT security partners, Continuity Centers, the 4-3-2 rule states that four copies of data are stored in three different locations. Out of the three locations, two are offsite and the third copy is stored in the cloud. And, the fourth backup goes to another cloud storage.

The 4-3-2 backup strategy ensures that duplicate copies of backups are created and stored at geographically distant locations to avoid data loss in the events of natural disasters.

Concluding Thoughts

Preventing data loss, in the event of a disaster, is crucial for business continuity. And so, you must be regularly taking backups and reviewing them to ensure their effectiveness in restoring the SQL databases. The 3-2-1 backup rule is a good starting point for data security, but you need more extensive backup strategy to protect your data against the growing number of digital threats. Upgrading the 3-2-1 rule to a 3-2-1-1-0 or 4-3-2 backup strategy provides an additional layer of security to help you recover ransomware affected databases.

Thursday, 23 September 2021

How to Design Backups in SQL Server?

The Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are basic concepts related to the information to be recovered and the time that it will take to recover. In a Recovery Design plan, it is essential to define the time required to restore the data and how much data we can lose in the recovery.

The RPO defines the objective related to the point of recovery. In some cases, we can lose one hour of data, and sometimes we can lose 1 entire day of data. Therefore, defining the RPO at the beginning is critical to design the plan. The same applies to the RTO.

In this article, we will talk about both RPO and RTO, and also discuss how to design the right backup and restore strategy.

Recovery Point Objective (RPO)

Recovery Point Objective (RPO) is a proportion of how often it is necessary to take backups. On the off chance that a disaster happens between backups, would you be able to stand to lose ten minutes of information refreshes? Or then again ten hours? Or then again, an entire full day? RPO addresses how new recuperated information will be. The RPO shows the measure of information (refreshed or made) that will be lost or should be restored after a blackout.

Recovery Time Objective (RTO)

Recovery Time Objective (RTO) isn't just the length of time between the disaster and recovery. The goal additionally represents the means IT team should take to reestablish the application and its information. In the event that IT team has put resources into failover administrations for high-need applications, they can securely communicate RTO in short order.

How to Design the Right Backup and Restore Strategy to Meet Business Goals?

To reduce the RTO, you need to consider combining the transaction log backups and differential backups in your strategy. If you only make full backups, the time to restore the data may be too high. Using differential backups can help reduce the recovery time.

Here are some tips to consider when doing a backup:

  • Make sure to store the backup on a different server. This will prevent you from losing data in case the server is damaged.
  • Use fast hard disks. The backup and restore will be faster if you use hardware with good performance. This will help reach the RTO.
  • Use differential backups to reduce the number of transactional files used to restore. The differential backups allow to restore the data faster and to get the RTO.
  • Estimate how often the data changes. If your data does not change too much during the day, you can make one single differential backup each day and a weekly full backup. On the other hand, if your database has several transactions per minute, you will need to combine full, differential, and transactional backups to recover all the data in the correct RPO.
  • Always test your backup and make sure that RPO and RTO are accomplished on your tests. You will test that the integrity of the data is fine. It is recommended to have a production environment and a testing environment to verify the backups.
  • Make sure to automate the backup and restore strategy by using jobs. The backups should run in a schedule that depends on how the RTO and RPO are designed.
  • If your database is big, using the MAXTRANSFERSIZE and the BUFFERCOUNT may help. These arguments will allow you to increase the size and the buffer. This way you will be able to restore the database faster.
  • Use database and backup compression to increase the speed of the backup.

 Use Stellar Repair for MS SQL for Faster Recovery

There is also another way to recover information in a fast and secure way. Stellar Repair for MS SQL is a well-known third-party software that helps recover data from a corrupt or damaged database. It can also repair damaged databases. If you want to know more about this software, please visit the website here.

Wednesday, 7 July 2021

How to Fix SQL Database Error 8961?

Running DBCC CHECKDB or DBCC CHECKTABLE on a database may report error 8961. Such a situation could occur while changing the data type of a table column from “ntext to nvarchar(max)” and updating the table with over 4000 records.

Essentially, updating the database table (in SQL Server 2012, 2014, or 2016) with 4000+ records leads to corruption. As a result, you get the following error message:

Msg 8961, Level 16, State 1, LineNumber

Table error: Object ID, indexID, paritionID, allocUnitD (type LOB data). The off-row data node at page (PageID), slot 0, textID does not match its reference from page (PageID), slot 0.

The complete error message looks similar to:

What Causes SQL Error 8961?

This is a corruption bug in the SQL Server engine. The corruption occurs within the Large Object (LOB) column.

How to Fix SQL Error 8961?

Microsoft has released the following cumulative updates to fix the database corruption error 8961:

Cumulative Update 5 for SQL Server 2016 RTM

Cumulative Update 2 for SQL Server 2016 SP1

Cumulative Update 4 for SQL Server 2014 SP2

Cumulative Update 11 for SQL Server 2014 SP1

Cumulative Update 7 for SQL Server 2012 Service Pack 3

Apply the cumulative update based on the SQL Server version you are using.

What If the Error Persists?

If applying the cumulative updates doesn’t help resolve the issue, to work around this issue do the following:

Note: Before trying out the below workarounds, investigate the hardware like drivers for I/O subsystem to check if corruption occurs due to hardware problem. If the hardware is faulty, contact your vendor or hardware manufacturer for further assistance.

  • Set the “large value types out of row” Option to 1

After changing the data type, also change the “large value types out of row” option to 1 by executing the query:



exec sp_tableoption 'tbl_Name', 'large value types out of row', '1'

  •  Restore Records from Backup

If the above workaround fails to fix the error, try resolving the problem by restoring the database from a good known backup. If you don’t have a valid backup, skip to the next method.

  • Run DBCC CHECKDB with Repair Option

As a last resort, you may try running the DBCC CHECKDB command using the minimum repair level, i.e., “REPAIR_ALLOW_DATA_LOSS”.


 But, this may delete the rows from the table.

  • Use SQL Database Repair Tool

To repair a severely corrupt SQL database and retrieve all the records, try using a third-party SQL database repair tool. Stellar Repair for MS SQL is one such tool that is built to safely scan and fix a corrupted SQL Server database. It supports repairing a db on SQL Server 2019, 2017, 2016, and earlier versions. The software repairs database files (.mdf and .ndf) and recovers all the objects like table, deleted records, stored procedures, etc.


DBCC CHECKDB may report database corruption error 8961 when the data type of a table column is changed and the table is updated with 4000+ rows. The issue occurs due to a corruption bug within the SQL Server engine. To fix this, try installing the latest cumulative updates released by Microsoft. If this doesn’t work, try the workarounds discussed above to fix the 8961 error. If you are required to repair the database, a better alternative is to use SQL database repair tool. The tool can help fix the error with no added risk of data loss.

Monday, 24 May 2021

How to Repair a Database in SQL Server Management Studio

As a SQL database administrator, it’s your responsibility to maintain and secure the databases. However, you may still experience database corruption. If not handled correctly and on time, you may lose potential data. This post will discuss how you can repair database in SQL Server Management Studio (SSMS) when there is no backup. Also, we will discuss an alternative solution to repair a SQL database without data loss risk. But let’s first discuss the possible reasons that can result in database corruption.

Possible Reasons for SQL Database Corruption

  • Server crashes or the system shuts down abruptly in the middle of processing data
  • I/O subsystem failure
  • Bug in the SQL software

You can call your hardware vendor for support to fix issues with the hardware. If the problem is with your SQL software, check out for updates released by Microsoft to fix the bugs in SQL Server. However, in case of a system crash or abrupt system shutdown, you will need to repair the corrupted database to recover its data. 

Before We Proceed

Make sure that your system meets these prerequisites:

  • SSMS must be installed on your machine. If it is not installed, you can download and install it from here.
  • SQL must be installed on your PC.

Steps to Repair Database in SQL Server Management Studio

  • Launch SQL Server Management Studio and connect to a server instance.
  • In the Object Explorer window, click the ‘+’ sign to expand databases.
  • Right-click on a database that you want to repair, and then select Properties. 
  • In the ‘Database Properties’ dialog box, click the Options tab from the left pane. In the Options screen, scroll down to the bottom of the screen.
  • To repair a SQL database, you will need to set it to single-user mode. For this, Restrict Access to SINGLE_USER from MULTI_USER. Click OK. 
You can also set the database to single-user mode by executing the following query:


Here ‘Test’ is the name of the database that needs to be repaired, replace it with your corrupted database.

Now, to repair the SQL database, you need to run the DBCC CHECKDB command with any of these repair options: REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS.




It only maintains the syntax for backward compatibility and does not perform any repair actions.


It helps perform quick repair tasks without any data loss. It can be used for repairing missing rows in nonclustered indexes and rebuilding an index. It cannot repair errors associated with FILESTREAM data.


It can repair all the errors reported by DBCC CHECKDB. But, Microsoft advises on using this option as a last resort to repair the SQL database, resulting in data loss.

Note: You can try using a SQL Recovery Software, such as Stellar Repair for MS SQL to fix all types of corruption errors. It can help repair a SQL database without any data loss risk. 

Click ‘New Query’ from the main menu. 

In the query window that opens, copy and paste the command below:



In the above command, replace ‘DB_Name’ with the name of your corrupted SQL database. And choose one of the repair options. Here’s an example of using the DBCC CHECKDB with ‘REPAIR_ALLOW_DATA_LOSS’ option to repair the database.

  • Click the Execute button to run the query.
  • This will repair the database. Change the database mode from ‘SINGLE_USER’ to ‘MULTI-USER’ by performing steps 4–6 above. Make sure to close and re-launch the SQL Server Management Studio for the changes to take effect.

How Can I Recover Missing Data after Running DBCC CHECKDB?

If you find missing data in the repaired database after running the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command, consider using a SQL repair tool. Recommended by DBAs and MVPs worldwide, Stellar Repair for MS SQL is a reliable software you can use to handle corruption errors in a SQL database. It helps repair a severely corrupted MDF/NDF file on Windows as well as Linux systems.

The software recovers all the database objects, like tables, views, keys, stored procedures, etc., without making any changes to the original database structure. It also helps recover deleted records.


This article outlined the most common reasons leading to SQL database corruption. While restoring from backup is the most recommended and obvious choice to deal with database corruption, there are times when the backup is not updated or has turned corrupt. In that case, you need to try repairing the database. This article covered the step-wise instructions following which you can repair the database in SQL Server Management Studio. Also, it advised using a SQL recovery tool as an alternative to DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS command. 

Monday, 8 March 2021

Stellar Toolkit for MS SQL - Software Review

As a SQL database administrator, I am responsible for maintaining our organization's SQL Server and databases. But despite implementing best database maintenance practices, including backups and performing DBCC CHECKDB integrity checks, I still run into database issues. Among these, corruption of SQL Server database is one of the most common issues that I've encountered as a DBA.

My recent experience with database corruption, which nearly made me lose my data due to corrupted database backup, made me think about an alternative approach. This was when I came across Stellar Toolkit for MS SQL, a software package comprising tools to repair corrupt SQL database, extract data from a corrupted backup file, and reset password of the database files.

Today, I'll be sharing my experience with Stellar Toolkit for MS SQL. For this review, follow the step-wise instructions below once you have downloaded and installed the software:

Stellar Repair for MS SQL

  • Launch Stellar Toolkit for MS SQL. This will open the following window:

  • Select 'Repair MS SQL Database.' The SQL repair software main interface opens with an instruction. The instruction suggests stopping the SQL server and creating a copy of the database (.mdf) file to be repaired. After creating the MDF file copy, you can restart the server and repair the db copy. 

  • Clicking OK opens a 'Select Database' window. Click on 'Browse' to select the corrupt MDF database file that you want to repair. If you're not aware of the file's location, click 'Search' to locate and select the file. After selecting the corrupt database file, click 'Repair' to begin repairing it.

  • Once the repair process is complete, a 'Repair complete' message box appears along with a preview of all recoverable database objects and the repair process's log report.

  • After verifying the recoverable data, clicking 'Save' on the File menu opens a Save Database dialog box with options to save the repaired file. The file can be saved in multiple file formats, such as MDF, CSV, HTML, or XLS. To proceed with saving the file, you'll need to connect to an instance of SQL Server to create a new database or overwrite an existing db. Finally, click the 'Save’ button for saving the repaired SQL Server database file to the selected location. 

Stellar Password Recovery for MS SQL

The toolkit's second tool is useful for admins who have lost or forgotten the password of their SQL database files. It helps in resetting the password of a protected database in just a few clicks; here’s how:

  • Select ‘Recover MS SQL Password’ from the main interface. This will open the Stellar Password Recovery for MS SQL interface as shown below:

  • Next, all you need to do is click ‘Browse’ to select your SQL database file (MDF), and then click the ‘Change Password’ button. This will reset the password of the selected database file. 

Note: You may not be able to problem selecting the backup (.bak) file if any MDF file is in use. So, make sure to stop the SQL Server service before resetting the password.

After resetting the password, exit the Password Recovery tool, restart the SQL Server service, and then try connecting to your server instance using the reset password. 

Stellar Backup Extractor for MS SQL

Another tool available in the toolkit is the backup file extractor. It helps recover data from a corrupted SQL database backup (.bak) file. Also, the tool provides different scan modes to perform quick or deep recovery of backup data.

The steps to recover data from corrupted backup file are as follows:

Click ‘Extract from MS SQL Backup’ to open the Stellar Backup Extractor window. The window opens with options to browse or search the corrupt backup (.bak) file. Once the file is selected, click Scan to initiate the scanning process.

  • The tool then asks to select an appropriate scan mode to perform the recovery. It recommends running the ‘Standard Scan’ to analyze a corrupted SQL backup file quickly. If you’re not satisfied with the Standard Scan results, choose ‘Advanced Scan’ for a severely corrupted backup file. After selecting the desired scan mode, click ‘OK’.

  • A window opens asking to select the type of backup you want to recover. 

  • Once you’ve selected the backup type, click Next. A ‘Repair Complete’ dialog box opens on successful completion of the SQL backup process. Click ‘OK’ to preview the recoverable data along with log report of the entire backup recovery process.  

  • After verifying the recoverable backup file data, click ‘Save’ on the ‘File’ menu to save the recovered file.


Stellar Toolkit for MS SQL is an effective utility that you can rely on to resolve major database issues. Having this tool at your disposal can help you reduce downtime associated with troubleshooting SQL database issues, as it provides tools to perform database repair, backup recovery, and reset passwords in a single interface. 

Tuesday, 5 January 2021

Stellar Repair for MS SQL - Product Review

Stellar Data Recovery has an amazing product designed for the Microsoft SQL Server platform to provide data recovery solutions where databases or backups get corrupted, login passwords are unknown, and other all is lost scenarios like deletions. It’s inevitable and undeniable that most SQL Database administrators and developers would one day face one or more of these questions:

  • Do you have a corrupt database file and need to recover the data?
  • Do you have a corrupt backup file that you are unable to restore?
  • Are you missing records from a deletion of table, entries, and other database objects?
  • Did you forget or do not know the password to connect to your SQL Server instance?

If you need a resilient solution for any of these mentioned issues above or just one or two, then look no further than the Stellar Repair for MS SQL.

The software is available in three editions:

  1. Technician Edition: Database Repair
  2. Platinum Edition: Database and Backup Repair
  3. Toolkit Edition: Database and Backup Repair with Password Recovery

The Stellar Repair for MSSQL provides an a la carte way of shopping for your needed features. Instead of Stellar billing you for a one suite, the software has been priced based of the features. You could purchase a technician when looking into repairing a corrupt .MDF or Platinum to get the ability to repair backups or go for the Toolkit to have full access to all the above-mentioned features as well as reset SQL Server administrator and user passwords.

Some features you will notice with the SQL Repair software:

  • New and enhanced GUI.
  • Multiple DPI Supported (100%, 125%, 150%, 175%).
  • Saves the Scan Information.
  • Option to save repaired database to SQL Server Authentication.
  • Support for MS SQL Server 2019, MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012 R2, 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats.

The installation piece of the software is very easy and user friendly. The below steps would help you to successfully install the software.

  • Run the downloaded Stellar Repair for MSSQL.exe to open the setup dialog box.
  • Click next and accept the License Agreement to continue.
  • Browse and specify the location and folder for the binary installation file and click next to continue.
  • Select checkbox per installation requirements for the additional task screen.
  • Review your installation configurations and select back to modify any settings. Click start to install if ready.
  • Click Finish after a successful completion of the install.

 Launch the Stellar Repair for MS SQL from your start menu under programs or double click the desktop icon.

Quick Overview of Stellar Repair for MS SQL:

SQL Database Repair Software can help you fix the corruption and restore the data back in some cases even when the data is deleted. The easy to navigate GUI takes all the existing stress of dealing with a corrupt database.

The included deleted records functionality can assist in restoring deleted records in the MS SQL database (MDF) repaired file. By check-boxing the above highlighted [Include Deleted Records], the repair process will try to recover any deleted records (if found).

A left pane object explorer shows a tree map of all the database objects in the repaired database. You also have the functionality of being selective in your export/save of objects by check boxing the needed table(s). You can also directly view records in the tables to verify the consistency and data quality.


Below at the footer of the application is the Log report which provides a verbose log of the transactions that occur during the repair process. This is essential for troubleshooting and understanding the different processes that occurred during the repair.


Stellar Repair for MS SQL provides that enterprise-level SQL Server database repair solution that saves tremendous hours of administrative work of recovering a corrupt database. The entire suite called the Toolkit opens up all the amazing features of SQL Server password reset and SQL database backup recovery. The super-fast recovery algorithm can retrieve data from a corrupted database or backup file and create a new database or export data into other formats. The SQL user password reset tool allows the average user to change passwords without in-depth technical knowledge. I was not expecting such additional out-of-box functionalities such as multiple backup supports (full, differential, or transaction log). This is an all-in-one solution tool that I would recommend to anyone (technical and non-technical) that works with SQL Server databases.