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 StatisticsPermalink
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: