Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- $BackupPath = "C:\SQLServer\Backups"
- $ServerName = "Localhost"
- $DatabaseServer = "localhost\SP13"
- # little helper function to ping the remote server.
- function Get-ServerOnline ([string] $server)
- {
- $serverStatus = (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
- $dt = get-date -format yyyyMMddHHmmss
- Write-Host "$dt - Server Online: $serverStatus"
- return $serverStatus
- }
- # Load the SQL Server SMO Libraries
- $assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
- if ($assemblyInfo.GetName().Version.Major -ge 10)
- {
- # sql server version is 2008 or later, also load these other assemblies
- [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
- [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
- }
- $dt = get-date -format yyyyMMddHHmmss
- Write-Host "$dt - Connecting to Database Server $ServerName"
- if (get-serveronline($ServerName))
- {
- $s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $DatabaseServer
- $dbs = $s.Databases
- foreach ($db in $dbs)
- {
- if($db.Name -ne “tempdb”) # We don’t want to backup the tempdb database
- {
- $DatabaseName = $db.Name
- if (!(Test-Path -path $BackupPath\$DatabaseName\))
- {
- $dt = get-date -format yyyyMMddHHmmss
- Write-Host "$dt - Creating the Backup Directory for $DatabaseName"
- New-Item $BackupPath\$DatabaseName\ -type directory | Out-Null
- }
- $dt = get-date -format yyyyMMddHHmmss
- Write-Host "$dt - Starting the Backup of the Database $DatabaseName"
- $dt = get-date -format yyyyMMddHHmm # We use this to make a file name based on the timestamp
- $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
- $dbBackup.Action = “Database”
- $dbBackup.Database = $DatabaseName
- $dbBackup.Devices.AddDevice($BackupPath +"\" + $DatabaseName + “\” + $DatabaseName + “_Database_” + $dt + “.bak”, “File”)
- $dbBackup.SqlBackup($s)
- }
- }
- $dt = get-date -format yyyyMMddHHmmss
- Write-Host "$dt - Database Backup Complete"
- }
Advertisement
Add Comment
Please, Sign In to add comment