PowerShell 數據庫備份與還原(帶替換)

同一目錄下config.xml:

 

<?xml version="1.0" encoding="UTF-8"?>
<config index="0">  
	<backup>
		<whether>0</whether> <!-- wheter download -->
		<ServerName>10.1.100.13</ServerName>
		<UserName>test</UserName>	
		<PassWord>test123</PassWord>
		<DataBase>BarCode09</DataBase>
		<BackupObject>2;5</BackupObject><!--     All Objects = 0; Tables = 1; Table Triggers = 2; Views = 3; Functions = 4; Stored Procedures = 5;     -->
		<WildCard>el_mp*;el_get_date</WildCard>		
	</backup>
	
	<replace>
	    <BackupFolder>D:\sp\masterplan\backup</BackupFolder>
	    <whether>1</whether> <!--whether  replace -->
		<newChar>live.dbo.</newChar>
		<oldChar>uat18.dbo.</oldChar>
		<ReplaceFolder>D:\sp\masterplan\replace</ReplaceFolder>
	</replace>
	
	<restore>
	    <whether>0</whether> <!--whether upload/restore -->
		<ServerName>10.1.100.58</ServerName>
		<UserName>sa</UserName>
		<PassWord>sa123</PassWord>
		<DataBase>stu</DataBase>
	</restore>
</config>

PowerShell腳本如下:

