DBA

Restoring a database from higher version to lower version SQL server

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

2. Import/Export

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

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

  • Anonymous

    Hi muthukkumaran,

    Its good post.

    An additionally there are many ways to do this but "it depends".

    Still you can use the BCP and CDC.

    If the data volume is huge then Replication/ BCP is good choice.

  • Pare

    Merely like to point out the page is as a revelation. The particular lucidity inside your write-up is great and even i was able to guess you’re an expert for this matter. Alright with your agreement well then, i’ll to seize the Rss feed to maintain up known by means of coming write-up. Bless you a mil and even remember to keep going this profitable work.

  • Addision Philip

    You can also do this by creating a SSIS package with Transfer SQL Server object task . This will create SQL server object and copy the data to the other database.
    Regards,
    Addision Philip
    SQL Disaster Recovery Specialist

  • Bod

    I love your blog, you should add an RSS feed feature so I can get automatic notifications of new blogs. If you set one up please email me! i will bookmark you for now. Again Excellent Blog!

  • Bod

    I especially like your last paragraph – and I did start a blog – two in fact – just a few months ago! I always write letters to my children at Christmas – sometimes more often, but at least once a year. They know their letters will be in their stockings! Probably wouldn’t do anything you said not to – just a little bit inhibited! But that’s just me. Congratulations on being freshly pressed!

  • Backup Creator

    Thanks Muthukkumaran kaliyamoorthy for the Great share of information, it was very helpful to me. I really enjoy the way you have framed your issues regarding this matter, keep up the great work. All the Best. John

  • Allaudhin

    Hi Muth,
    You are posted screen shot not able to view in Google Chrome.
    Kind do something.

    How to restore Lower version DB.bak to Higher version database

Leave a Reply

%d bloggers like this: