Datenbank Backup – Powershell

PS> .backup.ps1 -serverName “localhostSQLEXPRESS” -backupTempDirectory “C:tmp” -daysToStoreBackups 1 -copyDirectory “pfadzumDatenbank_Backup”

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]

param(
$serverName,
$backupTempDirectory,
$daysToStoreBackups,
$copyDirectory
)

 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
{
write-host "Backup Database" $database.Name -foregroundcolor green
$dbName = $database.Name

$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupTempDirectory + "" + $dbName + "_" + $timestamp + ".bak"

$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)

"backed up $dbName ($serverName) to $targetPath"
}

Get-ChildItem "$backupTempDirectory*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"

Write-Host "Move the $dbName database(s): $backupTempDirectory$dbName*.bak to $copyDirectory"
mv $backupTempDirectory$dbName*.bak $copyDirectory -force

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Advertisements

Dateien im Ordner zippen, löschen und archivieren – Powershell

Dateien, die älter sind als x Tage, sollen innerhalb eines Ordner gezippt, gelöscht und archiviert werden.


<#
.SYNOPSIS
.
.DESCRIPTION
Find, delete and zip files in a folder.
.PARAMETER days
Check the last write time of a file. File is older than x days
.PARAMETER path
Path of the root folder of the files to be deleted.
.PARAMETER zip
Makes an archive containing all the files that were found.
.PARAMETER delete
Deletes the files that were found.
.PARAMETER archivePath
Specifies the path of the archive file. Standard path: C:tmparchive-"DATE".zip
.EXAMPLE
C:PS> .delete_old_files.ps1 -days 5 -path C:tmp -zip yes -delete yes -archivepath C:archives
Files that are older than 5 days from now on, root folder C:tmp, files will be zipped and deleted, the archive for the zip files will be in C:archives
Note that 7zip is a must have for zipping the files!
.NOTES
Author: Jens Braun
Date: Feb 04, 2016
#>

param(
[parameter(Mandatory=$true)]
[int]$days,
[parameter(Mandatory=$true)]
[string]$path,
[parameter(Mandatory=$false)]
[string]$zip,
[parameter(Mandatory=$false)]
[string]$delete,
[parameter(Mandatory=$false)]
[string]$archivePath
)

$sz="C:Program Files (x86)7-Zip7z.exe"
$z=1
$date = get-date -uformat "%Y-%m-%d"
#Soll gezippt werden?
if($zip -eq "yes"){
if(test-path "C:Program Files (x86)7-Zip7z.exe"){
Write-Host "7zip found"
Write-Host "Files will be zipped"
if($archivePath){
if(test-path $archivePath){
$archivePath= -join ($archivePath,"archive-",$date,".","zip")
Write-Host "archive to $archive"
}else{
$archivePath = "C:tmparchive-$date.zip"
Write-Host "Archive Path not found, using Standardarchive: $archivePath"
}
}else{
$archivePath = "C:tmparchive-$date.zip"
Write-Host "Archive Path not found, using Standardarchive: $archivePath"
}
}else{
Write-Warning "7zip not found"
$zip = "no"
}
}else{
Write-Host "Files will not be zipped"
$zip = "no"
}

#Soll gelöcht werden?
if($delete -eq "yes"){
Write-Host "Files will be deleted"
}else{
Write-Host "Files will not be deleted"
$delete="no"
}

#weder noch
if(($delete -eq "no") -and ($zip -eq "no")){
Write-Warning "Neither delete nor zipping, exiting!"
break
}

#path vorhanden?
if(!(test-path $path)){
write-warning "Archive Path not found"
break
}
#Nur Dateien, keine Ordner!
$files2delete = gci -path $path -recurse -force | ? -FilterScript {(($_.LastWriteTime -lt (get-date).adddays(-$days)) -and (! $_.PSIsContainer)) }

$Files2Remove = $files2delete.Count
foreach ($file in $files2delete){
Write-Progress -Activity "Working..." -status ("Files Processed $z of "+$Files2Remove.ToString()) -percentComplete ($z / $Files2Remove*100)
if($zip -eq "yes"){
& $sz a $archivePath $file.fullname -ssc >$null 2>&1
}

if($delete -eq "yes"){
remove-item $file.fullname -force -whatif
}

$z++
}

Massenkonvertierung .doc nach .pdf – Powershell

Mit diesem Powershell Skript ist es möglich, alle Word Dokumente (.doc und .docx) automatisch in ein PDF umzuwandeln.
Dazu muss der Code als .ps1 Datei gespeichert werden.
Entweder wird danach das Skript direkt aufgerufen oder in der Powershell gestartet und der Pfad eingegeben.

powershell-console

Anschließend befinden sich neben den Word Dokumenten ebenfalls PDF Dokumente im Verzeichnis.

explorer-docs

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]

$documents_path = Read-Host "Pfad eingeben "
$word_app = New-Object -ComObject Word.Application
# This filter will find .doc as well as .docx documents
Get-ChildItem -Path $documents_path -Filter *.doc? | ForEach-Object {
$document = $word_app.Documents.Open($_.FullName)
$pdf_filename = "$($_.DirectoryName)$($_.BaseName).pdf"
$document.SaveAs([ref] $pdf_filename, [ref] 17)
$document.Close()
}
$word_app.Quit()

Alternativ gibt es das Skripte hier als Download.[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]