Copy the data from the corrupted table into a new table by creating a dummy table, and copying the old data into the dummy table.
You can create the new table in any database (except model) where enough space is available. Follow these steps:
Check the table size that you want to copy, as follows:
1> sp_spaceused table_name 2> go
Check the amount of available space in the database in which you plan to create the new table:
1> use database_name 2> go
1> sp_spaceused 2> go
The easiest way to copy the table into a new one is to select all the data from your corrupted table into a temporary table. This way, you can skip step 3.
If space is too limited to create your table in any database, you may back up the data to an operating system file. Refer to “Back Up Data to an Operating System File”.
Enable the select into/bulkcopy option on the database where you want to create the new table. You do not need to enable the select into/bulkcopy option on tempdb, as tempdb already has this option enabled. For more information about enabling the select into/bulkcopy option on a database, refer to Error 268.
After you have run a select into command or used non-logged bulkcopy to move data into a database, you cannot perform a transaction log dump to a device. Therefore, once you have made unlogged changes to your database, issue a dump database command.
Setting the select into/bulkcopy option to “on” still allows you to use dump transaction database_name with truncate_only.
WARNING! Be careful about running select into across databases if you have column names that exist in both databases, as this may cause problems.
Copy the old table into the new table:
1> select * into database_name..new_table 2> from old_table 3> go
Or, if you select all the data into a temporary table:
1> select * into tempdb..new_table from old_table 2> go
Drop the original table.
Use sp_rename to give the new table the old name.
Recreate all views, triggers, stored procedures, constraints, defaults, and so on that referenced the table.
An alternative approach is to bulk copy data out of the old table into a file and bulk copy back into the new table.
