I need to move the aspnetdb from the development machine to the test machine. The development is SQL server 2008, the test one is 2005. I tried to script the aspnetb schema+data and then run it on the test, which worked fine and the database was populated, however when used is my asp.net 4 application it gave the following error
The 'System.Web.Security.SqlRoleProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.
I wonder what am I doing wrong and how to solve this. Strongly preferably without having to run the aspnet_regsql.exe tool on the test machine.
I don't think you did anything wrong necessarily, but there are only a few options to choose from when 'downgrading' a database. 1) Replicate the database to the lower version, 2) log ship to the lower version, 3) copy the schema and data, as you have done, I would use SSIS for this task however.
The error you are seeing however, looks more to be a collation issue. Check that your source and target databases are set to use the same collation settings.
Also take a look at this post The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'