McBadass

[Powershell] Backup all databases in a MSSQL instance

Nov 16th, 2012
254
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. $BackupPath = "C:\SQLServer\Backups"
  2. $ServerName = "Localhost"
  3. $DatabaseServer = "localhost\SP13"
  4.  
  5. # little helper function to ping the remote server.
  6. function Get-ServerOnline ([string] $server)
  7. {
  8.     $serverStatus = (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
  9.     $dt = get-date -format yyyyMMddHHmmss
  10.     Write-Host "$dt - Server Online: $serverStatus"
  11.     return $serverStatus
  12. }
  13.  
  14. # Load the SQL Server SMO Libraries
  15. $assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
  16. if ($assemblyInfo.GetName().Version.Major -ge 10)
  17. {
  18.     # sql server version is 2008 or later, also load these other assemblies
  19.     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
  20.     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
  21. }
  22.  
  23. $dt = get-date -format yyyyMMddHHmmss
  24. Write-Host "$dt - Connecting to Database Server $ServerName"
  25.  
  26. if (get-serveronline($ServerName))
  27. {
  28.     $s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $DatabaseServer
  29.  
  30.     $dbs = $s.Databases
  31.  
  32.     foreach ($db in $dbs)
  33.     {
  34.         if($db.Name -ne “tempdb”) # We don’t want to backup the tempdb database
  35.         {      
  36.             $DatabaseName = $db.Name
  37.            
  38.             if (!(Test-Path -path $BackupPath\$DatabaseName\))
  39.             {
  40.                 $dt = get-date -format yyyyMMddHHmmss
  41.                 Write-Host "$dt - Creating the Backup Directory for $DatabaseName"
  42.                 New-Item $BackupPath\$DatabaseName\ -type directory | Out-Null
  43.             }
  44.            
  45.             $dt = get-date -format yyyyMMddHHmmss
  46.             Write-Host "$dt - Starting the Backup of the Database $DatabaseName"
  47.            
  48.             $dt = get-date -format yyyyMMddHHmm # We use this to make a file name based on the timestamp
  49.             $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
  50.             $dbBackup.Action = “Database”
  51.             $dbBackup.Database = $DatabaseName
  52.             $dbBackup.Devices.AddDevice($BackupPath +"\" + $DatabaseName + “\” + $DatabaseName + “_Database_” + $dt + “.bak”, “File”)
  53.             $dbBackup.SqlBackup($s)
  54.         }
  55.     }
  56.    
  57.     $dt = get-date -format yyyyMMddHHmmss
  58.     Write-Host "$dt - Database Backup Complete"
  59. }
Advertisement
Add Comment
Please, Sign In to add comment