4

I need to upload a .bak file to SQL Server. How can I downgrade the database from SQL Server 2008 R2 to SQL Server 2008

thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Roy Amir
  • 457
  • 1
  • 6
  • 15

2 Answers2

11

You cannot. Period. That's never been possible with SQL Server (and most likely never will be).

If you have a database backup file from a 2008 R2 version, you cannot under any circumstances and with any tricks or tools restore that into a 2008 version. Just cannot be done.

You need to either find a way to do this using scripting (e.g. script out the SQL structure and/or data), or you could use a structure/data comparison tool like Red-Gate SQL Compare and Red-Gate SQL Data Compare to compare two databases. With Red-Gate's tools, you can also compare a live 2008 database against a 2008 R2 backup file and move data between them.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

We can generate scripts and we run them in lower version. steps to do this.

STEP 1: Right click database which you want to downgrade.you will see option to script that. in advanced section choose for which version you want to downgrade.

STEP 2: This will script your databse.

STEP 3: Copy the script on other machine and run following command.

SQLCMD -S LOCALHOST -d <databse_name> -i <scriptpath/name> –E

This link might help you more.

Shankar Morwal
  • 159
  • 1
  • 12
  • 3
    The OP has a backup file he wants to load into an older version. – Andrew Barber Dec 03 '12 at 13:15
  • thanks. ya if we have .bak file then we can not load that file in any way. But if we have access to source database then it can be done as i mentioned :) – Shankar Morwal Dec 03 '12 at 13:36
  • @AndrewBarber But if he has the backup file, could he not load to r2 and then use this answer to downgrade the source database to 2008? (Implying this answer is still useful now that it has been clarified) – Mansfield Dec 03 '12 at 13:47
  • @Mansfield With the line "This can be done" removed, I don't find the answer to be quite as bad. (I'm not the one who down voted, btw) – Andrew Barber Dec 03 '12 at 13:51
  • @AndrewBarber i am using this from past one year. sql server 2008 R2 have options to generate scripts from 2008,2005 and 2000. i think i didn't write my answer properly so – Shankar Morwal Dec 03 '12 at 13:57
  • @user1872670 I am not saying this does not work; Where do you see I've said any such thing? I simply pointed out your solution does not address the question asked - the OP had a backup file they wanted to 'upload' to a SQL 2008 instance. You don't address the fact that you can't do that. – Andrew Barber Dec 03 '12 at 13:59
  • @user1872670 If you clarified your answer to mention that he would need to load the backup file onto an r2 database and then follow your steps the answer would be much better. – Mansfield Dec 03 '12 at 14:24