SQL Database Backup Restoring Errors and Solutions
Introduction
In this article, we will discuss different errors that occur during the backup and restoration process. We will cover problems related to files in use, like backups in use, how to handle big backup sets that take a long time to restore and the compatibility errors during restoration. To understand this, the first thing is to have MS SQL Server in our machine.
Requirements
- Install MS SQL Server 2012 or later.
- Install SQL Server Management Studio.
Steps
You need to successfully install MS SQL Server Management Studio (SSMS) and SQL Server. Run SQL Server Management Studio and we will see an interface. We will work with SSMS to check the errors and try to find a fix for them. For example, when the file is in use, you will have some errors. Also, when you are trying to restore the database, it may send you an error. We will see how to fix these possible problems.
Sometimes our database is used by another user. This state sends us an error when you restore your database.
Fig 1
Press the left click and select the option. It will open a new window that will tell you to select the database backup from the device. The backup file may be in your computer directories or where you save your last backup. Select the file and open .bak file. The new window looks like below.
Fig 2
In this image, you can see my .bak file. When your file is successfully attached without any error then you will see a screen.
Fig 3
To continue the process after selecting the file and if you get an error, it will look like below screenshot:
Fig 4
The SSMS will send you an error related to the compatibility. This error occurs when you try to restore a SQL Server database from a new version using an old SQL Server. For example you want to restore a database of SQL Server 2014 using SQL Server 2012:
Fig 5
Now, here we need to study the error. The error is related to a SQL Server version compatibility problem. In general, you cannot restore in and old SQL Server, SQL Server backups from newer versions. You can however restore backups from older SQL Server versions in new SQL Server versions. For example, you cannot restore a SQL Server 2014 backup using SQL Server 2012. However, you can restore a SQL Server 2014 backup using SQL Server 2016.
The backup in use problem
Another common problem is when the database is in use and you cannot restore the backup if the database is in use.
When the DBA is trying to restore the backup, he receives an error that the database to restore is in use. To solve this error, we use sp_who and sp_who2.
Syntax
“sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]” in double-quotes.
I mentioned the simple syntax of the sp_who. This query will return the current users, sessions, and processes. Another option that may help is to set the database to single user mode and then you will be able to restore the database. With this stored procedure, you can detect the active user and use later the kill command to kill the users that are using the database and that do not let to restore the database.
The problem with big backups
Sometimes the database is too big and it takes forever to backup and after several hours it shows an error.
If that is your problem, we recommend to split your backup in multiple files or filegroups. The SSMS allows to backup just some files or filegroups instead of all the database.
Fig 6
Alternative solutions when the backup is corrupt
Some times our database stuck due to electric circuits problems, virus, hardware corruption and when we open it again the database cannot be restored. Here I explain you a different method using Stellar Toolkit for MS SQL. First, I show you the main screen of the Stellar Toolkit for MS SQL.
Fig 7
You can download the software here:
In this window here you can see the three options.
- 1- Repair MS SQL Database
- 2- Recover MS SQL Password
- 3- Extract from MS SQL Backup
Here we will use option three Extract from MS SQL Server Backup. It will help us to recover the database from .bak file. If you will select the option three from the Stellar toolkit for MS SQL Server then you will see a new window look like this one:
Fig 8
Here you will see a file select button click on the button and select the file from your computer. When you successfully scan the file, you will able to save this on your computer or laptop. This software helps us to restore or recover databases from corrupted backup files. This software also allows us to save the files in multiple formats.
Conclusion:
Here we studied SQL database backup restoring errors and the solutions that when error occurs and how we can solve this using SSMS (SQL Server Management Studio) and using sp_who and sp_who2 queries. We also learned how to handle big backup files, how to solve the error compatibility problem with SQL Server. Also, here we studied the Stellar Toolkit for MS SQL that also helps us for solving multiple errors.