Common Database Restore Failure Errors in SQL Server
One of the most common tasks for a DBA is to perform Restore of databases as part of recovering the data during an disaster or corruption or regular activities like refreshing databases from prod to Dev or Test server. While trying to perform the restore, we may encounter various errors causing the database restore to fail.
Below are some of the common errors related to Database Restore.
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
– You may encounter this kind of error if you tried to restore a database and if has failed in the middle or cancelled it after sometime leaving a database in restoring state. To get rid of the database which is in restoring state, issue below command
– Now you can perform a new restore.
Restore failed for Server ‘TestServer\Inst’. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The operating system returned the error ’32(The process cannot access the file because it is being used by another process.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘D:\MSSQLSERVER\MSSQL\DATA\TestDB.mdf’. (Microsoft.SqlServer.Smo)
– This error can occur if you are trying to restore a database and specifying the physical file names which are already being used by another database Or if you are restoring on top of another database, but not using “With Replace” option while restoring.
– So, make sure no other databases on the server are using the database files, and if you are sure, then perform the restore by selecting “With Replace” option. If the database files are used by other database, then specify a different physical name for the database files while performing restore.
Msg 3101, Level 16, State 1, Line 8
Exclusive access could not be obtained because the database is in use.
– This error occurs, if the database on top of which you are trying to restore is currently being used by some users, thus locks are being placed on the database. Unless these locks are released, you will not be able to perform the restore operation.
– You can first take the database into Single_User mode and using clause “rollback immediate” which will kill any active sessions for this database. Once the database is in single user mode, you can perform restore or delete the database as well if required.
Alter database test set single_user with rollback immediate
– Restore your database now
Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘Test_DB’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
– Change the context of your session to Master database and then take the database into single user mode as mentioned above and then perform the restore using the option “With Replace” which will overwrite the existing database files.
Restore failed for Server ‘Test_DB’. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘TESTDB’ database. (Microsoft.SqlServer.Smo)
– This error occurs if you are trying to restore on top of another database by using a backup of some other database. You can use the restore option “with Replace” in order for this to work.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases. Log file ‘D:\MSSQLServer\TestDB_Log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Processed 34353 pages for database ‘TestDB’, file ‘TestDB_Data’ on file 1.
Processed 234 pages for database ‘TestDB_copy’, file ‘TestDB_Log’ on file 1.
RESTORE DATABASE successfully processed 34587 pages in … seconds
– This error can occur, if there was already TestDB_Log.ldf file which belong to a different database in that location D:\MSSQLServer\
– You can delete the physical files in that location and then perform the restore again to make sure there are no errors after restore.
Msg 3201, Level 16, State 2, Line 4
Cannot open backup device ‘D:\MSSQLServer\Backups\TestDB_Backup.bak’. Operating system error 2(error not found).
– The Operating System error 2 refers to “The system cannot find the file specified”, so make sure that the backup file name mentioned is correct and the file exists in the path specified.
– If above did not resolve the issue, check if the backup file is corrupted or not, by trying to restore with verifyonly or filelistonly and see if they return the details and also you can try using another latest backup to try ans restore.
System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘D:\MSSQLServer\Test_data.mdf’. (Microsoft.SqlServer.Express.Smo)
– This error occurs if SQL Server service account does not have enough permissions to create the Test_data.mdf file in the path specified. Grant Full permissions to SQL Server Service account to this path and its subfolder and files.
specified cast is not valid
– If you are receiving this error while trying to restore from GUI, then try restoring using T-SQL commands. Example, if backup is performed with option of securing the backup with a password, and if you try to restore this backup from GUI, you will get this error, so the solution would be to restore using T-SQL.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.