•  
  • DBA
  • Restoring a database from higher version to lower version SQL server

Restoring a database from higher version to lower version SQL server

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.

Muthukkumaran kaliyamoorthy

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

More Posts - Website

Share this
12 Comments - Leave a comment
  1. Anonymous says:

    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.

  2. Muthukkumaran says:

    @Anonymous

    Thanks glad you liked it.

    I second that.Yep it depends.

  3. Affiliate Marketer says:

    This post excellently highlights what the author is trying to communicate. Nonetheless, the article has been framed excellently well and all credits to the author. For more information on how to load balance your web servers, please visit ..
    http://serverloadbalancing.biz/wordpressbiz/,
    http://serverloadbalancing.info/wordpressinfo/

  4. Kim says:

    Hey,

    Wonderful blog. This is an amazing information for me i am very thankful to you for providing this information. It will help me.

    Regards
    Kim Roddy
    Disaster recovery file server

  5. Muthukkumaran says:

    Thanks for your encouraging words Kim. Glad you liked it.

  6. Pare says:

    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.

  7. Addision Philip says:

    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

  8. Bod says:

    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!

  9. Bod says:

    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!

  10. 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

Leave a comment

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

*


Welcome , today is Monday, April 21, 2014