Downgrade SQL Server database - from a higher version to a lower version

A backup of a Sql server database that was taken from a older version can be restored to a new version with ease (in most cases).
However, a database cannot be resotred into an older version. for example you cant restore a database backup that was taken in SQL Server 2008 R2 and restore it on SQL Server 2008 or SQL Server 2005.

If you are unfortunate enough to be in a situation where you have a backup of a newer version and you want to restore it onto an older version of SQL Server, here are the steps you need to do;

For this you will need access to two instances of sql server (They can be running on the same box or on seperate boxes);

• Instance 1: running the old version say SQL 2005, I will call it Destination2005
• Instance 2: running the new version say SQL 2008R2, I will call it Source2008R2

1. On the destination server, create a linked server called Source2008R2
2. Create an empty database on the Destination2005 server (this will become your database).
3. Using a tool like Redgate SQL Compare script the schema of the database on Source2008R2 and run it against the new (empty) database you have created in the previous step.
4. Create a script to disable the Foriegn key constraints and another script to enable foriegn key constraints. There are many blogs on the web for this, I got this from http://www.sqlmag.com/forums/aft/70529

Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

## execute updates here...

Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

1. 5. Now you have an empty database on Destination2005 instance with the same schema as the one on Source2008R2 and with all constraints disabled.
2. 6. Start importing the data by running the following script on Destination2005. This script will generate the insert statements required to import the data from the source.

/*

YB: A script to copy data from one database to another.

Run it in the destination database. AT YOUR OWN RISK..

*/

DECLARE

@TABLENAME NVARCHAR(100) ,
@ColumnName  NVARCHAR(100) ,
@HasIdentity INT,
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX),
@NewLine NVarchar(4)

SET @newline=NCHAR(13) + NCHAR(10)
DECLARE ParentCursor CURSOR FOR
SELECT Tab.TABLE_NAME, HasIdentity =CASE WHEN Ident.INCREMENT_VALUE IS NULL THEN 0 ELSE 1 END
FROM
INFORMATION_SCHEMA.TABLES Tab
LEFT OUTER JOIN SYS.IDENTITY_COLUMNS Ident ON Tab.TABLE_NAME=OBJECT_NAME(OBJECT_ID)
WHERE
TABLE_TYPE='BASE TABLE'
and TABLE_CATALOG='MyDatabaseName'
OPEN ParentCursor;
FETCH NEXT FROM ParentCursor INTO @TABLENAME, @HasIdentity
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT 'HAS Identity : ' + convert (VARCHAR(10), @HasIdentity);
SET @SQL1=N''
SET @SQL2=N''
DECLARE ChildCursor CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG='MyDatabaseName'
AND TABLE_NAME=@TABLENAME
AND DATA_TYPE <> 'Timestamp'
ORDER BY
Ordinal_position
OPEN ChildCursor;
FETCH NEXT FROM ChildCursor INTO @ColumnName
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL2 = @SQL2 +'['+ @ColumnName + '],'
FETCH NEXT FROM ChildCursor INTO @ColumnName
END
CLOSE ChildCursor;
DEALLOCATE ChildCursor;
   -- Remove the last , from @SQL2
SET @SQL2=LEFT ( @SQL2, LEN(@SQL2) -1)
SET @SQL1=@SQL1 + 'TRUNCATE TABLE [MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine + 'GO' + @NewLine ;
IF @HasIdentity=1
BEGIN
SET @SQL1 =@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] ON' + @NewLine
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END
SET @SQL1=@SQL1 + 'INSERT INTO [MyDatabaseName].[dbo].[' + @TABLENAME + '] (' + @SQL2 + ')' + @NewLine;
SET @SQL1=@SQL1 + 'SELECT ' + @SQL2 + ' FROM [Source2008R2].[MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine;

IF @HasIdentity=1
BEGIN
SET @SQL1=@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] OFF' + @NewLine;
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END

SET @SQL1=@SQL1 + 'GO' + @NewLine;
 

PRINT @SQL1

FETCH NEXT FROM ParentCursor INTO @TABLEName, @HasIdentity
END

CLOSE ParentCursor;
DEALLOCATEParentCursor;