How to bulk export multiple tables from SQL Server

Maybe you have been in the same situation as I ? You need to bulk export multiple tables from SQL Server, and can’t find any option in the Import and Export Wizard within SQL Server 2005. At least I couldn’t find any out of the box functionality to do this. So I went ahead and wrote a small T-SQL script that uses the BCP command utility to perform this desired task.

 

SQL Script – Copy Code

If you try to run this on a SQL Server 2005, you may encounter this error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component
is turned off as part of the security configuration for this server.
A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.
For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

In that case you have to unlock the possibility to execute xp_cmdshell usi
ng this script:

SQL Script – Copy Code