SQLServer脚本一键备份与还原(可配置文件及多通配符支持...)-CSDN博客

本文链接：https://blog.csdn.net/intrejj/article/details/83938966

需求：

需要数据库中的对象(eg: Table 、 Trigger、 View 、Function、 StoredProcedure 等)进行迁移到同一数据库服务器或远程数据库服务器，且要求支持迁移时改名，及迁移时可以对某些对象改名。

思路：

利用SMO可以操作数据库对象进行数据库备份与还原。且有两种方式：一种是数据库脚本的形式(.sql) ；一种是整个数据库（.bak）的操作形式，因为第二种(.bak)是对整个数据库的操作，不能够有选择的备份，达不到我的要求，故这种方式我没找到解决办法。（或许本人没找到）,因此这里记录的是本人通过第一种形式（.sql）的实现。因为他能够针对你指定的数据库对象来备份数据.而且还能够匹配到你指定的通配符来操作数据库对象。而且也能够进行文本替换来改名。缺点就是数据库对象之间的关系难以确定.因为假如你备份视图，但表不存在，这时就会有问题.

因为是一键对数据库进行备份与还原.所有这里有三个文件.

1) 批处理文件:run.bat

cls
powershell -command "&{set-executionpolicy RemoteSigned }"
powershell -command "&{.\beta.ps1 Club.Secretary}"

第一句是清屏

第二句：因为PowerShell在计算机上运行的权限不同，这里将执行权限设为RemoteSigned,其它权限参考如下：

 
          1 
          )Restricted - No scripts can be run. Windows PowerShell can be used only in interactive mode. 
         
          2 
          )AllSigned - Only scripts signed by a trusted publisher can be run. 
         
          3 
          )RemoteSigned - Downloaded scripts must be signed by a trusted publisher before they can be run. 
         
          4 
          )Unrestricted - No restrictions; all Windows PowerShell scripts can be run.

第三句是执行同目录下的beta.ps1文件。

2)配置文件: config.xml

<?xml version="1.0" encoding="UTF-8"?>
<config model="0"> <!--  Model=1  directory run ;  Model=2   communicate model-->  
	<backup>
		<ServerName>localhost</ServerName>
		<UserName>sa</UserName>	
		<PassWord>123</PassWord>
		<DataBase>UserDB</DataBase>
		<BackupObject>5</BackupObject>
		<!--  All Objects = 0; Tables = 1; Table Triggers = 2; Views = 3; Functions = 4;Stored Procedures = 5;     -->
		<WildCard>customer_cal*</WildCard> 
		<oldChar>axdev.dbo.</oldChar>
		<newChar>ax18.dbo.</newChar>
		<BackupFolder>d:\backup</BackupFolder>
	</backup>
	<restore>
	    <whether>0</whether> <!-- whether restore to database -->
		<ServerName>10.1.1.115</ServerName>
		<UserName>sa</UserName>
		<PassWord>123</PassWord>
		<DataBase>backupTest</DataBase>
	</restore>
</config>

配置信息为如下：

备份的数据库服务器的相关信息: 
   服务器(ServerName)、
   登录用户(UserName)、
   登录密码(PassWord)、
   要备份的数据库(DataBase)、
   备份对象(BackupObject)、
   通配符(WildCard)、
   要替代的字符(oldChar)、
   用来替代的字符(newChar)、
   备份文件存放路径(BackupFolder)
还原的数据库服务器相关信息：
   是否要還原(Whether)、
   服务器(ServerName)、
   登录用户(UserName)、
   登录密码(PassWord)、
   要还原的数据库(DataBase)、

3) 以下是beta.ps1的程序代码及说明：

(得到相关信息后开始,开始进行脚本备份，之后替换脚本 ,最后在还原数据库上执行脚本.)

[String]$CurrentPath = get-location;   #得到当前路径
[String]$xmlDocPath = $CurrentPath+"\config.xml";  #获得config.xml配置文件路径.
#write-host $xmlDocPath    #输出(Debug已注释掉)
$xmlDoc = New-Object "System.Xml.XmlDocument"     #创建一个xml文档对象
$xmlDoc.Load($xmlDocPath)     #加载并读取文档对象(config.xml)中的配置
$modelList = $xmlDoc.GetElementsByTagName("config");#得到<config>节点
$model=$modelList.ItemOf(0).GetAttribute("model")    #得到第一个<config>的属性model的值
#write-host $model  #输出(Debug已注释掉)
 
