Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Demo of the Power of Powershell

By Daniel Calbimonte,

Introduction

In the past, DBAs use to work with VB scripts, but now the PowerShell is reducing a lot all the administrative tasks with this flexible, versatile shell. In another article, Shawn Melton shows us the PowerShell basics. This article will show the power of PowerShell with a simple demo. This time, I will create a .txt file with a list of database names. The PowerShell script will create databases using that list.

Creating databases with PowerShell from a list

First we need a list of database names. To do this, we will use a txt file. Attached to this article, we have a database.txt file that contains a list of database names. The content of the database.txt is the following:

marketing

products

research

Now we are going to save the database.txt content in a PowerShell variable. The PowerShell variables can store numbers, text and also txt files.

For example, the variable $list can store the word hello world.

$list="hello world"

To verify the content we use the echo.

echo $li

In this case we want to save the database.txt content in the $list variable. That’s why PowerShell is so powerful. It can do things with few lines of code. 

$list=get-content c:\databases.txt 

To verify that the list contains the database.txt content use the echo command.

echo $list

Finally, to create databases we are going to call the Invoke-sqlcmd. The invoke-sqlcmd let us run T-SQL queries in PowerShell:

$list | ForEach-Object {Invoke-SqlCmd -Query "create database $_"}

The foreach let us create a database for each member of the $list.

To verify that everything is OK, open the SQL Server Management Studio and verify that the new databases were created:

Conclusion

This simple demo shows how easy it is to work with powershell. You can interact with text files, the active directoy, Exchange and more. The future of the Administrative tasks is Powershell.

 

Resources:

databases.txt
Total article views: 9714 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

ARTICLE

How to create Policies using PowerShell scripts

This article explains how to create policies with user defined conditions in MS SQL 2008 using Powe...

FORUM

creating SQL Server 2005 database from powershell function

Does user have the permissions to create the database

ARTICLE

Automating Daily Checks with Powershell

Using Powershell to create color coded backup reports for all servers in your environment.

ARTICLE

Change the Settings of a Database Object Using Powershell

This post tells you how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database object...

Tags
powershell    
 
Contribute