PROBLEM: I need to know if a database has Service Broker enabled. SOLUTION: The query I have listed below will provide you with the information you are looking for and if a database master key has been created for a database.

PROBLEM: Sal Young is a member of your DBA team and today is his last day at work. You need to find out if his login is mapped to any database throughout your MS SQL environment. SOLUTION: I used Central Management Server, PowerShell, and SQLPSX. I queried my Central Management Server to get the name […]

PROBLEM: I need to change the owner of several SQL Server Agent Jobs. Is there an easy way to do it? SOLUTION: Use PowerShell and SQLPSX. I use the Get-AgentJob function from SQL Server PowerShell Extension (SQLPSX) to find the jobs I need to modify. I need to change ownership of all SQL Server Agent […]

PROBLEM: I need a T-SQL script to remove a login from all SQL Servers in my environment. SOLUTION: My solution includes the use of MS SQL 2008 Central Management Servers and the script listed below. This script is a work in progress because I’m constantly adding, removing and updating features as I encounter problems. Maybe […]

PROBLEM: You need to copy the table definition for all tables in a database between two SQL servers. SOLUTION: One of the requirements is to use PowerShell and I will use it in combination with SQLPSX. Yes, I will use SQLPSX again and you probably have noticed I use it in all my PowerShell scripts […]

PROBLEMA: Necesitas copiar el esquema de tablas de un servidor Microsoft SQL a otro servidor SQL. SOLUCION: Para resolver este problema utilizo PowerShell y el SQLPSX. Si sigues este blog, podrás notar que utilizo la extensión SQLPSX de SQL para PowerShell en todos mis PowerShell scripts de base de datos. Hay algunas líneas en el […]

I read about T-SQL Tuesday #15 this evening while riding the train on my way home; I need to get in the scoop of these events when they first come out. How to Review SQL Logs with PoweShell describes one of the many scripts I created in PoweShell to automate my daily tasks. I have […]

PROBLEMA: Tienes un directorio con muchos archivos y deseas cambiarles la extensión sin tener que hacerlo uno a uno. SOLUCION: Puedes lograr tu objetivo utilizando PowerShell y hacerlo con 3 líneas de código. CLEAR CD [PATH TO FILE LOCATION] Get-ChildItem *.[FILE EXTENSION]|Rename-Item –NewName {$_.Name –replace “[FROM VALUE]”,”[TO VALUE]”} Siempre me gusta empezar mis scripts con […]

During a smoke break, Mike and Pat (two suits for a global consulting firm) were having one of those vainglorious conversations about who spends more on what, who has the larger whatever and who has the latest gadget. Their conversation was interrupted as the CTO and smoking pal, Jeff showed up and joined the two […]

PROBLEM: You need an easy way to review the MS SQL Server logs of all the servers you manage. SOLUTION: Use the Get-SqlErrorLog function from the SQL Server PowerShell Extensions (SQLPSX). I will show you some of the things you can achieve with the use of the Get-SqlErrorLog function and a bit of creativity.  We […]