Often on data recovery cases, we need to extract all the user table data into a new database. There are multiple options to perform this task:

1. Export/Import Wizard

2. Custom SSIS Task

3. Copy Database Wizard

4. BCP operations

5. SELECT INTO T-SQL commands

I find that the last option is the easiest. I use the following script to get all the T-SQL SELECT…INTO commands for all user tables in the old database:

 select 'select * into <new db>.'+b.name+'.['+a.name+'] from <old db>.'+b.name+'.['+a.name+']' from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id where a.type = 'U' 

Note: You will have to replace the new database and old database names with the appropriate names. The above script only works for versions of SQL Server 2005 and above.