2

Ok, officially this one is messing with my head. I had the worst idea to try out the SQL server Denali CPT3 when it came out on our development environment. And it seemed ok so we used and gathered a couple of databases on it.

Now we have an instance of SQL 2008 R2 and the team that was working on the Denali instance want's to switch to 2008 R2.

My task is to copy the database from the Denali instance to the 2008 R2 instance, but it proves to be a more difficult job than I thought. I have tried to attach the database using the mdf and ldf files but it didn't work. I have tried to backup from denali but it didn't work. I have tried to export/import the databases but it didn't work. I have tried to copy the database but it failed. The only thing seemed to slightly work was to generate scripts on the denali instance and run them in the 2008 R2 instance, but it fails when i try to generate for all the objects of the database.

I am wandering if there is some other solution that could work or if one of the solutions that i've tried should work and i need to pay more attention to it. I'm open to "crazy" solutions like transferring to mysql or oracle if possible (even csv or flat file, anything that makes possible the transfer of the whole database).

primero
  • 157
  • 1
  • 7
  • Basically the problem of restoring from a newer version of sql server to older version of sql server. – primero Jun 19 '12 at 18:20

1 Answers1

3

As you've discovered, you can't get there from here -- SQL server can't back-rev data files. Presuming your guys weren't using any features or datatypes that were 2012-specific, you should be able to use SSMS to generate SQL scripts which you could then execute against the 2008r2 instance. Even if there are some cases where it doesn't work, you've got a modifyable text file that you should be able to nudge over the target rather than a big ugly binary mess with strict requirements.

I haven't tried the 2012 version of SSMS, but in the 2008r2 version you right click on a database and choose generate scripts to start the wizard. Make sure to check out the options under the Advanced button in the Set Scripting Options step. If the tools built into SSMS fail you, you might want to check out RedGate's suite of tools -- they have some very advanced "script my sql" options.

Wyatt Barnett
  • 725
  • 5
  • 14
  • The generate script option is the one that gave me the feeling that i was on the right way, but i wasn't able to script to sql the Views the Stored Procedures and the User-defined Functions(someting about table not existing or not enough permissions, which would be weird because i'm logged in as sa) and i started from a 35MB database(i did the transfer on a test database) and ended up with a 5MB database. I will take a look at RedGate's suite, thanks for the info. – primero Jun 19 '12 at 18:43