search
Categories
Sponsors
VirtualMetric Hyper-V Monitoring, Hyper-V Reporting
Archive
Blogroll

Badges
MCSE
Community

Cozumpark Bilisim Portali
SQL 2012 Day – Backup and Restore sunumumun PS kodları
Posted in Windows Powershell | No Comment | 2,139 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
}

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

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>

Bu komutları sunum sırasında sıfırdan yazmak zor olacak ama deneyeceğiz bakalım :)



Leave a Reply