[String]$CurrentPath = get-location;
[String]$xmlDocPath = $CurrentPath+"\config.xml";
#write-host $xmlDocPath  
$xmlDoc = New-Object "System.Xml.XmlDocument"  
$xmlDoc.Load($xmlDocPath)  
$modelList = $xmlDoc.GetElementsByTagName("config");#tagName  
$index=$modelList.ItemOf(0).GetAttribute("index")
if($index -eq 0 )
{
  #-------------------Start Param-----------------
  $backupNodelist=$xmlDoc.GetElementsByTagName("backup");
  $backupConfig=$backupNodelist.ItemOf(0);   #first <backup> node
  #below is backup tag attributes
  $whetherBackup=$backupConfig.GetElementsByTagName("whether").ItemOf(0).get_InnerXml();  
  $backupServer=$backupConfig.GetElementsByTagName("ServerName").ItemOf(0).get_InnerXml();  
  $backupLogin=$backupConfig.GetElementsByTagName("UserName").ItemOf(0).get_InnerXml();
  $backupPassWord=$backupConfig.GetElementsByTagName("PassWord").ItemOf(0).get_InnerXml();
  $backupDataBase=$backupConfig.GetElementsByTagName("DataBase").ItemOf(0).get_InnerXml();
  $backupObject=$backupConfig.GetElementsByTagName("BackupObject").ItemOf(0).get_InnerXml();
  $backupWildCard=$backupConfig.GetElementsByTagName("WildCard").ItemOf(0).get_InnerXml();
  
  $replaceNodelist=$xmlDoc.GetElementsByTagName("replace");
  $replaceConfig=$replaceNodelist.ItemOf(0);   #first <replace> node
  #below is replace tag attributes
  $backupFolder=$replaceConfig.GetElementsByTagName("BackupFolder").ItemOf(0).get_InnerXml();
  $whetherReplace=$replaceConfig.GetElementsByTagName("whether").ItemOf(0).get_InnerXml();
  $newChar=$replaceConfig.GetElementsByTagName("newChar").ItemOf(0).get_InnerXml();
  $oldChar=$replaceConfig.GetElementsByTagName("oldChar").ItemOf(0).get_InnerXml();  
  $replaceFolder=$replaceConfig.GetElementsByTagName("ReplaceFolder").ItemOf(0).get_InnerXml();
  
  $restoreNodelist=$xmlDoc.GetElementsByTagName("restore");
  $restoreConfig=$restoreNodelist.ItemOf(0);   #first <restore> node
  #below is restore tag attributes
  $whetherRestore=$restoreConfig.GetElementsByTagName("whether").ItemOf(0).get_InnerXml();
  $restoreServer=$restoreConfig.GetElementsByTagName("ServerName").ItemOf(0).get_InnerXml();
  $restoreLogin=$restoreConfig.GetElementsByTagName("UserName").ItemOf(0).get_InnerXml();
  $restorePassWord=$restoreConfig.GetElementsByTagName("PassWord").ItemOf(0).get_InnerXml();
  $restoreDataBase=$restoreConfig.GetElementsByTagName("DataBase").ItemOf(0).get_InnerXml();
   
 #-------------------End Param----------------------
  #Load Class
  #cls
  #load assemblies
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  #Need SmoExtended for backup
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
  [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
  [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
 
  $serverConn=new-object Microsoft.SqlServer.Management.Common.ServerConnection
  $serverConn.ServerInstance=$backupServer
  $serverConn.LoginSecure =$false
  $serverConn.Login = $backupLogin
  $serverConn.Password =$backupPassWord

  #trap errors(catch error )
  $errors =$backupFolder+"\errors.txt"
  trap
  {

	"______________________" | out-file $errors -append;
	"ERROR SCRIPTING TABLES" | out-file $errors -append;
	get-date | out-file $errors -append;
	"ERROR: " + $_ | out-file $errors -append;
	"`backupServer = $backupServer" | out-file $errors -append;
	"`backupDataBae = $backupDataBase" |out-file $errors -append;
	"`backupFolder = $backupFolder" | out-file $errors -append;
	"`restoreServer = $restoreServer" | out-file $errors -append;
	"`restoreDataBae = $restoreDataBase " | out-file $errors -append;
	#throw "ERROR: See $errors"
  }
  
  function MakeDirectory 
  {
	param([string]$DirName)
	Process
	{
		if (!(Test-Path -path $DirName))
		{
		  New-Item $DirName -type directory | Out-Null
		}
	}
  }
  $srv = new-object Microsoft.SqlServer.Management.Smo.Server($serverConn)
  $db=$srv.databases[$backupDataBase]
  $dbName=$db.Name

  $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
  $scr.Server = $srv
  $scrOptions = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"

  #Script Options Setting
  $scrOptions.AllowSystemObjects = $false #not include system object
  $scrOptions.IncludeDatabaseContext = $false #not include database infomation
  $scrOptions.IncludeIfNotExists = $true
  $scrOptions.ClusteredIndexes = $true
  $scrOptions.NoCollation = $true
  $scrOptions.Default = $true
  #$scrOptions.DriAll = $true
  $scrOptions.Indexes = $true
  $scrOptions.NonClusteredIndexes = $true
  $scrOptions.IncludeHeaders = $false
  $scrOptions.ToFileOnly = $true
  $scrOptions.Permissions = $true
  $scrOptions.ScriptDataCompression = $true
  $scrOptions.ScriptSchema = $true
  $scrOptions.AppendToFile = $true
  $sEnc = [System.Text.Encoding]::UTF8
  $scrOptions.Encoding=$sEnc
  
  $scr.Options = $scrOptions 

  if($whetherBackup -eq 1){
	if (Test-Path -path $backupFolder){
	    remove-item $backupFolder\*.* -Recurse
	}
  }
  if($whetherReplace -eq 1){#remove replaceFolder before replace
	if (Test-Path -path $replaceFolder){
		remove-item $replaceFolder\*.* -Recurse
	}
  }

  $WildCard="";
  function getWildCard{
      param($obj)
	  $WildCard="";
	  foreach($element in $backupWildCard.split(';'))
	  {
		 $WildCard+="'"+$obj+"' -like '"+$element+"' -or "
	  }
	  $WildCard=$WildCard.substring(0,$WildCard.length-5)
	  invoke-expression $WildCard
  }
  function ScriptDropStatement
  {
    param($object)
    $scrOptions.ScriptDrops = $true  # drop statement of sql
	MakeDirectory($backupFolder);
	$scrOptions.FileName = $backupFolder+"\"+$object.Name+".sql";
	$scr.Options = $scrOptions;
	$scr.Script($object);
  }
  function ScriptCreateStatement
  {
    param($object)
    $scrOptions.ScriptDrops = $false  #create statement for sql
	MakeDirectory($backupFolder);
	$scrOptions.FileName = $backupFolder+"\"+$object.Name+".sql";
	$scr.Options = $scrOptions;
	$scr.Script($object);
  }
  if($whetherBackup -eq 1){ #If want to backup
    ">>>>>Start Backup[Download] the Database Script !!!"
    $multiObject = $backupObject.split(";");
	foreach($obj in $multiObject)
	{     
	     $tables=$db.Tables;
	     if ($obj -eq 0 -or $obj -eq 1)
	     {     
		 $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table],"IsSystemObject")
		 foreach ($table in $tables | where-object {getWildCard($_.name)} | where-object{$_.IsSystemObject -eq $false})
		 {   
			MakeDirectory ($backupFolder); 
			#ScriptDropStatement($table)
			ScriptCreateStatement($table)
			write-host "Table "$table.Name" backup Complete !"
		 }
	     }
            if ($obj -eq 0 -or $obj -eq 2)
	    {
		$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Trigger],"IsSystemObject")
		foreach ($table in $tables)
		{
			foreach ($trigger in $Table.Triggers |where-object {getWildCard($_.name)} | where-object{$_.IsSystemObject -eq $false})
			{
				MakeDirectory ($backupFolder); 
				ScriptDropStatement($trigger)
				ScriptCreateStatement($trigger)
				write-host "Trigger "$trigger.Name" backup Complete !"
			}
		}
	   }
	   if ($obj -eq 0 -or $obj -eq 3)
	   {
		$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View],"IsSystemObject")
		foreach ($view in $db.Views | where-object { getWildCard($_.name)} | where-object {$_.IsSystemObject -eq $false} )
		{
			MakeDirectory ($backupFolder); #Check for folder, and create if needed
			ScriptDropStatement($view)
			ScriptCreateStatement($view)
			write-host "View "$view.Name" backup Complete !"
		}
	  }
	  if ($obj -eq 0 -or $obj -eq 4)
	  {
		$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction],"IsSystemObject")
		foreach ($function in $db.UserDefinedFunctions | where-object {getWildCard($_.name)} | where-object {$_.IsSystemObject -eq $false})
		{
			MakeDirectory ($backupFolder); #Check for folder, and create if needed
			ScriptDropStatement($function)
			ScriptCreateStatement($function)
			write-host "Function "$function.Name" backup Complete !"
		}
	  }
	  if ($obj -eq 0 -or $obj -eq 5)
	  {
		$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure],"IsSystemObject")
		foreach ($procedure in $db.StoredProcedures | where-object {getWildCard($_.name)} | where-object{$_.IsSystemObject -eq $false})
		{
			MakeDirectory ($backupFolder); #Check for folder, and create if needed
			ScriptDropStatement($procedure)
			ScriptCreateStatement($procedure)
			write-host "Stored Procedure "$procedure.Name" backup Complete !"
		}
	  }
	}
	">>>>>>>>Backup[Download] the Database Script Complete !!!"
  }
  if($whetherReplace -eq 1){#If want to replace 
    ">>>>>>>>Start Replace The BackupFolder's Script  !"
	if(!(Test-Path -path $backupFolder)){
	  write-host "Information: "$backupFolder" Directory not exist! Please Backup Before !"
	}else{
	      MakeDirectory($replaceFolder);#Check replaceFolder whether exist!
	      $file=Get-ChildItem $backupFolder;
	      if($oldChar -eq "" -and $newChar -eq "")
	      {
		   write-host "No replace necearray !"
	      }else{
		    foreach($str in $file)
		   { 
			$backfileAddr=$backupFolder+"\"+$str;			
			if(Test-Path -path $backfileAddr){
				$replacefileAddr=$replaceFolder+"\"+$str;
				write-host $replacefileAddr;
				$content=Get-Content -path $backfileAddr -Encoding UTF8
				$content | foreach-object {$_ -replace $oldChar,$newChar} | Set-Content $replacefileAddr -force -Encoding UTF8	
			}else{
				write-host "file "$backfileAddr" can't found! So the replace Action can't work !"
			}				
		  }
             }
	}
    ">>>>>>Replace Script Complete !"
  }
  if($whetherRestore -eq 1){#If want to restore
    ">>>>>>>Start Restore Database  !"
	if(!(Test-Path -path $replaceFolder)){
	  write-host "Information: "$replaceFolder "Directory not exist !please replace Before"
	}else{
	  foreach($f in get-childitem -path $replaceFolder -Filter *.sql | sort-object)
	  {   
		  sqlcmd -i $f.fullname -S $restoreServer -U $restoreLogin -P $restorePassWord  -d $restoreDataBase
	  }
	}  
	">>>>>>>Restore DataBase Complete !"
  }
  if($whetherBackup -eq 0 -and $whetherReplace -eq 0 -and $whetherRestore -eq 0){
    ">>>>>>You don't let progress do anything !<<<<<<"
  }
}

同一目錄下的批處理文件run.bat:

cls
powershell -command "&{set-executionpolicy RemoteSigned }"

powershell -command "&{.\beta.ps1 Club.Secretary}"