PROBLEMA: Tengo base de datos donde el propietario no es “sa”, cómo puedo cambiar el propietario de varias bases de datos a “sa” de una forma dinámica. SOLUCION: Yo utilizo el cmdlet Get-SqlDatabase del módulo SqlServer para capturar las bases de datos de una instancia de SQL Server. Entonces hago una búsqueda en el objeto … Read more
PROBLEM: Database ownership is set to the user who created or restored the database and I want to change it to “SA”. SOLUTION: I use the Get-SqlDatabase cmdlet from SqlServer module to capture the list of databases from the SQL instance. I then pipe and search for databases where the “.owner” property is not “sa”. … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
PROBLEM: You need to script all stored procedures from a database. Each stored procedure needs to be on its own file so you can add them to a source control repository. SOLUTION: The solution here is a variation of what we did for “How to Script All Tables From a Database with PowerShell”. The only difference … Read more
PROBLEM: You need to script all the tables from a database. Each table needs to be on its own file so they can be added to a source control repository. SOLUTION: The use of SQL Server PowerShell Extensions (SQLPSX) facilitates the solution I’m about to show. The first example is a straight to the point, … Read more