1)Why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?
2) How to identify if the database was upgraded from a previous version of SQL Server.
Read more: http://www.mytechmantra.com/LearnSQLServer/Unable_to_Restore_Database_From_Backup.html#ixzz3ceJTRwm5
How to identify if the database was upgraded from a previous version of SQL Server.
Once the above code has executed successfully look for dbi_createVersion and dbi_Version values as highlighted in the below snippet. You will be able to see the value for dbi_createVersion as 611 which mean database was initially created on SQL Server 2005 and the value for dbi_Version will also be 611 which mean the database is currently residing in SQL Server 2005 Instance.
Next step will be to restore or attach the Products database on SQL Server 2008 and then verify the internal version number of Products database.
Carefully look for dbi_createVersion and dbi_Version values in the above snippet. You will be able to see the value for dbi_createVersion as 611 which inform you that the database was initially created on a SQL Server 2005 instance. Next look for the value dbi_Version which will be 655 it means that the database is currently residing in SQL Server 2008 Instance. This way you will be able to understand that the database was upgraded to SQL Server 2008 and was not initially created on an SQL Server 2008 instance.
2) How to identify if the database was upgraded from a previous version of SQL Server.
If you are trying to restore a database on an SQL Server 2005 or a lower version from the database backup which was performed on an SQL Server 2008 instance then it will fail. In this article we will take a look at the reason why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?
Error Message received when restoring an SQL Server 2008 database backup on an SQL Server 2005 or lower versions
Msg 3241, Level 16, State 7, Line 1
The media family on device 'Drive:\BackupFolder\DatabaseName.BAK' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The media family on device 'Drive:\BackupFolder\DatabaseName.BAK' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The reason for the failure is the internal version number of SQL Server 2008 is different from that of an SQL Server 2005 or lower versions. Since the internal version number of SQL Server 2008 is higher than that of an SQL Server 2005 database the restore process fails. To know more about internal version number of a database checks the following article
Important Note: - You will not be able to restore a database backup from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. Downgrade of a database is not supported using the database backup and restore method.
Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?
If you try to attach an SQL Server database which was initially created on an SQL Server 2008 instance to an SQL Server 2005 or a lower version then the process will fail with the below mentioned error.
Error Message received when attaching an SQL Server 2008 database on to an SQL Server 2005 or lower versions
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
The above error message clearly states that the database cannot be opened in SQL Server 2005 instance as the database which you are trying to attach is of the version 655. The SQL Server 2005 instance can only open databases which are of version 611 or earlier. To know more about internal version number of a database checks the following articleHow to identify if the database was upgraded from a previous version of SQL Server.
In below table you will able to relate Database Compatibility Levels and Internal Database Version Numbers for different versions of SQL Server.
| SQL Server Edition | Database Compatibility Level | Internal Database Version Number |
| SQL Server 7 | 70 | 515 |
| SQL Server 2000 | 80 | 539 |
| SQL Server 2005 | 90 | 611/612 |
| SQL Server 2008 | 100 | 655 |
| SQL Server 2008 R2 | 105 | 660 |
| SQL Server 2012 | 110 | 706 |
Conclusion
In this article you have seen why you can’t restore or attach an SQL Server database from an higher version of SQL Server to a lower version.
Read more: http://www.mytechmantra.com/LearnSQLServer/Unable_to_Restore_Database_From_Backup.html#ixzz3ceJTRwm5
How to identify if the database was upgraded from a previous version of SQL Server.
As a Database Administrator it is always good to know the history of databases which you manage in your environment. Is there an easy way to identify whether the database was upgraded from a previous version of SQL Server or it was created on the existing instance of SQL Server? In this article we will go through the steps to identify whether a database was upgraded from a previous version of SQL Server or it was created newly on the existing instance.
In this demo, we will first check the database internal version number for Products database which was initially created in SQL Server 2005. Next step will be to restore the Products database on SQL Server 2008 and then check for database internal version number.
Identifying Internal Version Number of a Database in SQL Server 2005
Lets connect to SQL Server 2005 using SQL Server Management Studio and execute the below TSQL code to check the internal version number of Products database. Once the Trace FLAG 3604 is enabled, you can execute the undocumented stored procedure DBCC PAGE to retrieve the internal version number of a database.
DBCC TRACEON (3604)
GO
GO
DBCC PAGE ('Products',1,9,3)
GO
GO
DBCC TRACEOFF (3604)
GO
GO
Important Note: - Once you execute a DBCC TRACEON or DBCC TRACEOFF command there will be a corresponding entry added to SQL Server Error Logs.
Once the above code has executed successfully look for dbi_createVersion and dbi_Version values as highlighted in the below snippet. You will be able to see the value for dbi_createVersion as 611 which mean database was initially created on SQL Server 2005 and the value for dbi_Version will also be 611 which mean the database is currently residing in SQL Server 2005 Instance.
Next step will be to restore or attach the Products database on SQL Server 2008 and then verify the internal version number of Products database.
Identifying Internal Version Number of a Database in SQL Server 2008
Once you have restored / attached the Products database on SQL Server 2008 instance and then execute the below mentioned DBCC commands to verify the internal version number of the Products database when it now residing in SQL Server 2008 instance.
DBCC TRACEON (3604)
GO
GO
DBCC PAGE ('Products',1,9,3)
GO
GO
DBCC TRACEOFF (3604)
GO
GO
Carefully look for dbi_createVersion and dbi_Version values in the above snippet. You will be able to see the value for dbi_createVersion as 611 which inform you that the database was initially created on a SQL Server 2005 instance. Next look for the value dbi_Version which will be 655 it means that the database is currently residing in SQL Server 2008 Instance. This way you will be able to understand that the database was upgraded to SQL Server 2008 and was not initially created on an SQL Server 2008 instance.
No comments:
Post a Comment