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 –>
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.
Muthukkumaran
@Anonymous
Thanks glad you liked it.
I second that.Yep it depends.
Affiliate Marketer
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/
Kim
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
Muthukkumaran
Thanks for your encouraging words Kim. Glad you liked it.
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
Muthukkumaran kaliyamoorthy
Addision Philip
Thanks for your additional note.It will help for the readers.
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!
Muthukkumaran kaliyamoorthy
@bod I’m glad you liked it.
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