Database migration from 2008 R2 to 2005

My company should return to Sql Server 2005 with 2008R2 (obsolete scripting and client deployment problems ..), and I got the task to figure out how to do this: are there any automatic migration tools (Redgate, MS, anything) for this ?

There are several (about 10) databases, all of which contain from 20 to 220 tables.

If there are no known tools, is there a way in the form of scripts? I know that a SQL server can generate scripts (with and without data, I think?), But under what circumstances / limitations can schemas be transferred? If possible with data migration, but if not empty tables, they should do.

+4
source share
2 answers

@Rubens Farias This, unfortunately, does not work very well for complex databases with many foreign key restrictions, and sometimes for circular dependencies, as there are many script errors, and SQL Server Management Studio does not seem to be very smart about streamlining scripts.

I found a (a few more missing) way to do this, with the two mentioned marc_s commercial tools. We have our own tool for creating database scripts, so I first transfer the schema without SP and views, and then Sql Data Compare data, and then, using our internal DbScripter, I generate SP and Views. It works for statistics, even for full-text catalogs, very nice. But: at least in one case when the data set was very large, a deadlock occurred on the sql server when the data comparison migration tool was used. Still working on it, this is annoying.

Still not an ideal solution, but have not found a better one, so I will answer my answer, as was said ...

0
source

If you have Visual Studio installed, you can connect to your database, right-click on it and select "Publish to provider". There you can choose whether you want to generate a script for the database schema, for data only, or for both.

0
source

All Articles