PoshCode Logo PowerShell Code Repository

LibrarySqlBackup (modification of post by view diff)
View followups from Richard Vantreas | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1188"></script>download | new post

Standalone script adapted from SQL Server Powershell Extensions (sqlpsx) http://sqlpsx.codeplex.com. Defines SQL Server backup and restore functions.

  1. # ---------------------------------------------------------------------------
  2. ### <Script>
  3. ### <Author>
  4. ### Chad Miller
  5. ### </Author>
  6. ### <Description>
  7. ### Excerpt from SQL Server Powershell Extensions (sqlpsx)
  8. ### http://sqlpsx.codeplex.com
  9. ### Defines backup and restore functions
  10. ### </Description>
  11. ### <Usage>
  12. ### . ./LibrarySqlBackup.ps1
  13. ### $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") 'Z002\SQL2K8'
  14. ### invoke-sqlbackup 'Z002\SqlExpress' 'pubs' $($server.BackupDirectory + "\pubs.bak")
  15. ### invoke-sqlrestore 'Z002\SqlExpress' 'pubs' $($server.BackupDirectory + "\pubs.bak") -force
  16. ### </Usage>
  17. ### </Script>
  18. # ---------------------------------------------------------------------------
  19. $smoAssembly = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
  20. if (!($smoVersion))
  21. { Set-Variable -name SmoVersion  -value $smoAssembly.GetName().Version.Major -Scope Global -Option Constant -Description "SQLPSX variable" }
  22. [void][reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')
  23.  
  24. #######################
  25. function Invoke-SqlBackup
  26. {
  27.     param($sqlserver=$(throw 'sqlserver required.'),$dbname=$(throw 'dbname required.'),$filepath=$(throw 'filepath required.')
  28.           ,$action='Database', $description='',$name='',[switch]$force,[switch]$incremental,[switch]$copyOnly)
  29.    
  30.     #action can be Database or Log
  31.  
  32.     $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
  33.  
  34.     Write-Verbose "Invoke-SqlBackup $($server.Name) $dbname"
  35.  
  36.     $backup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
  37.     $backupDevice = new-object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") $filepath, 'File'
  38.  
  39.     $backup.Action = $action
  40.     $backup.BackupSetDescription = $description
  41.     $backup.BackupSetName = $name
  42.     if (!$server.Databases.Contains("$dbname")) {throw 'Database $dbname does not exist on $($server.Name).'}
  43.     $backup.Database = $dbname
  44.     $backup.Devices.Add($backupDevice)
  45.     $backup.Initialize = $($force.IsPresent)
  46.     $backup.Incremental = $($incremental.IsPresent)
  47.     if ($copyOnly)
  48.     { if ($server.Information.Version.Major -ge 9 -and $smoVersion -ge 10)
  49.       { $backup.CopyOnly = $true }
  50.       else
  51.       { throw 'CopyOnly is supported in SQL Server 2005(9.0) or higher with SMO version 10.0 or higher.' }
  52.     }
  53.    
  54.     trap {
  55.         $ex = $_.Exception
  56.         Write-Output $ex.message
  57.         $ex = $ex.InnerException
  58.         while ($ex.InnerException)
  59.         {
  60.             Write-Output $ex.InnerException.message
  61.             $ex = $ex.InnerException
  62.         };
  63.         continue
  64.     }
  65.     $backup.SqlBackup($server)
  66.    
  67.     if ($?)
  68.     { Write-Host "$action backup of $dbname to $filepath complete." }
  69.     else
  70.     { Write-Host "$action backup of $dbname to $filepath failed." }
  71.  
  72. } #Invoke-SqlBackup
  73.  
  74. #######################
  75. function Invoke-SqlRestore
  76. {
  77.     param($sqlserver=$(throw 'sqlserver required.'),$dbname=$(throw 'dbname required.'),$filepath=$(throw 'filepath required.'),
  78.           $action='Database',$stopat,$relocatefiles,[switch]$force,[switch]$norecovery,[switch]$keepreplication)
  79.  
  80.     #action can be Database or Log
  81.  
  82.     $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
  83.  
  84.     Write-Verbose "Invoke-SqlRestore $($server.Name) $dbname"
  85.  
  86.     $restore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")
  87.     $restoreDevice = new-object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") $filepath, 'File'
  88.  
  89.     $restore.Action = $action
  90.     $restore.Database = $dbname
  91.     $restore.Devices.Add($restoreDevice)
  92.     $restore.ReplaceDatabase = $($force.IsPresent)
  93.     $restore.NoRecovery = $($norecovery.IsPresent)
  94.     $restore.KeepReplication = $($keepreplication.IsPresent)
  95.    
  96.     if ($stopat)
  97.     { $restore.ToPointInTime = $stopAt }
  98.  
  99.     if ($relocatefiles)
  100.     {
  101.        if ($relocateFiles.GetType().Name -ne 'Hashtable')
  102.        { throw 'Invoke-SqlRestore:Param relocateFile must be a hashtable' }
  103.  
  104.        $relocateFileAR = New-Object Collections.ArrayList
  105.        
  106.        foreach ($i in $relocatefiles.GetEnumerator())
  107.         {
  108.             $logicalName = $($i.Key); $physicalName = $($i.Value);
  109.             $relocateFile = new-object ("Microsoft.SqlServer.Management.Smo.RelocateFile") $logicalName, $physicalName
  110.             [void]$relocateFileAR.Add($relocateFile)
  111.         }
  112.  
  113.         $restore.RelocateFiles = $relocateFileAR
  114.      
  115.     }
  116.  
  117.     trap {
  118.         $ex = $_.Exception
  119.         Write-Output $ex.message
  120.         $ex = $ex.InnerException
  121.         while ($ex.InnerException)
  122.         {
  123.             Write-Output $ex.InnerException.message
  124.             $ex = $ex.InnerException
  125.         };
  126.         continue
  127.     }
  128.     $restore.SqlRestore($server)
  129.    
  130.     if ($?)
  131.     { Write-Host "$action restore of $dbname from $filepath complete." }
  132.     else
  133.     { Write-Host "$action restore of $dbname from $filepath failed." }
  134.  
  135. } #Invoke-SqlRestore
  136.  
  137. #######################

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me