Sql-server – SQL Server 2008 – How to schedule a copy of view to another database

20,000/60 = 333 records per minute is not that rate where you worry about inserts, even if they are burst. Of course it depends on your hardware, but since your table is rather huge, you need something big. So I would defenitely create 1 or 2 indexes (you can omit clustered) to speed up your queries.

At a higher insert rate you probably go with partitioning and split PAGELATCH contention between partitions. Or you could use a variation of GUID clustered primary key. There is a lot of arguing here and you may want to check Tom Kejser blog post here.

Also there is no shame if you go below 3NF. People create highly denormalized data shops just for reporting purposes. Since you have enterprise edition you could use CDC (Change Data Capture) technique and create SSIS package to update reporting table, at a time you need fresh data.

Given that you can connect to the database you may actually be in pretty good shape. There are a number of methods you can use that might work but here is one.

1. Create a new database preferably on another disk because the possibility is that your disk is corrupt.

2. Use generate scripts to generate a script for your existing database. Include all database objects and permissions.

3. Run the script on your new database to create all of the structures you need.

4. Either manually or through a tool (Import/Export Wizard for example) copy the data from each of the tables on your corrupt database into the new database. Unfortunately this is the step most likely to have problems. If you do have a problem it will hopefully be with only a few records and you can use the Import/Export Wizard to generate an SSIS package and change your error setting for that particular table to ignore failures.

Either way once you are done take a backup. (Of course)