Meme Monday - Presents from Microsoft

05 December 2011

It is Meme Monday once again. The question this time is, "What should Microsoft leave under my tree this year?"

I want the ability to control my SQL Server databases using a Kinect. For example:

Hold a Knife = Kill a spid.

Point at the screen = sp_who2

Point and speak with a Boston accent = sp_whoIsActive

Kill a Kitten = Shrink a Database

Frown = Grant Permissions

Smile = Revoke Permission

Kick = Punish the developer who wrote LINQ on your production DB

Hold up a math book = Update Statistics

Permalink

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.aspx) 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:

Export-DbToBcp.ps1

Happy scripting!

Permalink