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.