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.

5 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. 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
  3. Hello, I'm happy to see some great articles on your site. Would you like to come to my site later? My site also has posts, comments and communities similar to yours. Please visit and take a look 토토사이트


    ReplyDelete
  4. Welcome To Techbrothersit: Dba - Restore Sql Server 2012 Database Backup On Sql Server 2008 /2008 R2 >>>>> Download Now

    >>>>> Download Full

    Welcome To Techbrothersit: Dba - Restore Sql Server 2012 Database Backup On Sql Server 2008 /2008 R2 >>>>> Download LINK

    >>>>> Download Now

    Welcome To Techbrothersit: Dba - Restore Sql Server 2012 Database Backup On Sql Server 2008 /2008 R2 >>>>> Download Full

    >>>>> Download LINK Nk

    ReplyDelete
  5. Thank you very much for writing such an interesting article on this topic. This has really made me think and I hope to read more. riatoto

    ReplyDelete