if($model -eq 0 )   #如果model属性值等于0
{
  #--------------------------------Start Param-----------------------------------------------------------
  $backupNodelist=$xmlDoc.GetElementsByTagName("backup");
  $backupConfig=$backupNodelist.ItemOf(0);   #得到第一个<backup>
 
  $backupServer=$backupConfig.GetElementsByTagName("ServerName").ItemOf(0).get_InnerXml();     #得到第一个<backup>中<ServerName>的值
  $backupLogin=$backupConfig.GetElementsByTagName("UserName").ItemOf(0).get_InnerXml();        #得到第一个<backup>中<UserName>的值
  $backupPassWord=$backupConfig.GetElementsByTagName("PassWord").ItemOf(0).get_InnerXml();     #得到第一个<backup>中<PassWord>的值
  $backupDataBase=$backupConfig.GetElementsByTagName("DataBase").ItemOf(0).get_InnerXml();     #得到第一个<backup>中<DataBase>的值
  $backupObject=$backupConfig.GetElementsByTagName("BackupObject").ItemOf(0).get_InnerXml();   #得到第一个<backup>中<BackupObject>的值
  $backupWildCard=$backupConfig.GetElementsByTagName("WildCard").ItemOf(0).get_InnerXml();     #得到第一个<backup>中<WildCard>的值
  $oldChar=$backupConfig.GetElementsByTagName("oldChar").ItemOf(0).get_InnerXml();     #得到第一个<backup>中<oldChar>的值
  $newChar=$backupConfig.GetElementsByTagName("newChar").ItemOf(0).get_InnerXml();     #得到第一个<backup>中<newChar>的值
  $backupFolder=$backupConfig.GetElementsByTagName("BackupFolder").ItemOf(0).get_InnerXml();   #得到第一个<backup>中<BackupFolder>的值
 
  $restoreNodelist=$xmlDoc.GetElementsByTagName("restore");
  $restoreConfig=$restoreNodelist.ItemOf(0);
  $whether=$restoreConfig.GetElementsByTagName("whether").ItemOf(0).get_InnerXml();            #得到第一個<restore>中<whether>的值
  $restoreServer=$restoreConfig.GetElementsByTagName("ServerName").ItemOf(0).get_InnerXml();   #得到第一个<restore>中<ServerName>的值
  $restoreLogin=$restoreConfig.GetElementsByTagName("UserName").ItemOf(0).get_InnerXml();      #得到第一个<restore>中<UserName>的值
  $restorePassWord=$restoreConfig.GetElementsByTagName("PassWord").ItemOf(0).get_InnerXml();   #得到第一个<restore>中<PassWord>的值
  $restoreDataBase=$restoreConfig.GetElementsByTagName("DataBase").ItemOf(0).get_InnerXml();   #得到第一个<restore>中<DataBase>的值
 
 #---------------------------------End Param------------------------------------------------------------------
  #加载所需包
  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   #采用SQL Server 身份验证,非Window 身份验证
  $serverConn.Login = $backupLogin    #登录用户名
  $serverConn.Password =$backupPassWord   #登录密码
  $user_folder=$backupFolder         #备份目录
 
  #trap errors
  $errors =$user_folder+"\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;
    "`user_folder = $user_folder" | 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
        }
    }
  }
  MakeDirectory ($user_folder)    #创建目录路径$user_folder
  $srv = new-object Microsoft.SqlServer.Management.Smo.Server($serverConn)   #创建Microsoft.SqlServer.Management.Smo.Server对象
  $db=$srv.databases[$backupDataBase]  #指定的数据库对象
  $dbName=$db.Name   #数据库名字
  #write-host "Current Database:"$dbName
 
  $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"   #脚本创建对象
  $scr.Server = $srv
  $scrOptions = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"   #脚本创建对象的选择(Options)
 
  $scrOptions.AllowSystemObjects = $false #不需要数据库中的系统对象，如系统中表、视图等
  $scrOptions.IncludeDatabaseContext = $false #buyaodababase
  $scrOptions.IncludeIfNotExists = $true     #包含IfNotExists语句
  $scrOptions.ClusteredIndexes = $true
  $scrOptions.NoCollation = $true
  $scrOptions.Default = $true
  #$scrOptions.DriAll = $true
  $scrOptions.Indexes = $true
  $scrOptions.NonClusteredIndexes = $true
  $scrOptions.IncludeHeaders = $true
  $scrOptions.ToFileOnly = $true    #只写入文件
  $scrOptions.Permissions = $true
  $scrOptions.ScriptDataCompression = $true  #压缩
 
  $scrOptions.ScriptSchema = $true
  $scrOptions.AppendToFile = $true   #追加到文件中，而不是覆盖.
  $sEnc=[System.Text.Encoding]::UTF8  #設置生成腳本編碼方式(注意)
  $scrOptions.Encoding=$sEnc
    
  #Set options for SMO.Scripter
  $scr.Options = $scrOptions
 
  $backupfolder=$user_folder+"\"+$dbName+"_backup"    #备份的目录
  MakeDirectory ($backupfolder)   #创建备份的目录
  remove-item $backupfolder\*     #每次备份之前会先删除备份目录中的所有文件
 
  $backupfile=$backupfolder+"\"+$dbName+".backup.sql"; #脚本备份到目录中的某个文件中.
  
  $WildCard="";   
  function getWildCard
  {       
      param($obj)    
      $WildCard="";    
      #write-host $obj    
      foreach($element in $backupWildCard.split(';'))    
      {   
            $WildCard+="'"+$obj+"' -like '"+$element+"' -or "    
      }    
      $WildCard=$WildCard.substring(0,$WildCard.length-5)    
      invoke-expression $WildCard   
  }
 
 
  ">>>>>>>>>>>>Start backup"
  function ScriptDropStatement
  {
     param($object)
     $scrOptions.ScriptDrops = $true  # drop statement
     $scrOptions.FileName = $backupfile;
     $scr.Options = $scrOptions;
     $scr.Script($object);
  }
  function ScriptCreateStatement
  {
     param($object)
     $scrOptions.ScriptDrops = $false  #create statement
     $scrOptions.FileName = $backupfile;
     $scr.Options = $scrOptions;
     $scr.Script($object);
  }
  $tables=$db.Tables;
  if ($backupObject -eq 0 -or $backupObject -eq 1)          #如果備份表腳本
  {     
        $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table],"IsSystemObject")   #讓server對象實例化時為Table對象
    foreach ($table in $tables | where-object {getWildCard($_.name)} | where-object{$_.IsSystemObject -eq $false})   #用通配符過濾對象且不為系統對象
    {   
        #write-host $table.Name
        MakeDirectory ($backupfolder); #Check for folder, and create if needed
        #ScriptDropStatement($table)
        ScriptCreateStatement($table)
            write-host "Table "$table.Name" backup Complete !"
    }
  }
  if ($backupObject -eq 0 -or $backupObject -eq 2)         #如果備份Trigger腳本
  {
    # Script table triggers (go into tables then triggers)
    $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Trigger],"IsSystemObject")    #讓server對象實例化時為Trigger對象
    foreach ($table in $tables)
    {
        foreach ($trigger in $Table.Triggers |where-object {getWildCard($_.name)} | where-object{$_.IsSystemObject -eq $false})  #用通配符過濾對象且不為系統對象
        {
        MakeDirectory ($backupfolder); #Check for folder, and create if needed
        ScriptDropStatement($trigger)
            ScriptCreateStatement($trigger)
                write-host "Trigger "$trigger.Name" backup Complete !"
        }
    }
  }
  if ($backupObject -eq 0 -or $backupObject -eq 3)       #如果備份View腳本
  {
    # script each view
    $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View],"IsSystemObject")       #讓server對象實例化時為View對象
    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 ($backupObject -eq 0 -or $backupObject -eq 4)       #如果備份Function腳本
  {
     # script each function
     $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction],"IsSystemObject")        #讓server對象實例化時為Function對象
     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 ($backupObject -eq 0 -or $backupObject -eq 5)         #如果備份StoredProcedure腳本
  {
    # script each stored procedure
    $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure],"IsSystemObject")    #讓server對象實例化時為StoredProcedure對象
    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 !"
    }
  } 
  ">>>>>>>>>>>>All Backup Complete"
 
  if($whether -eq 1){ #如果配置需要還原
      $file=Get-ChildItem $backupfile;
      #write-host ($oldChar -eq "" -and $newChar -eq "")
      if($oldChar -eq "" -and $newChar -eq "") #如果沒有替換
      {
      #write-host "No replace necearray !"
      }else{
      foreach($str in $file)
      {
         $content=Get-Content -path $str -Encoding UTF8                  #取文件內容(注意編碼)
         $content | foreach-object {$_ -replace $oldChar,$newChar} | Set-Content $str -Encoding UTF8           #替換文件內容并設置到文件內.(注意編碼)
      }
      }
      ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
      foreach($f in get-childitem -path $backupfolder -Filter *.sql | sort-object)
      {
      sqlcmd -i $f.fullname -S $restoreServer -U $restoreLogin -P $restorePassWord  -d $restoreDataBase        #開始還原到（遠程）數據庫上
      }
  }else{ #不還原
      #write-host 'No Restore !'
  }
}