Export a database using Powershell
28 November 2011
As a SQL developer, I always want access to more data. As a sometime DBA, I have to manage requests from people wants data. Most people are satisfied with query access to a mirror/standby/backup.
Some users need more. A common workaround is to provide a production backup. However, most users don’t have hard drives large enough for a production backup and restore.
I found myself in a situation like this recently. My workaround is to use bcp.exe to query out the data into files, along with format files and CREATE TABLE statements. Then I can re-create the database one table at a time. To make this easy, I wrote a Powershell script to do this, and used the SQLPX Powershell Extensions (these are free, community-written scripts that anyone can use).
My original script was just 14 lines of script. After I cleaned it up, it become ~60 lines of script:
Happy scripting!