Tips
And here starts the Powershell Series
Most DBA's have a hard time coming to terms with programming languages, and although I've used close to 10 in my history I've always defaulted back to my tried and trusted T-SQL to cater for my data-focussed needs...
Backup SQL Server Databases with a Windows PowerShell Script
In a previous tip on Backup and Restore SQL Server databases programmatically with SMO, you've seen how you can use Windows PowerShell to backup and restore SQL Server databases. In this tip, I will cover how to use Windows PowerShell to generate SQL Server backups.
Check the Last SQL Server Backup Date using Windows PowerShell
In a previous tip, Retrieve List of Databases and their Properties using PowerShell, you have seen how you can use Windows PowerShell to audit database properties and use Microsoft Excel to generate a report. Part of the daily SQL Server DBA tasks is to check the backups of all the databases in a SQL Server instance. How do we use Windows PowerShell to check for the last backup date of SQL Server databases and to confirm that they still meet our service level agreement?
Checking SQL Server Agent jobs using Windows PowerShell
Checking for SQL Server Agent jobs and their status is part of your daily task as a DBA. How do we use Windows PowerShell to check for SQL Server Agent jobs?
Connect to SQL Server via Windows PowerShell with SQL Server authentication
In previous tips on Windows PowerShell with SQL Server, you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. Most of the examples have used Windows authentication to connect to SQL Server. How can I connect to SQL Server via Windows PowerShell using mixed mode authentication?
Enable PowerShell on Windows Server 2008 R2 Core Edition
As we all know, Microsoft has announced support to run SQL Server on Windows Server 2008 R2 Server Core Edition which is GUI-less OS. In this tip I will explain the step by step process to install and enable PowerShell on a Windows 2008 R2 Server Core. PowerShell is basic prerequisite to install SQL Server on Windows Server 2008 R2 Server Core Edition. Check out this tip to get started with the PowerShell installation.
Enable Powershell Remoting on SQL Server Instances
Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? In this tip, I will describe a step by step process to enable PowerShell remoting on your SQL Server instances so that you can access all of your servers from your local machine via PowerShell.
Find SQL Server Instances Across Your Network Using Windows PowerShell
As more and more SQL Server instances get installed in my organization by different departments in both servers and desktops, I need to find out where they are as I'll be responsible for managing and administering these instances. In this tip I show you how this can be done easily with PowerShell.
Generating SQL Scripts using Windows PowerShell
In a previous tip on SQL Script Generation Programmatically with SMO, you have seen how you can use SMO to generate SQL scripts programmatically. In this tip I will cover how to generate scripts programmatically using Windows PowerShell.
Introducing PowerShell V2 Remoting for SQL Server
PowerShell can be used to for many SQL administrative tasks and in this tip we walk through how to run T-SQL code and admin tasks on remote servers.
Introduction to Windows PowerShell for the SQL Server DBA Part 1
I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowdays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.
Introduction to Windows PowerShell for the SQL Server DBA Part 2
I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.
Invoke Nz Powershell script similar to Nz VBA function
I got a few pings about the Restore-SqlDb script requiring the Invoke-Nz script to execute - here it is. Similar functionality to that of the Nz function in VBA - basically pass along 2 values and the function will return the first value if it is present (i.e. non-null), otherwise the second (could also say it's similar to the isnull() tsql function, or a coalesce with only 2 parameters). Sorry I missed this with the original Restore-SqlDb posting.
Iterating through SQL Servers and database objects with PowerShell
I've read several articles on MSSQLTips about executing queries against multiple servers or databases. I found advice about using the Local Servers Group to run a query against more than one SQL instance, about Central Management Server, about sp_MSforeachdb or about using the Management Studio query editor in SQLCMD mode. What about PowerShell? Can I use PowerShell to run queries against several instances, databases and database objects?
PowerShell changes for database backups in SQL Server 2012
PowerShell has many features and one set of features is the ability to backup SQL Server databases. In this tip we look at PowerShell enhancements for SQL Server 2012 to backup SQL Server databases.
Powershell Series #1 Setting up Powershell
Installing PowerShell version 1.0.
PowerShell Support in SQL Server 2008 with the Invoke Sqlcmd Cmdlet
In my organization we write scripts to perform all sorts of administrative functions on our many SQL Server instances as well as other server products. We are just getting started with using PowerShell and have heard that SQL Server 2008 provides some built-in support for executing T-SQL commands from within PowerShell. Can you provide some details and examples?
Removing HTML tags from text data using PowerShell
In this tip I'll address the same problem as a previous tip using CLR, but to get the job done I'll use PowerShell. PowerShell will let me "clean" the string data and export the result to a .csv file in a single step. The tip I've mentioned before covers only the data "cleaning".
Restore-SqlDb Automate a SQL Server Database Restore improved with Powershell
UPDATE: This script requires the Invoke-TryCatch.ps1 and the newly posted Invoke-Nz.ps1 scripts as well - thanks to all who pinged me about the Inovke-Nz.ps1 being required.
Retrieve a List of SQL Server Databases and their Properties using PowerShell
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you have seen how you can use Windows PowerShell and SMO to administer SQL Server databases. In this tip I translate some of the Transact-SQL scripts that I use every day to PowerShell scripts, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.
Retrieve Default SQL Server Backup Folder using PowerShell
In a previous tip on creating a Function to Return Default SQL Server Backup Folder , you've seen how you can create a T-SQL function to query the registry and retrieve the default SQL Server Backup folder. Is there an easier way to do it in Windows PowerShell?
Scheduling a SQL Server PowerShell script using the Task Scheduler
Even if your SQL Server edition does not include the SQL Server Agent service (if you use SQL Express, for instance), you can still automate administration tasks without using any 3rd party products. In this tip I'll describe how to schedule a PowerShell script with the Task Scheduler MMC snap-in. This applies to Windows 2008 Server, Windows 2008 Server R2 and Windows 7.
Try Catch Finally with Powershell
I ran across Adam Weigert's posting/script that provides a PoSh script that allows try/catch/finally like behavior - very cool. I started using the script myself and found that as much as I loved it, I wanted to add some additional 'features' to what it provides. I was looking for pipeline support (including the ability to throw 'soft' errors in the pipeline that would allow the pipeline to continue processing), some optional verbose debugging/error related information (particularly handy when using the script in a large distributed environment and you need information in your error logs about where the error came from, what system the script failed on, etc.), alias configuration, and further support for allowing friendly dot-sourcing of th
Using PowerShell for SQL Server Database Backups
I have seen some of your SQL Server Backup tips. I am starting to learn PowerShell and want to see how I can use it for common DBA tasks. How can I create a basic PowerShell script to backup all my user databases? Check out this tip to learn more.
Using PowerShell for SQL Server Transaction Log Backups
How can I use PowerShell to do a Transaction Log backup for SQL Server databases? In this tip we look at an alternative way of creating transaction log backups using a PowerShell script.
Using PowerShell to Pull SQL Server Services Configuration Information
Is there a way to pull information about the SQL Server services running on a server, without using SQL Server Configuration Manager? In this tip we look at how to use PowerShell to get this information.
Using PowerShell with SQL Server Management Objects (SMO)
I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.
Top 10
Iterating through SQL Servers and database objects with PowerShell
I've read several articles on MSSQLTips about executing queries against multiple servers or databases. I found advice about using the Local Servers Group to run a query against more than one SQL instance, about Central Management Server, about sp_MSforeachdb or about using the Management Studio query editor in SQLCMD mode. What about PowerShell? Can I use PowerShell to run queries against several instances, databases and database objects?
Using PowerShell for SQL Server Database Backups
I have seen some of your SQL Server Backup tips. I am starting to learn PowerShell and want to see how I can use it for common DBA tasks. How can I create a basic PowerShell script to backup all my user databases? Check out this tip to learn more.
Introduction to Windows PowerShell for the SQL Server DBA Part 1
I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowdays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.
PowerShell Support in SQL Server 2008 with the Invoke Sqlcmd Cmdlet
In my organization we write scripts to perform all sorts of administrative functions on our many SQL Server instances as well as other server products. We are just getting started with using PowerShell and have heard that SQL Server 2008 provides some built-in support for executing T-SQL commands from within PowerShell. Can you provide some details and examples?
Connect to SQL Server via Windows PowerShell with SQL Server authentication
In previous tips on Windows PowerShell with SQL Server, you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. Most of the examples have used Windows authentication to connect to SQL Server. How can I connect to SQL Server via Windows PowerShell using mixed mode authentication?
Using PowerShell for SQL Server Transaction Log Backups
How can I use PowerShell to do a Transaction Log backup for SQL Server databases? In this tip we look at an alternative way of creating transaction log backups using a PowerShell script.
Retrieve a List of SQL Server Databases and their Properties using PowerShell
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you have seen how you can use Windows PowerShell and SMO to administer SQL Server databases. In this tip I translate some of the Transact-SQL scripts that I use every day to PowerShell scripts, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.
Introducing PowerShell V2 Remoting for SQL Server
PowerShell can be used to for many SQL administrative tasks and in this tip we walk through how to run T-SQL code and admin tasks on remote servers.
Backup SQL Server Databases with a Windows PowerShell Script
In a previous tip on Backup and Restore SQL Server databases programmatically with SMO, you've seen how you can use Windows PowerShell to backup and restore SQL Server databases. In this tip, I will cover how to use Windows PowerShell to generate SQL Server backups.
Generating SQL Scripts using Windows PowerShell
In a previous tip on SQL Script Generation Programmatically with SMO, you have seen how you can use SMO to generate SQL scripts programmatically. In this tip I will cover how to generate scripts programmatically using Windows PowerShell.
Last 10
Iterating through SQL Servers and database objects with PowerShell
I've read several articles on MSSQLTips about executing queries against multiple servers or databases. I found advice about using the Local Servers Group to run a query against more than one SQL instance, about Central Management Server, about sp_MSforeachdb or about using the Management Studio query editor in SQLCMD mode. What about PowerShell? Can I use PowerShell to run queries against several instances, databases and database objects?
Enable PowerShell on Windows Server 2008 R2 Core Edition
As we all know, Microsoft has announced support to run SQL Server on Windows Server 2008 R2 Server Core Edition which is GUI-less OS. In this tip I will explain the step by step process to install and enable PowerShell on a Windows 2008 R2 Server Core. PowerShell is basic prerequisite to install SQL Server on Windows Server 2008 R2 Server Core Edition. Check out this tip to get started with the PowerShell installation.
PowerShell changes for database backups in SQL Server 2012
PowerShell has many features and one set of features is the ability to backup SQL Server databases. In this tip we look at PowerShell enhancements for SQL Server 2012 to backup SQL Server databases.
Enable Powershell Remoting on SQL Server Instances
Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? In this tip, I will describe a step by step process to enable PowerShell remoting on your SQL Server instances so that you can access all of your servers from your local machine via PowerShell.
Using PowerShell for SQL Server Transaction Log Backups
How can I use PowerShell to do a Transaction Log backup for SQL Server databases? In this tip we look at an alternative way of creating transaction log backups using a PowerShell script.
Using PowerShell for SQL Server Database Backups
I have seen some of your SQL Server Backup tips. I am starting to learn PowerShell and want to see how I can use it for common DBA tasks. How can I create a basic PowerShell script to backup all my user databases? Check out this tip to learn more.
Introducing PowerShell V2 Remoting for SQL Server
PowerShell can be used to for many SQL administrative tasks and in this tip we walk through how to run T-SQL code and admin tasks on remote servers.
Removing HTML tags from text data using PowerShell
In this tip I'll address the same problem as a previous tip using CLR, but to get the job done I'll use PowerShell. PowerShell will let me "clean" the string data and export the result to a .csv file in a single step. The tip I've mentioned before covers only the data "cleaning".
Using PowerShell to Pull SQL Server Services Configuration Information
Is there a way to pull information about the SQL Server services running on a server, without using SQL Server Configuration Manager? In this tip we look at how to use PowerShell to get this information.
Scheduling a SQL Server PowerShell script using the Task Scheduler
Even if your SQL Server edition does not include the SQL Server Agent service (if you use SQL Express, for instance), you can still automate administration tasks without using any 3rd party products. In this tip I'll describe how to schedule a PowerShell script with the Task Scheduler MMC snap-in. This applies to Windows 2008 Server, Windows 2008 Server R2 and Windows 7.
