rake db:schema:dump show no fields with rails 3.2.3 and SQL Server 2008

rake, ruby, ruby-on-rails, ruby-on-rails-3, sql-server

Solution

Short answer:

`db:schema:dump` isn't the right thing to use, but you can add a few lines of code to your Rakefile to get the outcome you want.

Longer answer:

The scuttlebutt is that the task `db:schema:dump` is actually not supposed to dump anything more than the structure. (I know, it's a misnomer.) It's analagous to `db:structure:dump`, except that the one gives you an .rb file, and the other gives you a .sql file.

You can create your own dumping rake task by appending the following code to your Rakefile:

For SQL 2008

task :mydump do
  ActiveRecord::Base.connection.execute(
    "dbcc traceon(2544, -1) \n go \n dbcc traceon(2546, -1) \n go \n dbcc stackdump"
  )
end

Using the SQL server itself to create the dump (which is what the forgoing code does) limits you because the dump will always go to your log directory; you cannot specify otherwise.

If you use SqlDumper or some other utility, you will have more freedom. You can call such a utility from your rake task by executing it as through from the command line, using the `system` method. (See the example for MySQL below, which uses the mysqldump utility.)

(I have not tested the forgoing code, not having an installation of SQL 2008, myself, but the raw SQL code for creating a dump from with the SQL server is explained on this blog.)

Running the rake task

Then on your command line, call `rake mydump` or `rake mydump RAILS_ENV=production`.

For MySQL

You could do something similar for MySQL with the following:

task :mydump do
    config   = Rails.configuration.database_configuration[Rails.env]
    system "mysqldump -h #{config["host"]} -u #{config["username"]} -p#{config["password"]} #{config["database"]} > db/dump.sql"
end

Problem

I am migrating an app from rails 2.3.8 to 3.2.3. The 'rake db:schema:dump' works fine in rails 2.3 but generates only the tables names with no column names in rails 3.2. Even for the app to connect successfully through the console, I had to change config/application.rb to include ``` ActiveRecord::Base.table_name_prefix = 'dbo.' ``` Do I need to do something different for the rake task to pick up these prefixes? Or is something else causing the missing column names problem? Further clarification: I am looking for rake db:schema:dump because the programmers on site stopped using migrations and started making changes to the db directly. Now I am trying to restart using migrations. The first step recommended in the process is to use the schema dump as the starting point. Also, (and I am not sure) it is needed for the tests to rebuild the test db from the development db.

Original source