locked
restore backup RRS feed

  • Question

  • Hi,

    Can I restore the backup of SQL Server 2012 CU1 on SQL Server 2012 CU10?

    Regards,





    • Edited by biltz Sunday, June 22, 2014 11:19 AM
    Sunday, June 22, 2014 11:19 AM

Answers

  • Yes you can restore hope you are talking about User database backup not system database.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Sofiya Li Monday, June 23, 2014 8:45 AM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:14 AM
    Sunday, June 22, 2014 11:48 AM
  • Ideally yes I guess as the major version is still the same and its just difference in cumulative pack.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Sofiya Li Monday, June 23, 2014 8:45 AM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:14 AM
    Sunday, June 22, 2014 11:49 AM

All replies

  • Yes you can restore hope you are talking about User database backup not system database.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Sofiya Li Monday, June 23, 2014 8:45 AM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:14 AM
    Sunday, June 22, 2014 11:48 AM
  • Ideally yes I guess as the major version is still the same and its just difference in cumulative pack.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Sofiya Li Monday, June 23, 2014 8:45 AM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:14 AM
    Sunday, June 22, 2014 11:49 AM
  • Hi,

    I was little cautious about system databases because of this support article

    http://support.microsoft.com/kb/264474


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Sunday, June 22, 2014 11:58 AM
  • Yes Offcourse User Database

    Thanks


    Bilal

    Sunday, June 22, 2014 12:24 PM
  • Yes Offcourse User Database

    Thanks


    Bilal


    No issues then you can very well restore.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Sunday, June 22, 2014 2:02 PM
  • Compatibility is also given for former versions of SQL Server, as you can e.g. restore a SQL Server 2008/R2 on a SQL Server 2012. The database will be upgraded during the restore process. But it never works the other way, once you went down the upgrade path, forever it will dominate your destiny.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss


    Sunday, June 22, 2014 4:40 PM
  • Compatibility is also given for former versions of SQL Server, as you can e.g. restore a SQL Server 2010 on a SQL Server 2012. The database will be upgraded during the restore process. But it never works the other way, once you went down the upgrade path, forever it will dominate your destiny. -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    SQL Server 2010?  Did you mean SQL Server 2008 or something else?  I'm unaware of a SQL Server 2010 release.
    Sunday, June 22, 2014 4:51 PM
  • Yes, 2010, the secret release :-) No just kidding, I was so thinking compat levels that I has the wrong digits in mind. Changed it not in order to confude anyway.

    Thanks for the headsup :-)

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Sunday, June 22, 2014 4:53 PM
  • It works like backup from lower version [which may be major version, cu, hotfixes] to heigher versions always work and this should be approach.

    backup from heigher versions usually give problem while restoring on lower versions of Sql Server.

    For example backup from Sql Server 2008 R2 Sp1 to 2008 R2 SP2 will be restored but not the opposite.

    So we should plan accordingly.

    If we have to follow other way then things will never be impossible but lot of process  coding has to be brought into.

    For example selecting data into  temp tables then loading into lower versions db versions or importing into excel and then loading into tables etc.


    Santosh Singh

    Sunday, June 22, 2014 5:43 PM
  • "For example backup from Sql Server 2008 R2 Sp1 to 2008 R2 SP2 will be restored but not the opposite."

    That is actually not right. I know what you mean, you mean the mistery about the numping of R2, which is sort of different 2008 (non R2) SP 2 <> 2008 R2 (SP1 or RTM)

    http://www.sqlskills.com/blogs/paul/sql-server-2008-r2-bumps-the-database-version/

    "For example selecting data into  temp tables then loading into lower versions db versions or importing into excel and then loading into tables etc."

    What do you mean by that ? If you create temp tables they will be not there after a restore anyway. And I did not get the Excel thing you are talking about...

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Sunday, June 22, 2014 6:31 PM
  • First pointer:

    "For example backup from Sql Server 2008 R2 Sp1 to 2008 R2 SP2 will be restored but not the opposite."

    For this, I was saying that backup taken from lower version let say Sql Server 2005  SP1 can be restored on Sql Server 2005 Sp4 or even on server with Sql Server 2008 R2 as while restoring or attaching databases, it will get upgraded.

    Now if I go just opposite then for example if I take backup form Sql Server 2008 R2 and tries to restore on Sql Server 2005 Sp1 or Sp4 then it will give a lot of problem or it will not let you proceed.

    --------------------------------

    Second pointer:

    "For example selecting data into  temp tables then loading into lower versions db versions or importing into excel and then loading into tables etc."

    What do you mean by that ? If you create temp tables they will be not there after a restore anyway. And I did not get the Excel thing you are talking about...

    Let me put this in this way: If I create temp tables then that table will be there and once data gets loaded using select statement or any other commands from these temp tables then these temp tables may be dropped based on your planning for brining database into lower version of sql server.

    But before going into this way, you need to design tables and db objects structure similar to heigher version has and then and then you only need to take care of data import part.

    Now for Excel, if you think that above means to do is little bit tough then you can export data into excel or any other csv or txt file and then you can easily import into lower version of sql server as DTS or other means[bcp\others] was there from 2000 onwards.

    Let me know if you still need any more clarification on these pointers.


    Santosh Singh

    Sunday, June 22, 2014 7:01 PM
  • I don´t need any clarification as I did not ask the question, this was the OP. But if you are answering the question it wold be good if the OP understands what you mean and you sure will see that the second explanation is a bit longer and more descriptive as the first one:

    "For example backup from Sql Server 2008 R2 Sp1 to 2008 R2 SP2 will be restored but not the opposite."

    But this statement is still not right in your first post. As long as the SP does not introduce an internal file schema change, this can be done without any problems.

    "If I create temp tables then that table will be there...."

    "Where?"

    "...and once data gets loaded using select statement or any other commands from these temp tables then these temp tables may be dropped based on your planning for brining database into lower version of sql server."

    Temp tables are temporary and you shouldn´t rely on them as being generally available anyway as the service might bounce anytime. In general I didn´t get that one too, he wants to migrate to a higher version, not a lower one.

    I guess we leave it to the OP to ask more questions that we will be able to help him.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Sunday, June 22, 2014 7:11 PM
  • Thanks Jens for all understanding!!

    "But this statement is still not right in your first post. As long as the SP does not introduce an internal file schema change, this can be done without any problems."

    Only trouble is I don't want to check at that level so wanted to be on safe side.

    "If I create temp tables then that table will be there...."

    "Where?

    ....."

    Temp table by that I meant, creating temp tables as not by @@temp table as that disappear by sessions. I had meant for example in original database let say table name is : emp then you can create a table with name like emp_temp in destination server where you need to migrate, so that after all your usage\validation, you can simply search and drop these tables.

    Thanks..


    Santosh Singh

    Sunday, June 22, 2014 7:21 PM
  • @@temp, you meant ##temp, right ? ;-)

    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Sunday, June 22, 2014 8:30 PM
  • >emp then you can create a table with name like emp_temp in destination server where you need to migrate

    That is called work table.  You are responsible deleting it when finished.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Monday, June 23, 2014 12:20 AM
  • There was typo, it was ##, but again depends on you, you want local or global variable.

    Yes, sure person doing it or if I am doing, it will be entirely responsible for deletion of all such tables or other db objects on that person\me.


    Santosh Singh

    Monday, June 23, 2014 5:44 AM