Backup SQLServer 2008 DataBase using PowerShell

最新推荐文章于 2021-05-29 11:37:07 发布

paoling2010

最新推荐文章于 2021-05-29 11:37:07 发布

阅读量387

点赞数

分类专栏： PowerShell 文章标签： database powershell sqlserver exception server disk

本文链接：https://blog.csdn.net/paoling2010/article/details/8028795

版权

PowerShell 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

This script will backup all database execept "tempdb", as follow:

try{

[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") "(local)"
$backupDirectory = $server.Settings.BackupDirectory
foreach($dbToBackup in $server.Databases)
{
if($dbToBackup.Name -ne "tempdb")
{
   Write-Debug "Begin backup database $($dbToBackup)"
   $db = $server.Databases[$dbToBackup.Name]
   $dbName = $db.Name
   $timestamp = Get-Date -format yyyyMMddHHmmss
   $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($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File")
   $smoBackup.SqlBackup($server)
}
}
$directory = Get-ChildItem $backupDirectory
$backupFilesList = $directory | where {$_.extension -eq ".bak"}
$backupFilesList | Format-Table Name, LastWriteTime
}
catch [Exception]
{
throw "Backup database error"+ $_.Exception.message
}
Write-Debug "BackUp database Success..."