Coping Indexes from old database to new one?

Question

Hi, I converted an old sql server database from 2000 to 2005 and forgetten to add old indexes to the new db.

is there any way to copy the old indexes to the new db?

any help?

Answer 1

You cannot copy indexes from one location to the other. They have to be built at the new location.

You can script out all your indexes in the old database, and take them over to the new one.

Right click on the database and under tasks, choose Generate Scripts. Mark the checkbox for all objects and then in the options, choose to script indexes. Run this script on the new database (it will take a while depending on the size of the tables.

Answer 2

If there aren't too many tables, you could generate a script from SSMS with all the objects and their indexes in it, and then just delete the CREATE TABLE statements, leaving the indexes.

If there are a large number of tables you're dealing with, it might be easier to use a tool like Red-Gate's "SQL Compare" or Visual Studio Database Edition to do schema comparison and generate the missing indexes.