obj folders. It is very tedious doing it manually.
I turn to Powershell Script to find a quick way. Soon I got my solution, only two lines of codes and I could do in PS console interactively:
Here is the function:
$a = Get-ChildItem -Path H:MyRepository\Solution\ -Recurse | Where-Object {$_Name -Match '^obj$' }
$a | { rm -r $_.FullName }
Repeat the same steps to remove bin folders.
Showing posts with label PowerShell. Show all posts
Showing posts with label PowerShell. Show all posts
Wednesday, August 20, 2014
PS: Remove folder recursively
Monday, April 07, 2014
PS Function: Convert Collection of PSObjects
I find out a great way to convert collection of PSObjects with collections as its properties. It works very well to break collection of values as multiple lines in column so that the layout of table view is much tight and clean.
Here is the function:
#FileName: UtilCollection.ps1
#================================
#FUNCTION LISTINGS
#================================
Function ConvertPSObjectCollection {
<#
.SYNOPSIS
Gets a converted collection of PSObjects so that the converted
collection can be nicely displayed in table-view.
.DESCRIPTION
This function will convert a collection of PSObjects with
one or more array properties to a collection of PSObjects so
that the colleciton can be output to a nice and tight table view.
The input collection contains PSObjects with one or more array
property element. Normally array elements are too wide to be displayed
in a table-view. By converting collection, the returned colection
can be viewed nicely in table-view.
This function is based on SO solution http://stackoverflow.com/questions/22723954/powershell-autosize-and-specific-column-width
.PARAMETER CollectionPSObjects
Mandatory. Collection of PSObjects, PSObject have one or more
property of array data.
.INPUTS
Parameters above
.OUTPUTS
A collection of converted PSObjects with same prorperties
but array elements as rows in the collection so that the
collection can be nicely formated output to a table view.
.NOTES
Version: 1.0
Authors: David Chu
Creation Date: 10/04/2014
Purpose/Change: Initial function development
.EXAMPLE
$myCol = @(
(New-Object –TypeName PSObject –Prop @{'id'='01';'name'='a';'items'=@(1,2,3);'others'=@('SampleA1','SampleA2')}),
(New-Object –TypeName PSObject –Prop @{'id'=@('02a','02b');'name'='b';'items'=@(1,2,3);'others'=@('SampleB1','SampleB2','SampleB3','SampleB4','SampleB5')}),
(New-Object –TypeName PSObject –Prop @{'id'='03';'name'=@('c1','c2');'items'=@(1,2,3);'others'='SampleC'})
)
$myCol1 = ConvertPSObjectCollection $myCol
$myCol1 | FT ID,Name,Items,Others -AutoSize
#>
[CmdletBinding()]
Param (
[Parameter(Mandatory=$true)] $CollectionPSObjects
)
Process {
$m_result = $CollectionPSObjects | %{
$Current = $_
$Members = $_|GM|?{$_.MemberType -match "Property"}| `
Select -ExpandProperty Name
$Rows = ($Members|%{$current.$_.count}| `
sort -Descending|Select -First 1)-1
For($i=0; $i -le $Rows;$i++){
$LoopObject = New-Object PSObject -Property `
@{$($Members[0]) = if($Current.$($Members[0]).count -gt 1) { `
$Current.$($Members[0])[$i] `
} else{ `
if(!($i -gt 0)){ `
$Current.$($Members[0]) `
}else{ `
$Null `
} `
} `
}
If($Members.Count -gt 1){
$Members[1..$Members.count]|%{
Add-Member -InputObject $LoopObject `
-MemberType NoteProperty `
-Name $_ `
-Value $(if($Current.$_.count -gt 1) { `
$Current.$_[$i] `
}else { `
if(!($i -gt 0)) { `
$Current.$_ `
}else{ `
$Null `
} `
} `
)
}
}
$LoopObject
}
}
return $m_result
}
}
References
- SO Question: Autosize and Specific Column Width
Wednesday, April 02, 2014
Three Shortcuts of PowerShell Script
Still I don't know some very basic concepts. Here are three most commonalty used code cases:
%{....}
@{....}
?{....}
I just use them based on other people's codes. For these three shortcuts, I have trouble to google explanations. Finally, last week, I got an answer from SO.
%{...}: ForEach{}
@{...}: Constructor for hash table
?{...}: shortcut for Where {....}
References
- SO comment discussion on Francis Padron's quetsion.
Tuesday, December 18, 2012
Get Folder Permissions by Scripts (3)
The help function is a very simple one which prints out the usage of the script. The same practice can be applied to other scripts.
# FUNCTION LISTINGS
# =============================================================================
# Function: HelpInfo
# Created: [11/25/2009]
# Author: David Chu
# Arguments:
# $p_scriptApp: script application
# -------------------------------------------
# Purpose: display usage message
# -------------------------------------------
function HelpInfo($p_scriptApp)
{
Write-Output ""
Write-Output "Description: Get permissions for a folder."
Write-Output "Parameters: folderPath [outFile] [/s]"
Write-Output "Where"
Write-Output " folderPath: a path for a folder"
Write-Output " outFile: output file"
Write-Output " /s: recursive to get sub folders"
Write-Output "Examples:"
Write-Output "This one running from PowerShell:"
Write-Output (" PS: {0} C:\MyData" -f $p_scriptApp)
Write-Output "The following example running from cmd console:"
Write-Output (" cmd> PowerShell {0} C:\MyData myFile /s" -f $p_scriptApp)
}PS entry section is the place to get all the parameters from command line.
# Example to run this script:
# @powershell d:\Scripts\GetPerm.ps1 'D:\Displays\Public' /s
# Result:
D:\Displays\Users Everyone:(OI)(CI)F
BUILTIN\Administrators:(OI)(CI)F
NT AUTHORITY\SYSTEM:(OI)(CI)F
CREATOR OWNER:(OI)(CI)(IO)F
BUILTIN\Users:(OI)(CI)R
BUILTIN\Users:(CI)(special access:)
FILE_APPEND_DATA
BUILTIN\Users:(CI)(special access:)
FILE_WRITE_DATA
#*=============================================================================
#* SCRIPT BODY
#*=============================================================================
# example parameter values for this script. Add the parameter to PowerGUI Script
# Editor's Input toolbar:
# C:\Users /s
$scriptApp = "GetPerm.ps1"
# check input arguments
if ( $args.length -eq 0 -or $args.length -gt 3 )
{
HelpInfo $scriptApp
return
}
# Get the first input parameter
$i = 0
$folderPath = $args[$i++
$outFile = "outFile.txt"]
$recursive = $false
if ($args.length -gt 1)
{
if ( $args[$i++].ToLower() -eq "/s")
{
$recursive = $true
if ($args.length -gt 2)
{
$outFile = $args[$i++]
}
}
else
{
$outFile = $args[$i++]
if ($args[$i++] -eq "/s")
{
$recursive = $true
}
}
}
# check if source and dest pathes exist
if ( Test-Path -Path $folderPath | where {!$_PSIsContainer} )
{
GetFolderPermission $folderPath $recursive $outFile
} else {
Write-Output ("Invalid path: {0}" -f $folderPath)
}
#*=============================================================================
#* END OF SCRIPT BODY
#*=============================================================================
In PS, command line argument can be obtained from $args[] collection, and the number of arguments is the property of length of $args. Each argument can be enclosed by single quote ' char, not double " char.
Tuesday, December 11, 2012
Get Folder Permissions by Scripts (2)
To get all the containers as a collection in PS, the command is Get-Item with option $_.PSIsContainer:
$fs = Get-Item -Path $p_Path | Where-Object {$_.PSIsContainer }
The following is the function
# Function: GetFolderPermission
# Created: [10/02/2012]
# Author: David Chu
# Arguments:
# $p_Path: folder
# $p_recursive: true for recursive, false for not
# -------------------------------------------
# Purpose: get folder permissions
# -------------------------------------------
function GetFolderPermission(
$p_Path,
$p_outFile,
$p_recursive)
{
# get all the files matched and timestamp > comparing date
$fs = Get-Item -Path $p_Path | Where-Object {$_.PSIsContainer }
if ( $fs -ne $null )
{
foreach ($folder in $fs)
{
$fullDir = $folder.FullName
Write-Output $fullDir
$cmd = 'cacls "{0}"' -f $fullDir
Invoke-Expression $cmd | out-File -Append $p_outFile
if ( $p_recursive ) { $fsSub = Get-ChildItem -Path $fullDir |Where-Object {$_.PSIsContainer}
if ( $fsSub -ne $null)
{
foreach ($subDir in $fsSub)
{
$fullDir = $subDir.FullName
GetFolderPermission $fullDir $p_recursive $p_outFile
}
}
}
}
}
}The above codes are very straightforward, therefore, no need to explain.
Tuesday, December 04, 2012
Get Folder Permissions by Scripts (1)
Here is a example of using the script in a batch script:
C:\test\powershell c:\scripts\GetPerm.ps1'C:\MyData' /sThe result is something like this:
D:\MyData Everyone:(OI)(CI)F
BUILTIN\Administrators:(OI)(CI)F
NT AUTHORITY\SYSTEM:(OI)(CI)F
CREATOR OWNER:(OI)(CI)(IO)F
BUILTIN\Users:(OI)(CI)R
BUILTIN\Users:(CI)(special access:)
FILE_APPEND_DATA
BUILTIN\Users:(CI)(special access:)
FILE_WRITE_DATAIf there are sub-folders within, the script will loop through to get all sub-fodler permissions as well.
It is possible to use WMI class or other tools in PS to get folder permissions. Calcs is a free tool in Windows. I found it is much easy to leverage its power to do the job.
Friday, January 21, 2011
Parse and Transform Text File by Using PowerShell (3)
Generate the Second Report
The second report is another summary report, based on the first report. It displays a list of methods and counts of their calls. In other words, it will list distinct method names in the first report, and take the max counter for each method as method call count.
I find out that to get the second report, it is not easy to use just one pipeline with a long chain of segment codes. Still, I'll continue to use pipelines with each one to generate temporary results. I'll use pipelines to get temporary results, and dynamically add properties to objects.
First, initialize some variables:
# second report
# initialize variables
$totalCount = 0
$i = 0
$ht=New-Object Collections.Hashtable
The first pipeline is used to pass the result of the above collection of objects ($results) as input. There will be no result out of the pipeline. Instead, I use the pipeline to update a hash table variable $ht, with the property of method name as its key.
The input objects are piped into the second segment which is a where clause to filter any object with its count property larger than zero.
# Filter out rows with count less or equals 0
$result | where { $_.$propertyNameForCount -gt 0 } `
Then the filtered object is passed to a block of codes, where the hash table variable $ht is updated with the object with the max count value:
| Select-Object -Property 'MethodName', $propertyNameForCount `
| %{
if ( $_ -ne $null ) {
$key1 = $_.'MethodName';
if ( $ht.ContainsKey($key1) -eq $false ) {
$ht.Add($key1, $_)
}
elseif ($_.$propertyNameForCount -gt $ht[$key1].$propertyNameForCount) {
$ht.Set_Item($key1, $_)
}
}
}
The values of the hash table $ht contain objects we need for the report. In addition to those objects, I need a total count of each method call count. This is done by a pipeline to update total count into the variable $totalCount:
$ht.Values `
| %{ `
if ( $_ -ne $null ) {
$totalCount += $_.$propertyNameForCount
}
}
After we get the total count, a new object is created in the same structure of properties as ones in the hash table, then add the new object to the hash table $ht:
# add total count to $ht1 table
$objValue = New-Object PSCustomObject
$objValue | Add-Member -type NoteProperty -Name 'MethodName' -Value '[Total count]'; # use XXX so that sorting to the last
$objValue | Add-Member -type NoteProperty -Name $propertyNameForCount -Value $totalCount;
$ht.Add("XXXX dummy key", $objValue);
Finally, the objects in the hash table are ready for the second report. The report is generated by the last pipeline: sorting by property 'MethodName', adding a sequence number as object property, and appending the table layout report to the output file:
# generate report
$ht.Values `
| Sort-Object -Property 'MethodName' `
| %{ # Add sequence column
$obj = $_;
$obj | Add-Member -type NoteProperty -Name 'No.' -Value $i;
$i++;
$obj;
} `
| ft -AutoSize -Property 'No.', 'MethodName', $propertyNameForCount >> $outputFile; # Format the result and out put to file
In summary, pipeline in PS is a nice-to-have feature. You may achieve the same result without using pipeline. I like PS pipeline's simple, fluent flow and powerful feature. As you can see, PS is also dynamic data type script language. Objects can be created on-fly and properties can be added or removed during the run-time. My parse script codes take the advantage of those two great features.
Here is the complete package(ParseDebugMsgs.zip) of the script codes.
Friday, January 07, 2011
Parse and Transform Text File by Using PowerShell (2)
Define Input Variables
I could set some input parameters for my script module for easy use from command line. However, since my script is only for my own use and I have to update some values frequently when I use it, then I decided to just declare some variables with initial settings as a simple start:
$inputFile = 'C:\Tmp\test\fst_PO20100505.txt';
$outputFile = '{0}.txt' -f $inputFile;
[decimal]$durationLimit = -0.01;
Then some counter variables and a hash table variable $ht for later use are initialized:
$i = 0
$j = 1
$identityExPattern = "*duration: *" # expression pattern as a filter
$exPatternForCount = "*DAO::*" # epxression pattern for count
$identityPropertyName = "Duration"
$propertyNameForCount = "DAO count"
$ht = New-Object Collections.Hashtable;
Next I output a line a header to my output file:
# Output duration limit to the result file
'==== Result of "Duration > {0}" ====' -f $durationLimit >> $outputFile;
Those codes are straightforward. The duration limit is a filter, which is used to list only calls with duration larger than the filter value.
Generate the First Report
The first report is generated by one statement with a long list of chained segments of codes as a pipeline. The result is saved to variable $result.
The first segment in the chain is to get lines from input file:
$result = Get-Content $inputFile `
Then the second segment is a block of codes %{...}. This block takes the input to process and generate empty or a collection of objects as a result. The result can be piped to the next segment. The codes in the block is very simple, it updates the line number in a varable $i, and then takes the input object as it is. $_ is a special variable notation for the input object:
| %{ $i = $i + 1;
$_;
} `
Then each line is piped to the next where constrain statement:
| where {$_.trim().length -gt 0 -and ($_.trim().SubString(0,1) -eq "[") -and ($_.trim() -like $identityExPattern)} `This constrain clause is like a filter to remove un-expected string lines. The result of this filter will be a line which is not empty, the first none-empty char is "[" and the content of the string contains a substring of "duration: ". The interested lines are then piped into the next code block %{...}.
This block contains a lot of codes. They can be divided in to two parts. The first part is to split a line into an array variable $row, and to acuminate count of each method:
$row = (-split $_ ); # split a line into array by space
$bCount = $false;
$method = $row[7]; # example: [ 5/5/2010 9:55:03 AM duration: 0.19 ] AppUserDAO::loginUser
if ( $method -like $exPatternForCount )
{
$c = 1;
# update method in hash table with count value
if ( $ht.ContainsKey($method) )
{
$c = $ht.Get_Item($method) + 1;
$ht.Set_Item($method, $c);
}
else
{
$ht.Add($method, $c);
}
$bCount = $true;
}
The second part is to create an object based on the result of the first part: $row. The object is created by using "Select ... -InputObject ... -Property" statement. The -InputObject take the array of $row as input, and -Property defines a list of properties:
# create an object with properties: sequence, datetime, duration, DAO count, and methodName
$obj = select-object -input $row -prop `
@{Name='No.'; expression={$i;}}, `
@{Name='DateTime'; expression={[DateTime]($row[1] + ' ' + $row[2] + ' ' + $row[3]);};} , `
@{Name=$identityPropertyName; expression={([decimal]$row[5]);} }, `
@{Name=$propertyNameForCount; expression={ `
if ($bCount) { `
$ht.Get_Item($method); `
} `
else { `
0; `
} `
}
}, `
@{Name='MethodName'; expression={($method);} };
$obj; #output object
The last line of $obj will pass the object to the next segment of the pipeline.
The segment is a simple where clause, which filters out any duration smaller than the expected value:
| where { $_.Duration -gt $durationLimit } `The final pipe segment is to create another object based on input object. The purpose of this new object is for the first report, each property for a column in the report:
%{
$obj1 = Select-Object -Input $_ -Property `
@{Name='No.'; expression={$j;}}, `
@{Name='Org No.'; expression={$_.'No.';}}, `
@{Name='DateTime'; expression={$_.'DateTime';};} , `
@{Name=$identityPropertyName; expression={$_.$identityPropertyName;} }, `
@{Name=$propertyNameForCount; expression={$_.=$propertyNameForCount;} }, `
@{Name='MethodName'; expression={$_.'MethodName';} };
$j++;
$obj1; # output obj1
};The result of the above chained segments is a collection of objects for the first report. The result is assigned to variable $result, and then is output to a file:
# get the reults to output file as formatted table
$result | ft -AutoSize >> $outputFile;
Saturday, January 01, 2011
Parse and Transform Text File by Using PowerShell (1)
What I would like is to generate concise summary reports, for example, a list of method calls with durations in an order and a list of DAO calls with their counts. This is similar to the case to use XPath to parse and to transform an XML content to another format, such as a HTML table list.
PS came to my mind first. PS is a script based language; therefore, it is easy to give it a try. I am not an expert in PS. I just use it and learn it as I need. I spent some time to write codes and finally I completed a script module to get my expected result. Here is my review of the codes.
Basic Concepts
Before I jump deep into my PS codes, I would like to list brief explanations for some basic concepts.
Single value variables are dynamically declared in PS with prefix $. The data type can also be static in the format of [type]var.
Hashtable is a dictionary data type with a key and an associated value. The constant definition is @{[key1=value1,...]} or @{}.
# is used for comments.
Statements can be either separated by line break or terminated by ';' character. ` character is used as a continuing indicator.
Piping or pipeline is a very powerful feature in PS. By using |, twp segments of codes can be chained together, the output or results of the first segment being piped into the next segment of codes as an input. Not only strings can be piped, but objects can also be passed through the pipeline. You can write similar codes without pipelines, but by using it appropriately, your scripts may look much simple and easy to read, and you may like this unique and powerful feature of PS.
There are many great resources on web. For example, the first part of this blog tutorial on PS variables, arrays, and hashtables provides nice hands-on examples on PS basics.
The Goal of my Project
I call it as a project because I want to write script codes to reach my goal. Basically, I copy my debug messages from VS output console and save them to a text file. The goal of the project is to read the text file as input, parse each line and generate a list of reports, actually two reports in this project.
The first report is a table view of methods and their corresponding duration time. The second report is a table view of interested methods and their call counts.
Here is a partial section of the raw data:
[ 5/5/2010 3:12:58 PM ] MainSchedulingTool::posMenuItem_Click
[ 5/5/2010 3:12:58 PM ] POSelectionViewForm::POSelectionViewForm
[ 5/5/2010 3:12:58 PM ] POSelectionViewForm::poStartDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM duration: 0.00 ] POSelectionViewForm::poStartDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM ] POSelectionViewForm::poEndDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM duration: 0.00 ] POSelectionViewForm::poEndDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM ] PODAO::GetAllPOsByStatusAndOrderByEndDate
[ 5/5/2010 3:12:58 PM ] PODAO::GetPOs
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
...
Here an example of the first report:
No. Org No. DateTime Duration DAO count MethodName
--- ------- -------- -------- --------- ----------
1 161 5/5/2010 3:12:59 PM 0.38 1 PODAO::GetPOs
2 162 5/5/2010 3:12:59 PM 0.38 1 PODAO::GetAllPOsByStatusAndOrderByEndDate
3 164 5/5/2010 3:12:59 PM 0.41 0 POSelectionViewForm::POSelection...
4 807 5/5/2010 3:13:00 PM 1.56 1 VendorDAO::GetVendorData
5 808 5/5/2010 3:13:00 PM 1.56 1 VendorDAO::GetVendors
6 1450 5/5/2010 3:13:02 PM 1.83 2 VendorDAO::GetVendorData
7 1451 5/5/2010 3:13:02 PM 1.84 2 VendorDAO::GetVendors
8 1452 5/5/2010 3:13:02 PM 1.84 0 POSelectionViewForm::setupSortedPOList
9 2575 5/5/2010 3:13:13 PM 14.13 0 POSelectionViewForm::PopulatePOData...
10 2576 5/5/2010 3:13:13 PM 14.13 0 POSelectionViewForm::POSelection...
11 2729 5/5/2010 3:13:14 PM 0.16 0 MainSchedulingTool::setupC......
The first column is a sequence number. It is a sequence line number in the report. The second is similar to the line number in the raw text file. The remaining columns are the information about each method such date time, duration value, count of DAO method calls, and method names.
The following is an example of the second report:
No. MethodName DAO count
--- ---------- ---------
0 [Total count] 6
1 PODAO::GetAllPOsByStatusAndOrderByEndDate 1
2 PODAO::GetPOs 1
3 VendorDAO::GetVendorData 2
4 VendorDAO::GetVendors 2
...
Saturday, December 04, 2010
PowerShell Tip: Dynamic Data Type
One of PS great features is its dynamic data type. All the variables are defined by $ prefix. You can define a variable with strong data type. However, sometimes you just need to take the advantage of its dynamic data type. For example, the following code is to get files. The result may be null, one file or a collection of files:
$fs = Get-Item -Path "*.txt"
In order to find out if the result contain any file, you have to check the cases of empty, one object or a collection of objects.
$fs = Get-Item -Path "*.txt"
if ($fs -eq $null) {
Echo "empty files"
}
if ($fs.Count -eq $null) {
Echo ("one file: {0}" -f $fs)
}
else {
Echo ("collection of files. Count: {0}" -f $fs.Count)
$fs
}
Saturday, November 20, 2010
Powershell Scripts and Batch File
First to execute a PS script in batch file, run it from PS with script name and additional arguments:
Powershell myscript.ps1 argument1, 'argument two'
Notice that if there is a space in an argument, use single quote instead of double quotes.
Second tip is about calling a batch file from PS script, use the
cmd /c to execute a batch file. For example, the following case is to use PS script to format the current date time asa string and then run a bat with the string as its argument:# get the current date time
$date = Get-Date
# format the date time as a string
$argDateTime = "{0:d4}{1:d2}{2:d2}_{3}{4}{5}" `
-f $date.Year, $date.Month, $date.Day, $date.Hour, $date.Minute, $date.Second
# build a command line: $args[0] is a bat file
$cmdApp = ("{0} {1}" -f $args[0], $argDateTime)
# run the bat with a formatted date time string as argument
cmd /c $cmdApp
Monday, September 13, 2010
Backup SQL Server by Using PowerShell Scripts
The basic requirements for the database backup job are:
- The job can be scheduled as an automation job without user interaction
- The job is preferred in script for each maintenance, for example, database, user/pwd, and backup location changes.
- The backup job is centralized on one place so that several SQL databases are backed up on one central location.
SQL Server Utility
The first tool I found is to use SQL management tool SQLMaint.exe. It comes with SQL Server Management Studio 2005. Here is the technical information of this tool. For example, I use the following batch commands to do a database backup for MyDatabase on SQL server PC001\sqlexpress:
@echo off
REM
REM This path is SQL binary folder for sql maintanance app
REM
pushd "C:\MSSQL2005\MSSQL.2\MSSQL\Binn"
sqlmaint.exe -S PC001\sqlexpress -U dbbackup -P pwd
-D myDatabase -CkDB -BkUpOnlyIfClean
-Rpt C:\MSSQL_Backup\Log\PC001MyDatabase_backup_log.txt
-VrfyBackup -BkUpMedia DISK
-BkUpDB C:\MSSQL_Backup\DB
-DelBkUps 3days -DelTxtRpt 3weeks
-HtmlRpt C:\MSSQL_Backup\HTMLRpt\PC001MyDatabase_backup_report.html
-DelHtmlRpt -3weeks
popd
@echo on
The batch commands work fine with only SQL Server 2005 database; however, it does not work for SQL Server 2008 or Expression databases.
PowerShell Solution One
By googling web, quickly I found an alternative way to do the job. Those scripts are based on Microsoft.SqlServer.xxx classes. That's very cool! Based on those scripts and my requirement, I created a function. This function does database backup by either Windows log-on user credential, or SQL Server user credential. In either case, the credential user should be configured in the SQL server with db_backupoperator permission.
The function takes following parameters:
- SQL Server Name, for example, PC001\SQLEXPRESS
- database name, for example, myDatabase
- folder: a path on SQL server where the backup file will be saved
- SQL user name. This is optional. If it is not supplied, the current Windows log-on user's credential will be used
- password for the above SQL server user. Optional
Here is the script:
function BackupSQLDb (
[string]$p_sqlServerName = ${throw "Missing sql server name "},
[string]$p_db = ${throw "Missing parameter database name"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
[string]$p_userName,
[string]$p_password
)
{
#load assemblies
#note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
#otherwise may get this error
#Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
#the assembly containing this type is loaded.
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$sqlServername = $p_sqlServerName
$sqlUserName = $p_userName
$sqlPWD = $p_password
#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")
-ArgumentList $sqlServername # "PC001\sqlexpress"
$backupDirectory = $p_DestFolder
#display default backup directory
Write-Debug ("Default Backup Directory: {0}" -f $backupDirectory)
if ( $sqlUserName -ne $null -and $sqlUserName.length -gt 0 ) {
$server.ConnectionContext.LoginSecure=$false;
$server.ConnectionContext.set_Login($sqlUserName)
$securePassword = ConvertTo-SecureString $sqlPWD -AsPlainText -Force
$server.ConnectionContext.set_SecurePassword($securePassword)
}
$db = $server.Databases[$p_db]
$dbName = $db.Name
if ( $dbName.length -gt 0 )
{
$timestamp = Get-Date -format yyyyMMdd_HHmmss
$backupFile = $backupDirectory + $dbName + "_" + $timestamp + ".bak"
Write-Output ("Start backup database ""{0}"" to ""{1}"""
-f $dbName, $backupFile)
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
#BackupActionType specifies the type of backup.
#Options are Database, Files, Log
#This belongs in Microsoft.SqlServer.SmoExtended assembly
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($backupFile, "File")
$smoBackup.SqlBackup($server)
Write-Output ("Finished backup database ""{0}"" to ""{1}"""
-f $dbName, $backupFile)
}
else {
Write-Output ("ERROR: invalid database name or database does not exist: {0}"
-f $p_db)
}
}
I only tested this function on Windows XP and Windows 2008 Server with SQL Server 2008 Express installed.
PowerShell Solution Two
The above script function works fine with SQL Server 2005 and 2008 and Express versions. However, it does not work for SQL Server 2000! I realized one day that there is option to obtain the backup TSQL scripts from SQL Server Management studio. I verified that in the TSQL command stays same in all SQL Server versions: 2000, 2005 and 2008. How about to make a connection to SQL server and run the TSQL command? Quickly I come to the solution two.
function BackupSQLDb (
[string]$p_sqlServerName = ${throw "Missing sql server name "},
[string]$p_db = ${throw "Missing parameter database name"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
[string]$p_userName,
[string]$p_password
)
{
$timestamp = Get-Date -format yyyyMMdd_HHmmss
$backupFile = $p_DestFolder + $p_db + "_" + $timestamp + ".bak"
$backupDescription = "Full backup of {0}" -f $p_db
Write-Output ("Start backup database ""{0}"" on SQL Server({2}) to ""{1}"""
-f $p_db, $backupFile, $p_sqlServerName)
# TSQL command for backup
$tsqlCmd = "BACKUP DATABASE {2} TO DISK = N'{0}' WITH NOFORMAT, NOINIT, NAME = N'{1}', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
-f $backupFile, $backupDescription, $p_db
$con = $null
if ( $p_userName -ne $null -and $p_userName.length -gt 0 ) {
# Use SQL user/password
$con = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}"
-f $p_sqlServerName, $p_db, $p_userName, $p_password
}
else {
# Use Windows log on credential
$con = "Data Source={0};Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1}"
-f $p_sqlServerName, $p_db
}
Write-Output ("Connecting to {0} ..." -f $con)
$cn = new-object System.Data.SqlClient.SqlConnection ($con)
$cn.Open()
$cmd2 = new-object "System.Data.SqlClient.SqlCommand" ($tsqlCmd, $cn)
$result = $cmd2.ExecuteNonQuery()
$cn.Close()
Write-Output ("Backup database is done with result {0}" -f $result)
}
I did similar tests for this function. It seems that it works for all SQL Servers, 2000, 2005, and 2008.
References
SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell
SQL Server PowerShell : Basics – Connecting to SQL Server
JBs Powershell blog: SQL Queries.
Saturday, August 21, 2010
App Installation as Admin in Windows Server 2008
The issue I encountered was that the UDL is a connection to an Oracle database, and it seems that the UDL string cannot be accepted by the application. To cut the story short, Captone's tech support guru finally identified that the issue was caused by Oracle drive installed on this Windows box. The network team at work installed 64-bit oracle driver (Oracle g10), while the application requires the 32-bit driver. Unfortunately, the person who is responsible for the installation is on vacation this week. Therefore, Capstone person helped me to install the Oracle g11 client, 64-version first and then 32-version next.
The 64-version installation went OK. However, we had trouble to install the 32-version. There were several failures during the preparing stage. He finally figured out that he has to run the installation oui as administrator. I saw it so simple to run installer as administrator. Since my log-in name is a member of Administrators group, in Windows Server 2008 (VISA-like Windows), just right click on oui and run it as administrator:
to my surprise, no password was requested. I think it might be Windows Server 2008 and my log-in is in Administrators. Anyway, that reminded me a case I did before: to configure PowerShell Execution settings.
By default, the execution policy for PS is Restricted. I needed to change it to RemoteSigned. However, I could not do it even my log-in name is in Administrators. It seems Windows Server 2008 has strong security. What I did was to make a request to the network team to log in as administrator to run Set-ExecutionPolicy command with RemoteSigned.
When I realized that it was so simple to run installation as admin, I tried PS right afterward. 
Now, after I run the PS as admin and I can set policy. That's pretty cool!
Sunday, December 06, 2009
SysInternals Tool: PsExec.exe
Quickly I found a solution: a SysInternals tool, PsExec.exe. It is very small and it works well. To start a process and wait it terminated, there is the code:
PsExec.exe \\computerName -u userName -p pwd -i program args...
the option
-i is used to start the program in an interactive way.
Tuesday, December 01, 2009
Zip Files with PowerShell Script
This job can be easily done in a .Net project, but I was required to write a script instead of another program. As I know very little about PowerShell, I spent about 2-3 days to find out a solution. Basically, you can access to almost any .Net classes in PS. I have used DotNetZip library before, which provides a very simple and nice library class to zip files. What I need to access to this library, create an instance from its class and call its methods to detect and zip files. In PS, it is very easy to do that.
# ZIP dll library file in the local PC folder:
$ZIP_DLL = "C:\bin\Ionic.Zip\Ionic.Zip.dll"
$assemblyLoaded = [System.Reflection.Assembly]::LoadFrom($ZIP_DLL);
# Zip class
$zipClass = "Ionic.Zip.ZipFile";
$ZIP_DLL = "C:\bin\Ionic.Zip\Ionic.Zip.dll"
$assemblyLoaded = [System.Reflection.Assembly]::LoadFrom($ZIP_DLL);
# Zip class
$zipClass = "Ionic.Zip.ZipFile";
Here I use a var to hold
LoadFrom(...) is to prevent output of loading results. The var is not needed for reference use. In PS, if you want to prevent some output while calling some methods, this may be a strategy to do it.To zip files, I created a function to do the job. The function will zip a group of files (source files as a string such as "C:\temp\*.log"), with a constrain of days for last modified date stamp within those days from now, to a destination folder. In addition to that, I pass one flag to the function to provide option to include path in zip or not.
#*============================================
# FUNCTION DEFINITIONS
#*============================================
function ZipUpFiles (
[string]$p_Source = ${throw "Missing parameter source"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
[int]$p_days = ${throw "Missing parameter int days"},
$p_zipFile,
[bool]$p_PathInZip,
$p_zipClass
)
{
...
}
...
#*============================================
# END OF FUNCTION DEFINITIONS
#*============================================
# FUNCTION DEFINITIONS
#*============================================
function ZipUpFiles (
[string]$p_Source = ${throw "Missing parameter source"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
[int]$p_days = ${throw "Missing parameter int days"},
$p_zipFile,
[bool]$p_PathInZip,
$p_zipClass
)
{
...
}
...
#*============================================
# END OF FUNCTION DEFINITIONS
#*============================================
In PS, actually, you don't need to define input parameters. You can define () empty list, and you can still call it with a list of parameters. Within the function, you can get parameters by $args. However, it is much clear by defining parameters. You can think them as var definitions.
The first thing to do in the function is to get a list of files:
$checkFileDate = ($p_days -ne 0)
# adjust timestamp by days for comparing
$dateToCompare = (Get-date).AddDays(-$p_days)
$zipCount = 0;
# get all the files matched and timestamp > comparing date
$fs = Get-Item -Path $p_source | Where-Object {!$_.PSIsContainer -and (!$checkFileDate -or ($checkFileDate -and $_.lastwritetime -gt $dateToCompare))}
if ( $fs -ne $null )
{
...
# adjust timestamp by days for comparing
$dateToCompare = (Get-date).AddDays(-$p_days)
$zipCount = 0;
# get all the files matched and timestamp > comparing date
$fs = Get-Item -Path $p_source | Where-Object {!$_.PSIsContainer -and (!$checkFileDate -or ($checkFileDate -and $_.lastwritetime -gt $dateToCompare))}
if ( $fs -ne $null )
{
...
The codes are pretty much straightforward. Here
Get-Item command to check path with pipe to check each items to meet requirements: not sub-direction, and file created date great than days if specified. The result is a collection of files to be zipped.In PS, all the comparison and logical operators are literal with -. For example,
-gt for great than, -eq for equal to, and -or. This very handy and easy to understand. It also makes the blog HTML tags much easier, no need to convert "<" to "<".Next continue to zip files in a for loop. The function takes one parameter as zip file name. If it is specified, all the files will be zipped to that file with {mmyyyy}.zip as suffix. If it is not specified, each file will be zipped with that suffix.
$zipObj = $null
if ( $p_zipFile -ne $null )
{
$zipFile = "{0}{1}" -f $p_DestFolder, $p_zipFile
$zipObj = new-object $p_zipClass($zipFile);
}
foreach ($file in $fs)
{
$addFile = $file.Name
if ( $p_zipFile -eq $null )
{
$zipFile = "{0}{1}.zip" -f $p_DestFolder, $addFile
$zipObj = new-object $p_zipClass($zipFile);
}
# Trim drive name out as key to check if file already in zip?
if ( ($zipObj.Count -eq 0) -or
(!$p_PathInZip -and ($zipObj[$file.Name] -eq $null)) -or
($p_PathInZip -and ($zipObj[$file.FullName.Substring(3)] -eq $null))
)
{
Write-Output "Zipping file $addFile to $zipFile..."
$pathInZip = ""
if ( $p_PathInZip )
{
$pathInZip = $file.Directory
}
$e= $zipObj.AddFile($file.FullName, $pathInZip)
$zipCount += 1
}
if ( $p_zipFile -eq $null -and $zipCount -gt 0 )
{
$zipObj.Save()
$zipObj.Dispose()
$zipObj = $null
$zipFile = $null
}
}
if ( $zipObj -ne $null -and $zipCount -gt 0 )
{
$zipObj.Save()
$zipObj.Dispose()
$zipObj = $null
}
if ( $p_zipFile -ne $null )
{
$zipFile = "{0}{1}" -f $p_DestFolder, $p_zipFile
$zipObj = new-object $p_zipClass($zipFile);
}
foreach ($file in $fs)
{
$addFile = $file.Name
if ( $p_zipFile -eq $null )
{
$zipFile = "{0}{1}.zip" -f $p_DestFolder, $addFile
$zipObj = new-object $p_zipClass($zipFile);
}
# Trim drive name out as key to check if file already in zip?
if ( ($zipObj.Count -eq 0) -or
(!$p_PathInZip -and ($zipObj[$file.Name] -eq $null)) -or
($p_PathInZip -and ($zipObj[$file.FullName.Substring(3)] -eq $null))
)
{
Write-Output "Zipping file $addFile to $zipFile..."
$pathInZip = ""
if ( $p_PathInZip )
{
$pathInZip = $file.Directory
}
$e= $zipObj.AddFile($file.FullName, $pathInZip)
$zipCount += 1
}
if ( $p_zipFile -eq $null -and $zipCount -gt 0 )
{
$zipObj.Save()
$zipObj.Dispose()
$zipObj = $null
$zipFile = $null
}
}
if ( $zipObj -ne $null -and $zipCount -gt 0 )
{
$zipObj.Save()
$zipObj.Dispose()
$zipObj = $null
}
Here
$zipObj is created from .Net class. All the methods then are available in PS. You may refer to class definition in Visual Studio or ReFlector to view class structure. Before I add a file to zip, I check if the file is already in the zip file (two cases: path in zip or not). If so, no zip will be done.In the end of the function, the
$zipObj has to be saved and cleared if there is any files added:...
}
if ( $zipObj -ne $null -and $zipCount -gt 0 )
{
$zipObj.Save()
$zipObj.Dispose()
$zipObj = $null
}
}
if ( $zipcount -eq 0 )
{
Write-Output "Nothing to zip"
}
}
}
if ( $zipObj -ne $null -and $zipCount -gt 0 )
{
$zipObj.Save()
$zipObj.Dispose()
$zipObj = $null
}
}
if ( $zipcount -eq 0 )
{
Write-Output "Nothing to zip"
}
}
Finally, in my PS script, after the function definition, which has to be declared before it is called, here is my main entrance:
#*============================================
#* SCRIPT BODY
#*============================================
# Example parameters:
# E:\Temp\*.bak E:\Temp\BackupZips\ 50 backup.zip
Write-Debug "Starting ZipFiles.ps1"
# check input arguments
$argsLen = 0
if ($args -ne $null )
{
$argsLen = $args.length
}
if ( $argsLen -lt 2 -or $argsLen -gt 5 )
{
HelpInfo
return
}
$i = 0;
# Get input parameters
$sourcePath = $args[$i++]
$destPath = $args[$i++]
if ( !$destPath.EndsWith("\") )
{
$destPath += "\"
}
[int]$numOfDays = 0
$zipFile = $null
[bool]$pathInZip = $true
if ( $argsLen -gt $i )
{
$r = [int]::TryParse($args[$i++], [ref]$numOfDays)
if ( $argsLen -gt $i )
{
$zipFile = $args[$i++]
if ( $zipFile -eq $null -or $zipFile.length -eq 0 )
{
$zipFile = $null
}
if ( $argsLen -gt $i )
{
$pathInZip = ($args[$i++] -eq 1)
}
}
}
# Test source & destiantion
if ( !(Test-Path $sourcePath) -or !(Test-Path $destPath) )
{
Write-Output "Nothing to do. Either ""$sourcePath"" or ""$destPath"" is empty or does not exist."
return
}
# ZIP library is from http://www.codeplex.com/DotNetZip
# ZIP dll library file in the local PC folder:
$ZIP_DLL = "C:\bin\Ionic.Zip\Ionic.Zip.dll"
$assemblyLoaded = [System.Reflection.Assembly]::LoadFrom($ZIP_DLL);
# Zip class
$zipClass = "Ionic.Zip.ZipFile";
Write-Debug "Start zip process ($sourcePath > $destPath)..."
ZipUpFiles $sourcePath $destPath $numOfDays $zipFile $pathInZip $zipClass
$assemblyLoaded = $null
#*============================================
#* END OF SCRIPT BODY
#*============================================
#* SCRIPT BODY
#*============================================
# Example parameters:
# E:\Temp\*.bak E:\Temp\BackupZips\ 50 backup.zip
Write-Debug "Starting ZipFiles.ps1"
# check input arguments
$argsLen = 0
if ($args -ne $null )
{
$argsLen = $args.length
}
if ( $argsLen -lt 2 -or $argsLen -gt 5 )
{
HelpInfo
return
}
$i = 0;
# Get input parameters
$sourcePath = $args[$i++]
$destPath = $args[$i++]
if ( !$destPath.EndsWith("\") )
{
$destPath += "\"
}
[int]$numOfDays = 0
$zipFile = $null
[bool]$pathInZip = $true
if ( $argsLen -gt $i )
{
$r = [int]::TryParse($args[$i++], [ref]$numOfDays)
if ( $argsLen -gt $i )
{
$zipFile = $args[$i++]
if ( $zipFile -eq $null -or $zipFile.length -eq 0 )
{
$zipFile = $null
}
if ( $argsLen -gt $i )
{
$pathInZip = ($args[$i++] -eq 1)
}
}
}
# Test source & destiantion
if ( !(Test-Path $sourcePath) -or !(Test-Path $destPath) )
{
Write-Output "Nothing to do. Either ""$sourcePath"" or ""$destPath"" is empty or does not exist."
return
}
# ZIP library is from http://www.codeplex.com/DotNetZip
# ZIP dll library file in the local PC folder:
$ZIP_DLL = "C:\bin\Ionic.Zip\Ionic.Zip.dll"
$assemblyLoaded = [System.Reflection.Assembly]::LoadFrom($ZIP_DLL);
# Zip class
$zipClass = "Ionic.Zip.ZipFile";
Write-Debug "Start zip process ($sourcePath > $destPath)..."
ZipUpFiles $sourcePath $destPath $numOfDays $zipFile $pathInZip $zipClass
$assemblyLoaded = $null
#*============================================
#* END OF SCRIPT BODY
#*============================================
The first section of main body is to parse input parameters. As I mentioned,
$args is a PS variable for arguments. If there is less or more required parameters, function HelpInfo is called, which just output the usage of the script and it is omitted. When all the required parameters are parsed, the function ZipUpFiles is called.
Subscribe to:
Posts (Atom)
Posts
