When it comes to managing a SQL Server environment, one of the most important tasks is taking regular backups. Backups ensure that your data is protected and can be restored in case of any unforeseen events or disasters. However, sometimes it can be challenging to identify the version of SQL Server from a backup file, especially if you have multiple versions of SQL Server in your environment.
Let’s consider a scenario where you have backups of a database named “DevDepartment” from different versions of SQL Server – SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016. All these backups are stored in a single folder with similar names like “Backup-DevDepartment+[DynamicTimeStamp]”. Now, if you need to restore a specific backup, you might face difficulties in determining which backup file belongs to which database server.
Fortunately, there is a simple solution to this problem. By checking the header details of the backup files, you can easily figure out the server, login, and database version. Here is a script that can help you retrieve these important details:
RESTORE HEADERONLY
FROM DISK = N'D:\data\SQLAuthority.bak'
When you run the above script, it will provide you with several important details. Pay attention to the columns – ServerName, UserName, DatabaseName, and DatabaseVersion. By comparing the DatabaseVersion column with the internal database version, you can determine the version of the database.
Here is a quick list of SQL Server versions and their corresponding compatibility levels:
| SQL Server Version | Internal Database Version | Database Compatibility Level |
|---|---|---|
| SQL Server 2017 | 869 | 140 |
| SQL Server 2016 | 852 | 130 |
| SQL Server 2014 | 782 | 120 |
| SQL Server 2012 | 706 | 110 |
| SQL Server 2008 R2 | 660/661 | 100 |
| SQL Server 2008 | 655 | 100 |
By comparing the DatabaseVersion column from the result set with the internal database version, you can easily determine the version of the database.
Identifying the SQL Server version from backup files is a simple yet crucial task. It helps you ensure that you are restoring the backup to the correct version of SQL Server and avoid any compatibility issues. So, the next time you have multiple backup files and need to identify the SQL Server version, remember to use the script mentioned above.
We hope you found this tutorial helpful. If you have any questions or need further assistance, feel free to reach out to us.