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.

7 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