How to Migrate Database From Higher version to Lower version.

Step1:

1. Generate scripts for source (Database) server
   
   Right click → Task→ Click next → Choose advanced option → choose script create, script indexes true, script foreign key true → click Okay
   
   Please refer below screen shot

Step 2:

Export generate script file in destination server

And execute generate script file in destination server

Step 3:

Use below generate foreign keys script in destination (Database) server

— — SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyID int
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000)
declare @ParentColumn varchar(4000)
declare @ReferencedTable varchar(4000)
declare @ReferencedColumn varchar(4000)
declare @StrParentColumn varchar(max)
declare @StrReferencedColumn varchar(max)
declare @ParentTableSchema varchar(4000)
declare @ReferencedTableSchema varchar(4000)
declare @TSQLCreationFK varchar(max)
— Written by Percy Reyes www.percyreyes.com
declare CursorFK cursor for select object_id — , name, object_name( parent_object_id)
from sys.foreign_keys
open CursorFK
fetch next from CursorFK into @ForeignKeyID
while (@@FETCH_STATUS=0)
begin
set @StrParentColumn=’’
set @StrReferencedColumn=’’
declare CursorFKDetails cursor for
select fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
from — sys.tables t inner join
sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
inner join sys.columns c1 on c1.object_id=fkc.parent_object_id and c1.column_id=fkc.parent_column_id
inner join sys.columns c2 on c2.object_id=fkc.referenced_object_id and c2.column_id=fkc.referenced_column_id
inner join sys.tables t1 on t1.object_id=fkc.parent_object_id
inner join sys.tables t2 on t2.object_id=fkc.referenced_object_id
where fk.object_id=@ForeignKeyID
open CursorFKDetails
fetch next from CursorFKDetails into @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
while (@@FETCH_STATUS=0)
begin
set @StrParentColumn=@StrParentColumn + ‘, ‘ + quotename(@ParentColumn)
set @StrReferencedColumn=@StrReferencedColumn + ‘, ‘ + quotename(@ReferencedColumn)

fetch next from CursorFKDetails into @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
end
close CursorFKDetails
deallocate CursorFKDetails

set @StrParentColumn=substring(@StrParentColumn,2,len(@StrParentColumn)-1)
set @StrReferencedColumn=substring(@StrReferencedColumn,2,len(@StrReferencedColumn)-1)
set @TSQLCreationFK=’ALTER TABLE ‘+quotename(@ParentTableSchema)+’.’+quotename(@ParentTableName)+’ WITH CHECK ADD CONSTRAINT ‘+quotename(@ForeignKeyName)
+ ‘ FOREIGN KEY(‘+ltrim(@StrParentColumn)+’) ‘+ char(13) +’REFERENCES ‘+quotename(@ReferencedTableSchema)+’.’+quotename(@ReferencedTable)+’ (‘+ltrim(@StrReferencedColumn)+’) ‘ + char(13)+’GO’

print @TSQLCreationFK

fetch next from CursorFK into @ForeignKeyID
end
close CursorFK
deallocate CursorFK

Save the result in notepad file.


Step 4:

Drop foreign keys in destination (Database) server use below script or link

— — SCRIPT TO GENERATE THE DROP SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyName varchar(4000)

declare @ParentTableName varchar(4000)
declare @ParentTableSchema varchar(4000)

declare @TSQLDropFK varchar(max)

declare CursorFK cursor for select fk.name ForeignKeyName, schema_name(t.schema_id) ParentTableSchema, t.name ParentTableName
from sys.foreign_keys fk inner join sys.tables t on fk.parent_object_id=t.object_id
open CursorFK
fetch next from CursorFK into @ForeignKeyName, @ParentTableSchema, @ParentTableName
while (@@FETCH_STATUS=0)
begin
set @TSQLDropFK =’ALTER TABLE ‘+quotename(@ParentTableSchema)+’.’+quotename(@ParentTableName)+’ DROP CONSTRAINT ‘+quotename(@ForeignKeyName)+ char(13) + ‘GO’

print @TSQLDropFK

fetch next from CursorFK into @ForeignKeyName, @ParentTableSchema, @ParentTableName
end
close CursorFK
deallocate CursorFK

Save result in notepad file

Step5 :

Export datas in source database to destination database

Step 6 :

Select The Database which database you want to Migrate and for refernces above screen shot.

Once completed the above steps,select the destination server name.

We can provide the server Name and Authentication and create the new Database.

Finally Check Database.