none
Backup "SqlServer version" Independent RRS feed

  • Question

  • Hi,

    I use Smo for DB backup.

    I want restore the same backup over different version of SQL Server.

    Es: I want to backup database in SQL Server 2008r2 and I want to restore to SQL Server 2005.

    Is it possible?

    Thanks in advance.


    - Andrea Bianchi - site: http://www.BianchiAndrea.com
    Friday, February 4, 2011 3:42 PM

Answers

  • You cannot restore backups on earlier versions of SQL Server, for example you cannot restore a 2008 R2 or 2008 backup on 2005.

    From the BACKUP Transact-SQL Ref topic (http://msdn.microsoft.com/en-us/library/ms186865.aspx):

    Backups that are created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.

    If you need to get database data from a recent release to an earlier release, you have to use alternative mechanisms such as the database publishing wizard.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 4, 2011 8:30 PM
  • Hello,

    I have reread the documentation about BACKUP

    Denali      : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.110).aspx

    2008 R2   : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.105).aspx

    2008        : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.100).aspx

    2005        : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.90).aspx

    i found nothing about the possibility to do a backup compatible with an earlier version of SQL Server.

    According to me, it is logical as the physical structure of the database is different for SQL Server 2008  and 2005 for example. Moreover, there is the problem of new data types not existing in earlier versions : how to treat FileStream ( introduced with 2008 ) in a SQL Server instance ? And what bout the Unicode compression appeared with 2008 R2 ?

    I understand that is a problem for you, but i think that SQL Server is more complex than Office and the SQL Server Team has done the good choice when they decided to forbid the restore of databases to earlier versions of SQL Server. It avoids many problems ( it is the same thing for Visual Studio, you can modify the code coming from VS 2005 with VS 2008 , you have the possibility to change it to 2008 or to stay 2005, but you cannot modify code from VS 2008 or 2010 with a VS 2005 )

    Have a nice day

    PS : i suggest you to mark the post of Alan Brewer as the good answer as i am sure that your problem cannot be solved


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, February 7, 2011 5:15 PM
    Moderator
  • According to the backup PM, the statement applies primarily to major (like 10.0) and point (like 10.5) releases, such as not restoring a SQL 2008 R2 (version 10.5) backup on an instance running SQL 2008 (version 10.0). It does not generally apply to things like service packs, cumulate updates, or hotfixes. There are some rare exceptions, such as if a service pack introduced a new feature.

    Databases get stamped with a version. A point release or full release will bump the database version. Things like SPs or CUs rarely bump database versions.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 8, 2011 12:45 AM

All replies

  • You cannot restore backups on earlier versions of SQL Server, for example you cannot restore a 2008 R2 or 2008 backup on 2005.

    From the BACKUP Transact-SQL Ref topic (http://msdn.microsoft.com/en-us/library/ms186865.aspx):

    Backups that are created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.

    If you need to get database data from a recent release to an earlier release, you have to use alternative mechanisms such as the database publishing wizard.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 4, 2011 8:30 PM
  • Hello,

    The reply of Alan Brewer is applying even if the difference is only on the level of the Service Pack, but i don't know if it is applying on the level of a KB and CU ( it would be logical if the KB or CU means new dll or changes about the behaviour of the engine ). I would be glad if Alan Brewer could confirm what i am writing ( i think that the restore is blocked because of the full version value, and this value is modified by any KB or CU installed )

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, February 5, 2011 11:04 AM
    Moderator
  • In backup creation there are the possibility of specify version of backup? Like in word 2010 save as word 2000?


    - Andrea Bianchi - site: http://www.BianchiAndrea.com
    Monday, February 7, 2011 3:44 PM
  • Hello,

    I have reread the documentation about BACKUP

    Denali      : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.110).aspx

    2008 R2   : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.105).aspx

    2008        : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.100).aspx

    2005        : http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.90).aspx

    i found nothing about the possibility to do a backup compatible with an earlier version of SQL Server.

    According to me, it is logical as the physical structure of the database is different for SQL Server 2008  and 2005 for example. Moreover, there is the problem of new data types not existing in earlier versions : how to treat FileStream ( introduced with 2008 ) in a SQL Server instance ? And what bout the Unicode compression appeared with 2008 R2 ?

    I understand that is a problem for you, but i think that SQL Server is more complex than Office and the SQL Server Team has done the good choice when they decided to forbid the restore of databases to earlier versions of SQL Server. It avoids many problems ( it is the same thing for Visual Studio, you can modify the code coming from VS 2005 with VS 2008 , you have the possibility to change it to 2008 or to stay 2005, but you cannot modify code from VS 2008 or 2010 with a VS 2005 )

    Have a nice day

    PS : i suggest you to mark the post of Alan Brewer as the good answer as i am sure that your problem cannot be solved


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, February 7, 2011 5:15 PM
    Moderator
  • According to the backup PM, the statement applies primarily to major (like 10.0) and point (like 10.5) releases, such as not restoring a SQL 2008 R2 (version 10.5) backup on an instance running SQL 2008 (version 10.0). It does not generally apply to things like service packs, cumulate updates, or hotfixes. There are some rare exceptions, such as if a service pack introduced a new feature.

    Databases get stamped with a version. A point release or full release will bump the database version. Things like SPs or CUs rarely bump database versions.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 8, 2011 12:45 AM
  • Thank you Alan for your accurate response.
    - Andrea Bianchi - site: http://www.BianchiAndrea.com
    Wednesday, February 9, 2011 8:57 AM
  • Thank you Papy, your post was very usefull.
    - Andrea Bianchi - site: http://www.BianchiAndrea.com
    Wednesday, February 9, 2011 9:00 AM