A list of shares on a given Windows host can be created by:
Get-WmiObject Win32_Share
But the two shares "IPC$" og "ADMIN$" is not really interesting.
A more usefull list can be created by:
Get-WmiObject -Query "SELECT * FROM Win32_Share WHERE Name != 'ADMIN$' AND Name != 'IPC$'"
I know it's not correct or nice to use "SELECT *...", but this is an quick and dirty example.
The WMI class definition can be browsed in WMI CIM Studio, that can be downloaded from Microsoft.
Please use your preferred internet search site to get the current URI.
2008-09-23
2008-09-15
Restore Workshop draft
Some day - no, week(-end) - I would like to gather some comrades in arms - SQL Server naturally! - and do a workshop on SQL Server database restore.
The initial list of subjects is like this:
Each group should have one workstation and three servers - virtual servers could do, but there should be enough disk capacity to create several partitions for each server.
The workshop should be placed away from a major city, so that the participants are not tempted to drop in and out... Of course the food and the housing should support the process and the good spirit.
The seats should be distributed to persons that did not participate before those did the subjects last year.
I think it could be fun and most enlightening!
Paul Randal has made list of good questions to ask yourself - „SQLskills SQL101: Practicing disaster recovery“
2017-07-18 Reference to Paul Randal added.
The initial list of subjects is like this:
- Restore on full backup
- Restore on log backup
- Restore on differential backup
- Restore point-in-time
- Restore online
- Restore Full-Text index
- Restore Filestream
- All above on both single and multiple filegroups
- Restore msdb
- Restore master
- Rebuild database instance
- Rebuild Windows Server
- Discuss how to get, store and access the actual version of operating system, applications, .NET, MDAC etc.
- Switch center using clustering, database mirroring and log shipping
- Validate backup - discuss how to do this automatic
- Discuss device file versus backup files
- Recover using snapshot
- Discuss documentation like how, where, what, tools, access and maintenance
- Discuss security about backup sets and snapshots and access to these
- A general discussion about SQL Server infrastructure from the viewpoint of recovery and restore
Each group should have one workstation and three servers - virtual servers could do, but there should be enough disk capacity to create several partitions for each server.
The workshop should be placed away from a major city, so that the participants are not tempted to drop in and out... Of course the food and the housing should support the process and the good spirit.
The seats should be distributed to persons that did not participate before those did the subjects last year.
I think it could be fun and most enlightening!
Paul Randal has made list of good questions to ask yourself - „SQLskills SQL101: Practicing disaster recovery“
History
2008-09-15 Post created on old notes.2017-07-18 Reference to Paul Randal added.
2008-08-14
ISO 8601 date formatting using PowerShell
My favorite timestamp is the ISO 8601 format [YYYY]-[MM]-[DD]T[hh]:[mm]:[ss.sss]Z.
The ISO 8601 standard is very well described in a Wikipedia article (ISO 8601).
I do miss a ISO formatting option in .NET/PowerShell, but using DateTime formatting, an acceptable result is found:
$theDate = Get-Date
"{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffff'Z'}" -f $theDate.ToUniversalTime()
$theDate.ToUniversalTime().ToString( "yyyy-MM-ddTHH:mm:ss.fffffffZ" )
The formatting is shown twice using different syntax.
I've chosen seven digits on the second because I have logging in mind while working with time stamp. I have (once) seen that three digits wasn't enough - it was in a technical database log on a z/Server "mainframe".
A execution gives this result:
2008-01-13T10:02:36.8992896Z
I would like to get the week of the year, but again this is a problem. Is it because a week number is more used in Europe than on the other side of the Atlantic?
Thomas Lee has tried (eggheadcafe conversation), and I tried his examples. I regret to say he's right – again.
Using the formatting specifier 'o' (oscar) from the DateTimeFormatInfo Class to the CmdLet Get-Date will generate a output with seven digits, but also a a part with a reference to the timezone. Mark Puckett has made a comment to this post about this possibility. And he is right that this specifier wil give a output that is very precise. But I choose not to use this as I go for UTC time in a log as I think it makes the log entries more readable on senior management level.
2017-02-05 Section on -Format o added inspired by comment by Mark Puckett.
The ISO 8601 standard is very well described in a Wikipedia article (ISO 8601).
I do miss a ISO formatting option in .NET/PowerShell, but using DateTime formatting, an acceptable result is found:
$theDate = Get-Date
"{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffff'Z'}" -f $theDate.ToUniversalTime()
$theDate.ToUniversalTime().ToString( "yyyy-MM-ddTHH:mm:ss.fffffffZ" )
The formatting is shown twice using different syntax.
I've chosen seven digits on the second because I have logging in mind while working with time stamp. I have (once) seen that three digits wasn't enough - it was in a technical database log on a z/Server "mainframe".
A execution gives this result:
2008-01-13T10:02:36.8992896Z
I would like to get the week of the year, but again this is a problem. Is it because a week number is more used in Europe than on the other side of the Atlantic?
Thomas Lee has tried (eggheadcafe conversation), and I tried his examples. I regret to say he's right – again.
Using the formatting specifier 'o' (oscar) from the DateTimeFormatInfo Class to the CmdLet Get-Date will generate a output with seven digits, but also a a part with a reference to the timezone. Mark Puckett has made a comment to this post about this possibility. And he is right that this specifier wil give a output that is very precise. But I choose not to use this as I go for UTC time in a log as I think it makes the log entries more readable on senior management level.
History
2008-01-14 Blog post created2017-02-05 Section on -Format o added inspired by comment by Mark Puckett.
SQL Server Agent PowerShell job step
This seems like a valid and simple way to execute a PowerShell script in a SQL Server Agent job step.
The script I've been testing with looks like this (ExecuteablePS.ps1):
param( [string]$script:myParam = $( throw "Error: Command statement is missing." ) )
Write-Output "Hello to output."
Write-Host "Hello to host."
Write-Output "The parameter 'myParam' was given the value '$myParam'."
It is on purpose that the script requires a parameter, while most - if not all - my automation scripts is parametrized on the physical elements like server (host).
The script also demonstrates that both the Output stream and the Host stream is caught by SQL Server Agent.
The SQL Server Agent job step is defined as a CmdExec step.
The step only has one line:
powershell.exe -NoLogo -NoProfile -Command "&'C:\SQLAdmin\Script\ExecuteablePS.ps1' 'CoolValue'"
I spend some time getting the " and ' right...
When the job is executed, the history is:
Executed as user: Sandbox\Frodo. Hello to output. Hello to host. The parameter 'myParam' was given the value 'CoolValue'. Process Exit Code 0. The step succeeded.
I use CmdExec in stead of PowerShell as job step type, so that the solution can be used below SQL Server 2008.
Also I like to use powershell.exe instead of sqlps.exe. This makes the solution valid on PowerShell v2.
The script I've been testing with looks like this (ExecuteablePS.ps1):
param( [string]$script:myParam = $( throw "Error: Command statement is missing." ) )
Write-Output "Hello to output."
Write-Host "Hello to host."
Write-Output "The parameter 'myParam' was given the value '$myParam'."
It is on purpose that the script requires a parameter, while most - if not all - my automation scripts is parametrized on the physical elements like server (host).
The script also demonstrates that both the Output stream and the Host stream is caught by SQL Server Agent.
The SQL Server Agent job step is defined as a CmdExec step.
The step only has one line:
powershell.exe -NoLogo -NoProfile -Command "&'C:\SQLAdmin\Script\ExecuteablePS.ps1' 'CoolValue'"
I spend some time getting the " and ' right...
When the job is executed, the history is:
Executed as user: Sandbox\Frodo. Hello to output. Hello to host. The parameter 'myParam' was given the value 'CoolValue'. Process Exit Code 0. The step succeeded.
I use CmdExec in stead of PowerShell as job step type, so that the solution can be used below SQL Server 2008.
Also I like to use powershell.exe instead of sqlps.exe. This makes the solution valid on PowerShell v2.
2008-08-13
Write to user defined file with PowerShell
I'm working on a general solution for PowerShell job execution on SQL Server Agent. This generates a lot of small tasks, which mostly will be described here with a solution.
Right now it's about logging, and for this I'm thinking in several directions.
This task is for a solution using files, that are defined in the executing script.
The script writes to the same file in each execution, but the file will only contain the last execution:
$FileName = $MyInvocation.MyCommand.Name.split(".")[0] + ".Log"
Write-Host $FileName
"Hello to file..." > $FileName
"Another hello to file" >> $FileName
The execution generates this content (Get-Content Write2File.Log):
Hello to file...
Another hello to file
Another execution gives the same content - two lines, not four.
Right now it's about logging, and for this I'm thinking in several directions.
This task is for a solution using files, that are defined in the executing script.
The script writes to the same file in each execution, but the file will only contain the last execution:
$FileName = $MyInvocation.MyCommand.Name.split(".")[0] + ".Log"
Write-Host $FileName
"Hello to file..." > $FileName
"Another hello to file" >> $FileName
The execution generates this content (Get-Content Write2File.Log):
Hello to file...
Another hello to file
Another execution gives the same content - two lines, not four.
Subscribe to:
Posts (Atom)
Posts
