0

I am trying to attaching microsoft sql server 2008 database into msde 2000 but unable to attach. i googled many sites but still cant find solution. the only thing i found is that newer version cant attached into older version servers...

Please suggest me what can i do? is any way to attach database?

akkhatri
  • 1
  • 4

2 Answers2

0

There is no way to achieve this - you can never go back in time with SQL Server.

That is: you cannot with any method or trick or hack or workaround attach or restore a newer database (file) to an older SQL Server version.

No way. Doesn't work. Don't even try it. Nope.

You'll need to find a way to synchronize the two databases - both their structure (tables, views, stored procedures etc.) and their data (contents of the tables) - manually, e.g. by using T-SQL scripts to update one database from the other, a tool to help you with that, or whatever means you want to use.

But you cannot in any way, shape or form detach/attach or backup/restore newer SQL Server database files onto an older version.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    @akkhatri: this will **NOT** work as I've already said in my response. There is **NO way** (no script, no tool, no hack, no black magic or voodoo) to do this. Period. – marc_s Aug 30 '12 at 10:37
  • Well there is a way--see "what you need to do is", its not trival, but its is possible with caveats. I've done it. It is not possible simply by detaching / backing up and attaching / restoring. The data engine inside SQL checks for versions of the database 1st thing. Then the earlier database engine recognises that it does not support features that had not been invented until the later version was released and that it has no way of knowing about. Dont know why youd want to do this, the SQL express 2008 (R2) is free and provided the 2008 db is smaller than 10GB, you can just attach it. – Ian P Aug 30 '12 at 10:59
  • @IanP: yes - I know - I mentioned: you cannot do it the "easy" way - you need to use scripts and/or tools to handle both structure and data separately. – marc_s Aug 30 '12 at 11:05
0

What you need to do is:

script out your database structure including stored proceedures, indexes, functions etc. (there is a wizard in SQL management studio).

create an empty database on your MSDE instance.

run the script, making changes where any types of object are not supported in the earlier version of SQL

Bulk insert the data from the later version into the earier version (once again making changes to overcome errors because features are not supported in the earlier version) There is a wizard in in SQL management studio. (Doesnt fix your errors)

Ian P
  • 1,724
  • 1
  • 10
  • 12
  • Yes, that's basically what I said in my response, too - detach/attach or backup/restore won't work, you need to do the work manually using scripts and/or tools .... – marc_s Aug 30 '12 at 11:04
  • Hi Marc, sorry, I associate the word syncronise with replication. However it can be done without resort to commercial tools (Redgate) in your link. If it were a once only event, then I think Redgate do a trial. It can be done using the scripting tools in the various editions of SSMS. I agree with all posters that it cannot be done via attach detach or backup restore. In fact I gave your comment a +1.. – Ian P Aug 31 '12 at 07:58