SQL Server 2008: recreating the same indexes and statistics across different databases

Question

I've recently been assigned the task of updating a "legacy" database (previously managed by an ex-coworker who left the company a while ago). The new database has the exact same structure as the previous one; the only difference is in the content itself, as this database now has more recent data.

The problem is that the old database has a lot of indexes and statistics that I also have to implement in the newer database and I'm afraid the database creation scripts were lost somewhere along the highway.

Hence, how can I (preferably using transact SQL) recreate the old indexes on the new database?

Cheers guys

Answer 1

In SSMS you can right click on an Index in the object Explorer and click "Script Index As". You could then run the script against your new database.

EDIT:

If you have a number of Indexes, you can't script them all in SSMS 2005 very easily. You could look at a script similar to this: http://www.sqlservercentral.com/scripts/Miscellaneous/31893/

Another interesting way could be to use C#, see http://blogs.msdn.com/benjones/archive/2008/08/27/how-can-i-generate-a-t-sql-script-for-just-the-indexes.aspx for an example.

Answer 2

Try following these steps

• Right Click on the database and select Tasks, then Generate Scripts...
• Select a the database again then click Next (Note: You could just use the tick box at the bottom here and script all objects in the DB and finish - save some time!)
• Drag the bar down until you get to the Table/View Options
• Make sure Script Indexes is set to True, then click Next
• Select the required objects to script, then click Next
• Select the items to script
• Finally choose if you want to script to file/clipboard/query window

Answer 3

I would look into investing in Red Gate tools, which will do this easily (indexes, data and objects can be synchronized).

If you really need the exact same statistics on both databases, that is hard to do, especially if you don't update statistics manually. I would just worry about what Red Gate can synchronize first.