MsSQL: Move tempdb

When you need to move a database in MsSQL -in this case the tempdb- you can use two scripts:

use tempdb
go
sp_helpfile
go

 

When running this script, it will show the location of the tempdb database

This script is usefull to determine the file location of the databases. After relocating the database, these files can be removed.

With the following script, you can move the tempdb database to another location:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\DATA\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'F:\MSSQL\DATA\templog.ldf')
go

After this script is run, the SQL services need to be restarted and the old files can be removed.

VB Script: DiskSpace Monitoring

When i needed a script to check multiple servers for disk usage, i found this script: http://gallery.technet.microsoft.com/ScriptCenter/en-us/cc8176ab-348b-4152-b9a3-25b6ad950d3e

Download it here: [wpdm_file id=”17″], or see the code:

'******************************************************************************
'* Provides data from disks from a list of servers (servers.txt)              *
'* Created by Nag Pal and Modify by Martin Scaine                             *
'* The original script don't manage error,                                    *
'* so if you can't connect to a server it will repeat the data from last.     *
'* Also I include the date on the name of the report file and on it header.   *
'* It can be usefull if you need to check it every day                        *
'******************************************************************************

On Error Resume Next
Const ForAppending = 8
Const HARD_DISK = 3
Const ForReading = 1

'Var declaration
dtmDay = Day(Date)
dtmMonth = Month(Date)
dtmYear = Year(Date)
dtmDate = CDate(dtmYear & "-" & dtmMonth & "-" & dtmDay)

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Open the server list
Set SrvList = objFSO.OpenTextFile("Servers.txt", ForReading)

'Create a result file
If dtmday < 10 and dtmMonth >9 then
Set ReportFile = objFSO.OpenTextFile ("Srv_Disk_Space_" & dtmYear & "-" & dtmMonth & "-0" & dtmDay & ".html", ForAppending, True)
End If
If dtmday > 9 and dtmMonth < 10 then
Set ReportFile = objFSO.OpenTextFile ("Srv_Disk_Space_" & dtmYear & "-0" & dtmMonth & "-" & dtmDay & ".html", ForAppending, True)
End If
If dtmday < 10 and dtmMonth < 10 then Set ReportFile = objFSO.OpenTextFile ("Srv_Disk_Space_" & dtmYear & "-0" & dtmMonth & "-0" & dtmDay & ".html", ForAppending, True) End If If dtmday > 9 and dtmMonth >9 then
Set ReportFile = objFSO.OpenTextFile ("Srv_Disk_Space_" & dtmYear & "-" & dtmMonth & "-" & dtmDay & ".html", ForAppending, True)
End If
i = 0

