SQL Server Database Error troubleshooting: Msg 3167, Level 16, State 1, Line 1, RESTORE could not start database

  • Article

Introduction

This new article will help to fix the problem with the Msg 3167 in SQL Server. The error message is the following:

Processed 208 pages for  database 'yourdatabase', file 'yourdatabase'  on file 1.
Processed 4 pages for  database 'yourdatabase', file 'yourdatabase_log'  on file 1.
  
Msg 3167, Level  16, State 1, Line 1
RESTORE could not start  database  'yourdatabase'.
Msg 3013, Level  16, State 1, Line 1
  
RESTORE DATABASE  is terminating abnormally.
Msg 905, Level  21, State 1, Line 1
Database 'yourdatabase' cannot be started in this edition of  SQL Server because it contains a partition function 'myFunction'.  Only  Enterprise edition of SQL Server supports partitioning.

 

Requirements

Any supported SQL Server version on-premises for Windows.

Getting started

The error message 3167 level 16 and the error 905 with level 21 are related to the restoration of a SQL Server Enterprise database in another database of lower edition (Standard, Web, Express).

The Developer edition contains all the enterprise features, so it should not have problems to restore information related to an Enterprise database.

In some SQL Server versions, the tables and view partitions are only supported on SQL Server Enterprise or Developer edition. That is why if you have an Enterprise and try to restore in lower editions, the restoration will fail.

Starting on SQL Server 2016, the table and view partitions are supported in any SQL Server edition, however, in SQL Server 2014 and lower versions only on the enterprise and developer edition were supported.

What is a table or view partition?

A table partition is a division of the data horizontally into partitions. The table partition can improve the performance of the data.  Also, it is easier to maintain the partition quicker.

How to fix the problem

If you receive this error in SQL Server 2014, 2012 or earlier versions (earlier versions are not longer supported by Microsoft, but there are some users that still use them), the problem may be that you are trying to restore in a not enterprise edition.

To check the edition, open the SSMS and run this T-SQL code:

select SERVERPROPERTY ( 'edition' )

Another way to get the SQL Server edition is in the SQL Server Management Edition. Select the SQL Server, right click and select properties:

In product, you will be able to see if it is a Developer, Enterprise, Standard or other edition:

If your SQL Server edition does not support partitioned tables, you may need to remove the partitions, functions first before doing the backup[PC1] .

In order to remove the partition functions, you can use the drop partition T-SQL sentence to remove the partition like this:

DROP PARTITION SCHEME myRange;
GO

Removing the function is similar. The following T-SQL example shows how to do it:

  

DROP PARTITION FUNCTION myRangePF1;
GO

You can then remove the partition files:

ALTER DATABASE  [database_name] REMOVEFILE myPartitionFile;

Another option may be to restore in a Developer machine.

What to do if your backup is corrupt

If your database is corrupt, you can use the Stellar Repair for MS SQL. This software is used to repair corrupted backups.

You can download the software here:

ยท         Essential Toolkit to Recover & Extract Database from Corrupt SQL Server

To repair, select the backup file and press the Repair button:

You will be able to see the tables, views, stored procedures, functions, triggers and other SQL Server objects related:

You will need to select which backup type needs to be restored:

It will ask you the SQL Server versions. As you can see the SQL Server 7 (released in 1998 is also supported).

If everything is OK, the backup will be restored successfully.

Conclusion

The Msg 3167 is a common problem related to partitioned views and restoring a database. It is a common problem in SQL Server 2014 and earlier versions when you try to restore in a non-enterprise edition. We explained different ways to solve the problem and how to solve if the database is corrupt.

References

For more information about this topic, refer to these links: