Archive
getting started with Powershell
using progress Bar in powershell
Hey all ,
I was looking for an way to pop up an progress BAR for an ongoing progress within an POWERSHELL script ..
figured out powershell inheritably provide an inbuilt cmdlet for doing the same .
Command ::
write-progress
for detailed help and examples on this command use below cmdlet
## get-help
get-help write-help -full | more
Example ::
for ($i = 1; $i -le 100; $i++ )
{
get-process
write-progress -activity “Search in Progress” -status “$i% Complete:” -percentcomplete $i;}
Example for 2 bars ::
for ($i = 1; $i -le 100; $i++ )
{
get-process
write-progress -activity “Search in Progress” -status “$i% Complete:” -percentcomplete $i -CurrentOperation “outerloop” ;
for ($j = 1; $j -le 100; $j++ )
{
get-process
write-progress -Id 1 -activity “Search in Progress” -status “$j% Complete:” -percentcomplete $j -CurrentOperation “innerloop”;
}
}
Rename multiple FILES using POWERSHELL
Requirement ::
I needed to upload my Educational certificates onto a particular websites ..
after i was done with scanning my docs .. i never realised that the sites wont accept PDF or any other fromat Documents
which had special Characters in the name Exxmple –> ( ,&,*,@,$ ETC …
in my case the Documents were named Document(1).PDF
so i needed to get there name changed to just Document1.PDF
aligned snapshot is the what my docs looked
POWERSHELL SCRIPT ::
to change the names of the PDF files .. used POWERGUI and used below command to set my location to the path which
contained the PDF files
SL stands for SET LOCATION and is equvalent to CD
sl ‘D:\GS\DOCS\TEST_scancopy’
then i used below command to get a count of files that had round brackets [ ( ) ] in their names
get-childitem -Filter ‘*(*’ | measure
this gave me idea of how many docs need to renamed …
i used a while loop to run for 29 times … to ensure all the PDF files
having *(* present in their name are set to required name ..
The rename-item was used to set the names of PDF files .
below is PS Script i used to rename the docs … as i have around 29 docs .. i have used 30 as the no. of times the loop will run through
___________________SCRIPT STARTs __________
$int = 1
Write-Host $int
while ($int -ne 30)
{
Write-Host ‘within while loop’
Write-Host $int
$var = ‘Document’+ $int.ToString()+’.PDF’
# the names to which docs will be set
get-childitem -Filter ‘*(*’ | select -first 1 | rename-item -NewName $var
Write-Host ‘renamed to ‘ $var
$int ++
}
___________________SCRIPT ENDs __________
The SCRIPT ran fine ..
you may use similar approach to tackle .. any other requirement
specific to your need ENJOY 🙂
Delete OLD SQL Backups using Powershell in one go !!!
issue :
some times the maintance plans do not delete old Backups files (based on their age) ,that we have intended it to do
also the delete maintance task (subtask) may also skip files .. based on their creation date or AGE ..
manually deleting the old .BAK or any other files is not really inspiring after a certain time ..
Solution :
we can use Powershell (PS) to help us acheive the same result .. with few lines ..
SETUP :: im backing up the databases into a test folder .. but the PS script can used to do the same tasks for any storage filer server .. that you must be using to store backups — JUST MAKE SURE YOU PASS the CORRECT PATH ..
use below script to backup all database to a sepcfic path
USE master
GO
DECLARE @int varchar(15)
declare @Rand varchar(15)
DECLARE @SQL varchar(200)
declare @path varchar(200)
DECLARE @Cur varchar(20)
declare curr CURSOR FOR
SELECT name from sys.databases where database_id <> 2
OPEN curr
FETCH NEXT
FROM curr INTO @cur
WHILE @@FETCH_STATUS = 0
BEGIN
–declare @var char(20)
PRINT @cur
–set @var = @cur
set @int = convert(varchar(20),getdate(),112)
set @rand = substring(convert(varchar(10),rand()*1000,112),0,4)
set @path = ‘D:\POWERSHELL\TEST\New Folder\’+@cur+’_’+@int+’_’+@rand+’.BAK’
–print @path
backup database @cur to disk = @path
FETCH NEXT
FROM curr INTO @cur
END
CLOSE curr
DEALLOCATE curr
GO
Above script is send all full backups to path –> D:\POWERSHELL\TEST\New Folder\ (choose any path .. that works for you !!!)
once we have backups in place .. (or we know which Backups are to deleted according to their age ) .. start you powershell ..
START –> RUN –> POWERSHELL.EXE (will work on all machines above windows XP,Server 2003 ) .. for this 2 .. you need to download and install
you do not have to change you path .. to the folder .. from where you want to delete files .. (example in case of external storage location which is accessed like \\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\) .. you can run the powershell script .. from wherever your current path ..
in my case im switching to the location which holds the path .. because i need to show few options ..
the -Recurse option present in DIR or get-CHILDITEM is able to traverse through all sub folders under the parent folder ..
hence with this option you will be able to delete old files in a external storage path like —
\\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\
below results show .. that DIR – Recurse is able to trverse through D:\POWERSHELL\TEST\ and a subfolder named New folder
(we excluded the BAK files .. so as to minimize the outcome)
below command will give you backups created 3 hours back .. you will be looking for much older backups and use below command
which works with days instead of hours (i have used below .. as a DEMO )
USE addhours(-30) (to get a count of files and folders which creation date older then 30 days )
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse | Where-Object {$_.creationdate-lt ($(getdate).adddays(-30))} | measure
NOW : we only want to work with .BAK files and possibly may like to exlcude some files from deletion command … so we will use -include *.BAK (or *.SAFE) and -Exlcude (to exclude any type of files example *.PS1 )
if you use SQL SAFe for backups or any other Backup utility use the required extention
use below command
basically .. we are using -lt (less then comparision) to filter out files older then 30 days .. (in example its older then 2 hours)
below command will give .. only 2 files .. of type *.BAK ..
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclu
de *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | select -First 2
use below command to get a exact count of *.BAK files .. you will be deleting
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK
-exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | measure
if you trying to get a count of files older then 30 days .. on large file server .. above command will take some time to complete ..
———-command to delete files older then certains Days ————————
********** take care of below points **************
1> mention correct path from where you want to delete backup files (even sub-folder will be taken into account)
2> mention -include *.BAK .. to make sure only backup files are deleted .. also if needed exclude any application or config file
if its possibly on the mentioned path ..
3> make sure to mention correct AGE or no. of days you want to delete files adddays(-30)) .. means you want to delete a month old files or 2 months old files ETC
****************************************************************
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | remove-item -force
****************************************************************
Above command will delete the backup files of type (*.BAK) and exclude the files of type (*.PS1) older then 30 days
under path D:\POWERSHELL\TEST\New Folder and any sub folder .. it may have ..
try out this command .. and let me know your thoughts ..
ENJOY 🙂
using powershell to select from Tables in database
I was looking for some ways to connect to connect to SQL instance .. Sarabpreet had covered quite interesting ways to do the same … you may check his article over here (a big Thanks to Sarab for sharing the same …)
below is what I tried on windows 7 (SQL 2008 R2 )
once we are within shell .. shell treats all objects with SQL
as files and folders … like what we have while we are working with CMD
we can use different ways to connect to sql server via PowerShell tools ..
1> type powershell.exe in the start –> run prompt and hit enter
this will keep you PowerShell prompt
2> type SQLPS.EXE in the start –> run prompt and hit enter
3> or right click on any tab in the SSMS and click on Start PowerShell
depending on were you have right clicked .. you be taken to appropriate
depth or hierarchy with in the SQL instance
for this example we will use step 3 …
refer the snapshot on the right …
if you right click on any of the object within the SQL instance .. we will
taken into the corresponding hierarchy level within the SQL
once we are within the database .. we will use command DIR .. to look for
list of folder and files within (comparing the database structure to OS file system )
Note :: after a certain level command will not return any results or output …
below is an example of a command ran at database level .. we can all objects that are available in SSMS for a database
we will need to use a SQLPS command named INVOKE-SQLCMD to get any meaning full data out of the SQL server ..
once OS based commands like dir (which will work same as LS and get-childitem) hits its limits ..
1> get-help invoke-sqlcmd -FULL
2> get-help invoke-sqlcmd -EXAMPLE
3> get-help invoke-sqlcmd
invoke-SQLCMD has multiple parameters which help use to define query and connection options
so once we are within correct location (use cd or cd .. to move from one path to another .. )
we will select data from a table ..as below .. command used is as below (we may try it on any table with some data in it)
invoke-sqlcmd “select top 10 * from dbo.T2 where id > 1000”
below is an example of using SQLCMD to run another command to populate data ..
so you can run any t-sql command in SQLPS shell.. that use can run in SSMS
so what is use of all this .. if we can run these commands in SSMS … well the power of powershell
really comes across when we have list of servers to manange .. and we want to automate some admin
tasks across all of them …. going forward I will share more examples on Powershell …
Enjoy:)
powershell crash course part 2
Hi all ,
part 2 of PowerShell .. took a back stage and I just figured out that I do not need to reinvent the wheel .. because some of the dedicated powershell
sites do a lot better work at it … YES ,, its extremely useful for SQL admins too .. so I will direct you to that sites instead …
SITES –> http://powershell.com/cs/
on this sites .. GO to TAB named EBOOKV2 .. which has 18 lesson online eBook
and it covers most of the basics we need to start with …
–> http://powershell.com/cs/blogs/ebookv2/default.aspx
below I will provide small descriptions of lessons and topics covered in the same ..
Chapter 1. The PowerShell Console
This chapter will introduce you to the PowerShell console and show you how to configure it, including font colors and sizes, editing and display options. Topics Covered: Starting PowerShell First Steps with the Console Incomplete
Chapter 2. Interactive PowerShell
Powershell Crash course part – 1
Windows PowerShell exists? (Photo credit: crazytales562)
Hi All,
in this efforts , I will cover some PS basics to get myself and you started on Powershell..
Powershell helps DBA’s to achieve some complex functionality which are not possible by t-sql and command prompt
PS 1 had around 130 commands ,PS2 and PS3 have far more commands now
InstallingPS ..
PS wont get install on windows 2000 or older version then that ..
Newer OS like windows 7 and higher have Powershell preinstalled in that ..
for windows XP,windows 2003 etc go to http://www.microsoft.com/download to get your setup (check whether you need 32 or 64 bit)
to check whether PS is already available for your OS .. either enter powershell.exe in the START–> run prompt
OR go to add/remove program winmdows componets and add Powershell component ..
customizing the SHELL ..
you can right click on the edge of the prompt to customize the size of the window to suite ur preference …
getting Started :: (ur script may not run for that you have to set your execution policy to > set – executionpolicy unrestricted (refer this link for details PowerShell Basics – Execution Policy and Code Signing Part 1 (pauldotcom.com) ))
all the commands (also called as command-lets or cmdlets) in PS are in the form of VERB-noun form
even if the command returns multiple results the noun is singular ..
for example type get-process on the PS prompt (this will list all the process currently running on system )
PS D:\powershell\myscript> get-process
Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
——- —— —– —– —– —— — ———–
194 4 3632 652 46 1.67 4140 AcroRd32
349 9 55876 29360 162 109.86 4700 AcroRd32
277 8 5296 712 61 5.59 3452 AdobeARM
below is my default prompt .. that i have set my powrshell to start in ..
PS D:\powershell\myscript>
you will most probably start in in PS C:\document and setting\yourID >
to read the content of a file example text file use :: get-content
PS D:powershell\myscript> get-content myfile.txt
all commandlets take parameters to acheive different funalities and parameters are positional … meaning you may skip typing parameter name if you passing the actual value of parameter
PS D:powershell\myscript> get-content myfile.txt
is same as PS D:powershell\myscript> get-content -path myfile.txt
where –path was the parameter
in PS if a articular path has a space in it use double quotes for it
PS D:powershell\myscript> get-content “C:\test files\myfile.txt”
ALIASES :: to make all administrators (*nix,cmd.exe) switch to PS an easier curve .. PS has all *nix working in PS too .. so you already many PS commands without knowing it 🙂
example :: cd ,dir,mkdir,del,ren,remdir will all work in PS without any issues ..
as all of them are aliases to PS under hood commands
PS D:powershell\myscript> get-alias CD
CommandType Name Definition
———– —- ———-
Alias cd Set-Location
so CD is alias of PS command named SET-LOCATION
to get a list of all alias just type –> get-alias
creating a new alias (it will be available only in your session : to make it persistent you need to save it in ur profile )
PS D:powershell\myscript> new-alias N notepad
PS D:powershell\myscript> N
above command will open up new notepad window .. you can create aliases of ur preferences ..
Thanks for reading .. will continue in part -2
Related articles
- Tips & Tricks Tuesday: Adding tasks from PowerShell (rememberthemilk.com)
- How to switch from powershell v3 to v2 ? (itworldjd.wordpress.com)
- Powershell Alias (sqlcurve.wordpress.com)
- Cloud Spelunking, Managing Azure form your Desktop via PowerShell (the Setup) (blogs.msdn.com)
- Geek School: Learning to Use Cmdlets in PowerShell (howtogeek.com)
- Episode 217 – The Scripting Guy Ed Wilson talks about his new PowerShell books (powerscripting.wordpress.com)
- Russian ransomware takes advantage of Windows PowerShell (8080fourfour3.wordpress.com)
- Ransomware Written In PowerShell (And How To Disable It) (lifehacker.com.au)
- Close applications gracefully using powershell (soykablog.wordpress.com)
Powershell Alias
Power shell is object oriented .. all commands or cmdlets .. return or take objects as parameters ..
to make switch from unix or *NIX easier .. to powershell most of the command have corresponding aliases in PS ..
example :
1> CLS – to clear screen
2> DIR — to give list files and folders at current path/directory
3> to get all alias type at the PS prompt
get-ALIAS or
get-alias cmdlet_name (to get alias for particular cmdlet)
4> to create a temporary alias of yourself
EG: set-ALIAS np notepad
once you have created alias .. if you just type np .. at the PS prompt .. it will open up Notepad
SQL DBA learning curve 