SQLServer脚本一键备份与复原(可配置文件及多通配符支持.)

需求：

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

?

思路：

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

?

?

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

?

1) 批处理文件:run.bat

?

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

第一句是清屏

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

第三句是执行同目录下的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 !'  }}