Introduction

Have you ever encountered the dreaded error 3013 while trying to restore a database from an older SQL Server version to a newer one? I know I have, and it can be quite frustrating! In this article, we’ll dive into what causes this error and explore step-by-step solutions to help you successfully migrate your database without pulling your hair out.

Understanding Error 3013

Error 3013 typically occurs when you’re attempting to restore a database from a lower version of SQL Server to a higher version. In this case, we’re trying to restore from SQL Server 2017 to SQL Server 2019. The error message states that the upgrade step from version 901 to 902 failed during the restore process.

This error is related to the database compatibility level, which determines the specific features and behaviors available to the database. Each SQL Server version has its own compatibility level, and when restoring a database to a newer version, the compatibility level needs to be upgraded accordingly.

Resolving the Error

To resolve error 3013 and successfully restore your database, follow these steps:

1. Create a new, empty database in your SQL Server 2019 instance with the same name as the database you’re trying to restore.
2. Set the compatibility level of the new database to match the version of the source database (SQL Server 2017, which is compatibility level 140). You can do this using the following T-SQL command:

3. Perform the database restore again. This time, the restore process should complete successfully without encountering error 3013.
4. After the restore is complete, update the compatibility level of the database to match your SQL Server 2019 instance (compatibility level 150) using the following command:

By creating a new database with the appropriate compatibility level, you provide a suitable environment for the restore process to complete without hitting the upgrade step error. Once the restore is finished, you can safely update the compatibility level to take advantage of the new features in SQL Server 2019.

Other Reasons for Error 3013

While the main focus of this article is resolving error 3013 when restoring a database from SQL Server 2017 to SQL Server 2019, it’s important to note that error 3013 can occur in other situations as well. This generic error is raised when a backup or restore operation is interrupted unexpectedly, and it’s usually accompanied by other error messages that provide more specific details about the cause of the failure.

Some common reasons for encountering error 3013 include:

1. Insufficient disk space: If the drive where you’re writing the backup doesn’t have enough free space, the operation will fail.
2. Incorrect path to the backup storage device: Double-check the path and file name specified in the BACKUP or RESTORE command to ensure they are correct.
3. Backup file/device already open: If another program is currently using the backup file or device, the operation will fail.
4. Backup media device failure or malfunction: When using a tape drive or other backup device, ensure that it’s functioning properly and not experiencing any hardware errors.
5. Database corruption: If the database is corrupt, the backup or restore operation may fail. Run DBCC CHECKDB to identify and resolve any errors in the database.
6. Lack of permissions: The user account performing the backup or restore must have the necessary permissions (BACKUP DATABASE, BACKUP LOG, or CREATE DATABASE) for the operation to succeed.
7. SQL Server service account lacking access: The SQL Server service account must have read and write access to the backup device or file system where the backup is being written.

To troubleshoot error 3013, examine the SQL Server Error Log for additional messages that provide more specific information about the cause of the failure. Once you’ve identified the underlying issue, take the appropriate steps to resolve it, such as freeing up disk space, correcting file paths, fixing database corruption, or granting the necessary permissions.

By understanding the various reasons for error 3013 and knowing how to troubleshoot them, you’ll be better equipped to handle backup and restore issues in your SQL Server environment.

Conclusion

Dealing with error 3013 during a database restore can be stressful, but armed with the knowledge of compatibility levels and the step-by-step resolution process, you’ll be able to tackle this issue with confidence. Remember to create a new database with the appropriate compatibility level, perform the restore, and then update the compatibility level afterward. With these steps, you’ll be well on your way to a successful database migration!

If you’re curious to learn more about SQL Server upgrades and compatibility levels, check out the official Microsoft documentation here.

MSSQLSERVER_3013

Supported version and edition upgrades

Happy database migrating!