Posted in
Windows Powershell |
No Comment | 2,138 views | 23/03/2013 08:30
Merhaba,
Portal yapımı sırasında kullandığım kodlar aşağıda:
New-SQLDatabaseBackup:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
| function New-SQLDatabaseBackup {
param (
# Instance Name
[Parameter(
Mandatory = $true,
HelpMessage = 'Instance Name of SQL Server.')]
$ServerInstance,
# Database Name
[Parameter(
Mandatory = $true,
HelpMessage = 'Database Name.')]
$Database,
# Debug Mode
[Parameter(
Mandatory = $false,
HelpMessage = 'Debug Mode')]
[switch]$DebugMode = $false
)
# Enable Debug Mode
if ($DebugMode)
{
$DebugPreference = "Continue"
}
else
{
$ErrorActionPreference = "silentlycontinue"
}
# Date and Time Calculation
$JobTime = Get-Date -format HHmm
$HourString = (Get-Date).ToString("HH")
$MinuteString = (Get-Date).ToString("mm")
$DayString = (Get-Date).ToString("dd")
$MonthString = (Get-Date).ToString("MM")
$YearString = (Get-Date).ToString("yyyy")
Write-Debug "Jobtime: $JobTime"
# Log File
$LogFile = "C:\Program Files\Microsoft SQL Server\MSSQL11.SYSADMINSQL\MSSQL\Backup\backup_logs.txt"
# Backup File
$BackupFile = "C:\Program Files\Microsoft SQL Server\MSSQL11.SYSADMINSQL\MSSQL\Backup\" + $Database + "_" + $Jobtime + "_" + $DayString + $MonthString + $YearString + ".bak"
# XML Output Path
$XMLOutputPath = "C:\reports.xml"
# Backup Database
$BackupSQLDB = Backup-SqlDatabase -Database $Database -ServerInstance $ServerInstance -Checksum -Initialize -CompressionOption On -BackupFile $BackupFile
# Get Backup Size
$TestBackup = Test-Path -Path $BackupFile
if ($TestBackup)
{
$BackupSize = (Get-Item $BackupFile).Length / 1MB
}
else
{
$BackupSize = "0"
}
# Backup Status
if ($TestBackup)
{
$Status = "OK"
}
else
{
$Status = "Failed"
}
# Log Content
$LogContent = $Database + ";" + $HourString + ":" + $MinuteString + " " + $DayString + "." + $MonthString + "." + $YearString + ";" + $BackupSize + ";" + $Status
# Log Backup Job
Add-Content -Value $LogContent -Path $LogFile
# Get Log Output
$LogOutput = Get-Content -Path $LogFile
# XML Header
$BackupXML = "<?xml version=""1.0"" encoding=""utf-8""?>`n"
$BackupXML += "<Result>`n"
$BackupXML += " <Code>1</Code>`n"
$BackupXML += " <Message>Operation is completed</Message>`n"
foreach ($LogContent in $LogOutput)
{
$Log = $LogContent.Split(";")
$DBName = $Log[0]
$BackupDate = $Log[1]
$BackupSize = $Log[2]
$BackupSize = ([math]::round(($BackupSize), 0))
$Status = $Log[3]
# Backup Reports
$BackupXML += " <OperationResult>`n"
$BackupXML += " <DBName>$DBName</DBName>`n"
$BackupXML += " <BackupDate>$BackupDate</BackupDate>`n"
$BackupXML += " <BackupSize>$BackupSize</BackupSize>`n"
$BackupXML += " <Status>$Status</Status>`n"
$BackupXML += " </OperationResult>`n"
}
# XML Footer
$BackupXML += "</Result>`n"
# XML Output
Clear-Content -Path $XMLOutputPath
Add-Content -Value $BackupXML -Path $XMLOutputPath
} |
function New-SQLDatabaseBackup {
param (
# Instance Name
[Parameter(
Mandatory = $true,
HelpMessage = 'Instance Name of SQL Server.')]
$ServerInstance,
# Database Name
[Parameter(
Mandatory = $true,
HelpMessage = 'Database Name.')]
$Database,
# Debug Mode
[Parameter(
Mandatory = $false,
HelpMessage = 'Debug Mode')]
[switch]$DebugMode = $false
)
# Enable Debug Mode
if ($DebugMode)
{
$DebugPreference = "Continue"
}
else
{
$ErrorActionPreference = "silentlycontinue"
}
# Date and Time Calculation
$JobTime = Get-Date -format HHmm
$HourString = (Get-Date).ToString("HH")
$MinuteString = (Get-Date).ToString("mm")
$DayString = (Get-Date).ToString("dd")
$MonthString = (Get-Date).ToString("MM")
$YearString = (Get-Date).ToString("yyyy")
Write-Debug "Jobtime: $JobTime"
# Log File
$LogFile = "C:\Program Files\Microsoft SQL Server\MSSQL11.SYSADMINSQL\MSSQL\Backup\backup_logs.txt"
# Backup File
$BackupFile = "C:\Program Files\Microsoft SQL Server\MSSQL11.SYSADMINSQL\MSSQL\Backup\" + $Database + "_" + $Jobtime + "_" + $DayString + $MonthString + $YearString + ".bak"
# XML Output Path
$XMLOutputPath = "C:\reports.xml"
# Backup Database
$BackupSQLDB = Backup-SqlDatabase -Database $Database -ServerInstance $ServerInstance -Checksum -Initialize -CompressionOption On -BackupFile $BackupFile
# Get Backup Size
$TestBackup = Test-Path -Path $BackupFile
if ($TestBackup)
{
$BackupSize = (Get-Item $BackupFile).Length / 1MB
}
else
{
$BackupSize = "0"
}
# Backup Status
if ($TestBackup)
{
$Status = "OK"
}
else
{
$Status = "Failed"
}
# Log Content
$LogContent = $Database + ";" + $HourString + ":" + $MinuteString + " " + $DayString + "." + $MonthString + "." + $YearString + ";" + $BackupSize + ";" + $Status
# Log Backup Job
Add-Content -Value $LogContent -Path $LogFile
# Get Log Output
$LogOutput = Get-Content -Path $LogFile
# XML Header
$BackupXML = "<?xml version=""1.0"" encoding=""utf-8""?>`n"
$BackupXML += "<Result>`n"
$BackupXML += " <Code>1</Code>`n"
$BackupXML += " <Message>Operation is completed</Message>`n"
foreach ($LogContent in $LogOutput)
{
$Log = $LogContent.Split(";")
$DBName = $Log[0]
$BackupDate = $Log[1]
$BackupSize = $Log[2]
$BackupSize = ([math]::round(($BackupSize), 0))
$Status = $Log[3]
# Backup Reports
$BackupXML += " <OperationResult>`n"
$BackupXML += " <DBName>$DBName</DBName>`n"
$BackupXML += " <BackupDate>$BackupDate</BackupDate>`n"
$BackupXML += " <BackupSize>$BackupSize</BackupSize>`n"
$BackupXML += " <Status>$Status</Status>`n"
$BackupXML += " </OperationResult>`n"
}
# XML Footer
$BackupXML += "</Result>`n"
# XML Output
Clear-Content -Path $XMLOutputPath
Add-Content -Value $BackupXML -Path $XMLOutputPath
}
Twitter üzerinden komut çekerken kullandığım kodlar:
1
2
3
4
5
6
7
8
9
| # Twitter
$Request = New-Object -ComObject Msxml2.XMLHTTP
$APIURL = "https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=yusufozturk&count=1"
$Request.open('GET', $APIURL, $false)
$Request.setRequestHeader("Content-type", "application/x-www-form-urlencoded")
$Request.setRequestHeader("Content-length", $Parameters.length)
$Request.setRequestHeader("Connection", "close")
$Request.send($Parameters)
$Response = [xml]$Request.responseText |
# Twitter
$Request = New-Object -ComObject Msxml2.XMLHTTP
$APIURL = "https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=yusufozturk&count=1"
$Request.open('GET', $APIURL, $false)
$Request.setRequestHeader("Content-type", "application/x-www-form-urlencoded")
$Request.setRequestHeader("Content-length", $Parameters.length)
$Request.setRequestHeader("Connection", "close")
$Request.send($Parameters)
$Response = [xml]$Request.responseText
Portal, mevcut olan PoSHStats template’i ile oluşturuldu. Table kısmı aşağıda:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| <!-- SQL Server Backup Reports -->
<script type="text/javascript" src="js/custom/jquery.sql.backupreports.js"></script>
<div class="table">
<div class="head"><h5 class="iStats">Backup Reports</h5></div>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="SQL">
<thead>
<tr>
<th>Database Name</th>
<th>Backup Date</th>
<th>Backup Size (MB)</th>
<th>Status</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div> |
<!-- SQL Server Backup Reports -->
<script type="text/javascript" src="js/custom/jquery.sql.backupreports.js"></script>
<div class="table">
<div class="head"><h5 class="iStats">Backup Reports</h5></div>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="SQL">
<thead>
<tr>
<th>Database Name</th>
<th>Backup Date</th>
<th>Backup Size (MB)</th>
<th>Status</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
Bu komutları sunum sırasında sıfırdan yazmak zor olacak ama deneyeceğiz bakalım :)