DBA - Restore SQL Server 2012 Database Backup On SQL Server 2008 /2008 R2

If you have taken the backup from SQL Server 2012 and trying to restore on SQL Server 2008 or SQL Server 2008 R2. You will get below error if tried to restore by using SSMS(GUI).


Backward compatibility is not supported. If the backups are taken from newer version then they can not be restored to old version of SQL Server. But backups taken from older versions of SQL Server can be restored to new version of SQL Server.

So what are out best options, if we have to have restore SQL Server 2012 Database to SQL Server 2008/2008 R2?

1--Generate Scripts with Data
We can generate the scripts of entire Database with Data and then run that .sql file on Target server to restore all objects. Step by step Link

If the .sql file is small, it will be easy to open the file in SSMS and execute. If you have scripted a big database, you might not be able to open the .sql file in SSMS. Here is the Link , how to run big sql file.

2--Use Import Export Wizard
Here are the steps if you are going to use Import Export Wizard
i) Create Empty Target Database
ii) Generate Scripts for all the objects from Source Database.
iii) Execute Generated Scripts in step ii.
iv) Use below script to disable all Constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
v) By using Import/Export Wizard, Load the data from Source Database tables to Destination Database tables.
vi) Enable Constraints by using below script
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

3--Create SSIS Package
Create SSIS Package with specific requirements and load only data which is required instead of restoring entire database.

13 comments:

  1. MS SQL Server comes with an impressive tool set. Components such as SQL Server Profiler, SQL Server Management Studio, BI tools and Database Tuning adviser can save you a lot of troubleshooting, development, and administration time. Additionally, as a core product in the Microsoft technology stack, MS SQL Server support and documentation is extensive. Thank you!!
    ============================
    https://astarios.com/big-data-analytics/

    ReplyDelete

  2. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    http://chennaitraining.in/sap-rem-training-in-chennai/
    http://chennaitraining.in/sap-scm-training-in-chennai/
    http://chennaitraining.in/sap-sd-training-in-chennai/
    http://chennaitraining.in/sap-sm-training-in-chennai/
    http://chennaitraining.in/sap-srm-training-in-chennai/
    http://chennaitraining.in/sap-success-factor-training-in-chennai/
    http://chennaitraining.in/sap-tr-training-in-chennai/
    http://chennaitraining.in/sap-wf-training-in-chennai/

    ReplyDelete
  3. Congratulation for this successful project. Can you please share some pictures of the your project. I am going to share this post with my friends. 먹튀검증

    ReplyDelete
  4. I think a lot of articles related to are disappearing someday. That's why it's very hard to find, but I'm very fortunate to read your writing. When you come to my site, I have collected articles related to 안전놀이터.

    ReplyDelete
  5. That's a great article! The neatly organized content is good to see. Can I quote a blog and write it on my blog? My blog has a variety of communities including these articles. Would you like to visit me later? 온라인홀덤

    ReplyDelete
  6. Hello ! I am the one who writes posts on these topics 메이저놀이터 I would like to write an article based on your article. When can I ask for a review?

    ReplyDelete
  7. I’m not sure exactly why but this weblog is loading incredibly slow for me. Is anyone else having this problem or is it a problem on my end? I’ll check back later on and see if the problem still exists. 안전놀이터순위

    ReplyDelete
  8. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article :D 안전놀이터순위

    ReplyDelete
  9. Thank you so much for such a well-written article. It’s full of insightful information. Your point of view is the best among many without fail.For certain, It is one of the best blogs in my opinion. 안전놀이터추천

    ReplyDelete
  10. What an interesting story! I'm glad I finally found what I was looking for Kèohay.

    ReplyDelete
  11. Thank you a bunch for sharing this with all of us you actually realize what you are talking about! Bookmarked. Please also seek advice from my site =). We could have a hyperlink change contract between us 메이저놀이터

    ReplyDelete
  12. Awesome article! I want people to know just how good this information is in your article. It’s interesting, compelling content. Your views are much like my own concerning this subject 안전한놀이터 It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.

    ReplyDelete
  13. You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. 메이저놀이터순위

    ReplyDelete