DBA

Restoring a database from higher version to lower version SQL server

May 28, 2011
Advertisements

Restoring a database from higher version to lower version SQL server

How to downgrade a database from higher version to lower version?

There is no direct way to do this like Backup/Restore. You can use an alternative ways.

The alternative ways are,

 

1. Generate Script

2. Import/Export

3. Transaction replication

You may use the alternative ways. It depends upon your environment.

Restore errors

A backup has taken from higher version (SQL 2008/2005)

BACKUP DATABASE dba_test TO DISK='E:\mu\dba_2008.bak'

Restore the backup to lower version (SQL 2005/2000)

 

RESTORE FILELISTONLY FROM DISK ='E:\mu\dba_2008.bak'

 

Restore error from SQL server 2005 to SQL server 2000

Server: Msg 3205, Level 16, State 2, Line 1

Too many backup devices specified for backup or restore; only 64 are allowed.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

 

Restore error from SQL server 2008 to SQL server 2005

Msg 3241, Level 16, State 7, Line 1

The media family on device ‘E:\mu\dba_2008.bak’ is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

 

1. Generate Script with data 2008

 

Higher_to_lower_1Higher_to_lower_2

2. Import/Export

Right click the database–> Tasks–> Export data–> Next–> choose the data source –>

Higher_to_lower_Import_export

Click next –> Finish

3. Transaction replication

I hope this article will help someone.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

13 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + 5 =