'Inicializar HTML
ReportFile.writeline("")
ReportFile.writeline("")
ReportFile.writeline("")
ReportFile.writeline("")
ReportFile.writeline("")
ReportFile.writeline("
-->")
ReportFile.writeline("")
ReportFile.writeline("")

ReportFile.writeline("

“) ReportFile.writeline(“”) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(”

“) ReportFile.writeline(“Server Space Disk Report ” & dtmdate & ““) ReportFile.writeline(“
")

'Server name declaration
Do Until SrvList.AtEndOfStream
	StrComputer = SrvList.Readline

	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")

	If Err.Number <> 0 Then
		ReportFile.writeline("

“) ReportFile.writeline(“”) ReportFile.writeline(” “) ReportFile.writeline(” “) Err.Clear Else ReportFile.writeline(”

Error – ” & StrComputer & ” – Error

“) ReportFile.writeline(“”) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(“”) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(” “) ‘Data recolection For Each objDisk in colDisks ‘Var init TotSpace=0 FrSpace=0 FrPercent=0 UsSpace=0 Drv=”Error” VolName=”Error” ‘Var charge TotSpace=Round(((objDisk.Size)/1073741824),2) FrSpace=Round(objDisk.FreeSpace/1073741824,2) FrPercent=Round((FrSpace / TotSpace)*100,0) UsSpace=Round((TotSpace – FrSpace),2) Drv=objDisk.DeviceID VolName=objDisk.DeviceID ‘Lnt=Len(VolName) ‘If Len(VolName) = 3 then If FrPercent > 20 Then ReportFile.WriteLine ” ” ElseIf FrPercent < 10 Then ReportFile.WriteLine ” ” Else ReportFile.WriteLine ” ” End If ‘Else ‘End If Next End If ReportFile.writeline(“”) ReportFile.writeline(” “) ReportFile.writeline(” “) ReportFile.writeline(”

” & StrComputer & “
Drive / Mount Total Capacity (in GB) Used Capacity (in GB) Free Space (in GB) Freespace %
” & Drv & “ ” & TotSpace & “ ” & UsSpace & “ ” & FrSpace & “ ” & FrPercent & “%” &”
” & Drv & “ ” & TotSpace & “ ” & UsSpace & “ ” & FrSpace & “ ” & FrPercent & “%” &”
” & Drv & “ ” & TotSpace & “ ” & UsSpace & “ ” & FrSpace & “ ” & FrPercent & “%” &”
")
Loop
ReportFile.WriteLine ""

PowerCLI: VMware Backup Script

Searching for way’s to create easy backup’s of a few virtual machines, i came on the website gestalt it (http://gestaltit.com/all/tech/virtualization/simon/vmware-backup-powercli-script/).

On this website there is a script which will create a snapshot of a VM, then clone it (thin provisioned) and finally remove the snapshot off the original VM.

Backup Script

# Import Backup CSV
$backupinfo =  Import-Csv C:\scripts\mybackups.csv

#Set Date format for clone names
$date = Get-Date -Format "yyyyMMdd"

#Set Date format for emails
$time = (Get-Date -f "HH:MM")

#Connect to vCenter
Connect-VIServer ""

foreach ($customer in $backupinfo)
{
	$vm = Get-VM $customer.MasterVM

	#Send Start Email
	C:\scripts\backupstartedemail.ps1

	# Create new snapshot for clone
	$cloneSnap = $vm | New-Snapshot -Name "Clone Snapshot"

	# Get managed object view
	$vmView = $vm | Get-View

	# Get folder managed object reference
	$cloneFolder = $vmView.parent

	# Build clone specification
	$cloneSpec = new-object Vmware.Vim.VirtualMachineCloneSpec
	$cloneSpec.Snapshot = $vmView.Snapshot.CurrentSnapshot

	# Make linked disk specification
	$cloneSpec.Location = new-object Vmware.Vim.VirtualMachineRelocateSpec
	$cloneSpec.Location.Datastore = (Get-Datastore -Name $customer.BackupDS | Get-View).MoRef
	$cloneSpec.Location.Transform =  [Vmware.Vim.VirtualMachineRelocateTransformation]::sparse

	$cloneName = "$vm-$date"

	# Create clone
	$vmView.CloneVM( $cloneFolder, $cloneName, $cloneSpec )

	# Write newly created VM to stdout as confirmation
	Get-VM $cloneName

	# Remove Snapshot created for clone
	Get-Snapshot -VM (Get-VM -Name $customer.MasterVM) -Name $cloneSnap | Remove-Snapshot -confirm:$False

	#Send Complete Email
	C:\scripts\backupcompletedemail.ps1
}
#Disconnect from vCentre
Disconnect-VIServer -Confirm:$false

Send Started Email Script

#Set Date format for emails
$timestart = (Get-Date -f "HH:MM")

$emailFrom = ""
$emailTo = ""
$subject = "[$vm - Backup Started]"
$body = "Backup Details
-------------
VM Name:",$vm,"
Clone Name:","$vm-$date","
Target Datastore:", $customer.BackupDS,"
Time Started:", $timestart

$smtpServer = ""
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)

Send Completed Email Script

#Set Date format for emails
$timecomplete = (Get-Date -f "HH:MM")

$emailFrom = ""
$emailTo = ""
$subject = "[$vm - Backup Complete]"
$body = "Backup Details
-------------
VM Name:",$vm,"
Clone Name:","$vm-$date","
Target Datastore:", $customer.BackupDS,"
Time Started:", $timestart,"
Time Completed:", $timecomplete
$smtpServer = ""
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)

CSV File

The content of the csv file is very simple. This is what mine looks like:

MasterVM,BackupDS
VM1,BackupDataStore
VM2,BackupDataStore

Automating VMware with PowerShell

LAB Summary
Welcome to the Automating VMware with PowerShell: Hands-On Lab. This lab will provide you hands-on experience in writing PowerShell scripts for managing VMware using the VI Toolkit (for Windows). Each participant will create scripts that will show you how to more effectively manage your virtual infrastructure. Some familiarity with PowerShell is helpful, but is not required. 
 
LAB Objective
The objective of this lab is to introduce you to the VI Toolkit (for Windows) and show you how easy automating VMware Virtual Infrastructure management is when you use PowerShell. We will cover a wide range of topics, including things like provisioning, storage, networking and monitoring. 

 Link: http://blogs.vmware.com/vipowershell/files/vmworld_europe_2008_powershell_lab_7_manual.pdf

Or local download: [wpdm_file id=”15″]

Delete files & Directories older then n days

To delete files and directories older then a specified days, use the following vbs script:

'
'delete folders specified in a number of days, also specified in the script.
'
On error resume next

Dim Directory
Dim Noofdays
Dim FSO
Dim FSO2
Dim LogFile
Dim Folderlist
Dim folders

Directory ="D:\Logs\directory"
Noofdays=cint(31)
LogFile="D:\logs\result.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSO2 = CreateObject("Scripting.FileSystemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")
'
If oFSO.FileExists(Logfile) Then
Const ForAppending = 8
Set ofile = oFSO.OpenTextFile(LogFile, ForAppending, True)
Else
Set oFile = oFSO.CreateTextFile(logfile, true)
End If
ofile.writeline "Delete Folders older than 31 days Started   --> " & now()

Set Folderlist = FSO.GetFolder(Directory)

Set folders = Folderlist.SubFolders
For Each d In Folders
'          msgbox d.name
'          msgbox d.size
'          msgbox d.dateCreated
'          msgbox d.dateLastModified
'          msgbox d.dateLastAccessed
tempdirectory = Directory & d.name

If  datediff("d",d.dateCreated,now()) > Noofdays Then
FSO2.DeleteFolder(tempdirectory )
ofile.writeline "Deleting Folder...." & tempdirectory

if err.number <>0 then
ofile.writeline cstr(now()) & "    " & Err.description
err.clear
end if
End If
Next
ofile.writeline "Delete Folders older than 31 days Completed --> " &now()
ofile.writeline "--------------------------------------------"
ofile.close

PowerCLI – VM Guest Disk Sizes

A oneline script to show the VM disk (or partiton) for each of your VM’s:

ForEach ($VM in (Get-VM |Get-View)){($VM.Guest.Disk |Select @{N=“Name“;E={$VM.Name}},DiskPath, @{N=“Capacity(MB)“;E={[math]::Round($_.Capacity/ 1MB)}}, @{N=“Free Space(MB)“;E={[math]::Round($_.FreeSpace / 1MB)}}, @{N=“Free Space %“;E={[math]::Round(((100 * ($_.FreeSpace))/ ($_.Capacity)),0)}}) | Format-Table}

Or look at the “advanced” script of virtu-al (http://www.virtu-al.net/2010/01/27/powercli-virtual-machine-disk-usage/)

Get ESX host CPU and Memory stats

Use the following CLI to get the state of your ESX hosts, the cpu and memory usage:

t-Datacenter | Sort | Get-VMHost | Sort | Get-View |`
Select Name, OverallStatus, `
@{N="CPU Usage (GHz)";E={[math]::round(
$_.Summary.QuickStats.OverallCpuUsage/1024,2)}}, `
@{N="Memory Usage (GB)";E={[math]::round(
$_.Summary.QuickStats.OverallMemoryUsage/1024,2)}}

Add line 7 to it to get all status except green:

Get-Datacenter | Sort | Get-VMHost | Sort | Get-View | `
Select Name, OverallStatus, `
@{N="CPU Usage (GHz)";E={[math]::round(
$_.Summary.QuickStats.OverallCpuUsage/1024,2)}}, `
@{N="Memory Usage (GB)";E={[math]::round(
$_.Summary.QuickStats.OverallMemoryUsage/1024,2)}} | `
Where { $_.OverallStatus -ne "green" }

source: http://laez.nl/quick-and-simple-vmware-esx-host-statistics/