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.