`

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

阅读更多

需求:

   需要数据库中的对象(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 !'
  }
}
  • DB_Backup.rar (3.1 KB)
  • 描述: 将此文件解压到一个文件夹中,通过配置config.xml,双击run.bat即可运行!
  • 下载次数: 15
分享到:
评论

相关推荐

    服务器SQL Server 自动定时备份和还原的工具

    本工具是自己开发的,用于SQL Server 数据库的备份与还原用,有了本工具,设定好每一个数据库的备份时间,可以按年、月、日、时、分设置好后,到时间自动备份,工具还有备份数据为多少天前的可以自动删除,同时也有...

    SqlServer数据库的备份与还原

    上述操作涵盖了SqlServer数据库备份与还原的基本知识点,包括手动备份的步骤、自动备份的配置、还原操作流程及常见问题。掌握这些知识点对于任何SqlServer数据库管理员来说都是至关重要的。在实际工作中,根据不同的...

    SQLServer数据库的备份和还原详解

    ### SQL Server 数据库的备份与还原详解 #### 一、基本概念 在开始讨论SQL Server数据库的备份与还原之前,我们需要了解几个基本的概念。 - **MDF 文件**: 这是SQL Server数据库的主要数据文件,存储了数据库的...

    SQL Server 2000一键10秒极速安装.part1

    原理:其实只是提取了SQL server 2000安装后写入硬盘的文件及注册表信息等,所以只有83M, 比原来的安装包几百M小了非常多,但大家不要怀疑,这些绝对是完整的所有文件了。 将提取的文件制成了这个后缀为exe的自...

    C# 备份,还原sqlserver,SQL server还原,附加,分离一键操作 数据备份 数据还原

    对于SQL Server这样的大型关系型数据库管理系统,定期备份、还原以及附加和分离数据库是常见的维护操作。本项目聚焦于提供一个C#编写的工具,实现了对SQL Server数据库的自动化处理,使得这些繁琐的任务变得简单高效...

    将Sql Server 2000中的数据库备份文件还原到sql2005中

    将Sql Server 2000中的数据库备份文件还原到sql2005中 将Sql Server 2000中的数据库备份文件还原到Sql Server 2005中是一个常见的问题。在这个过程中,我们需要了解Sql Server 2000和Sql Server 2005之间的差异,...

    SQLServer2008R2数据库备份与还原操作文档.pdf

    SQLServer2008R2数据库备份与还原操作文档.pdf

    SQL SERVER 2008, 一键卸载工具

    一键卸载工具会清理与SQL Server 2008相关的注册表键值,防止卸载后遗留问题。 4. **删除程序文件**:工具会定位并删除SQL Server 2008的安装目录及其子目录中的所有文件,彻底移除程序组件。 5. **还原系统设置**...

    SQL SERVER 2000 数据库备份与还原

    在SQL Server 2000中,数据库的备份与还原是数据库管理的重要组成部分,它确保了数据的安全性和可恢复性。下面将详细讲解这个主题。 首先,我们需要理解数据库备份的基本概念。备份是复制数据库或其一部分到另一个...

    sqlserver2008远程备份、还原和压缩日志文件

    本篇将深入探讨如何在VS2010环境下,通过编程方式实现SQL Server 2008的远程备份、还原以及日志文件的压缩。 首先,我们来讨论SQL Server 2008的远程备份。在VS2010中,可以使用SQL Server Management Objects (SMO...

    Sqlserver2005数据库还原备份工具

    SQL Server支持多种类型的备份,包括完整备份、差异备份、日志备份和文件或文件组备份。这些备份类型各有其应用场景,比如完整备份保存所有数据,而差异备份只记录自上次完整备份以来的变化,日志备份则用于事务日志...

    Sql Server数据库自动全备份的脚本(带7z压缩)

    这使得我们可以在脚本中执行SQL Server的T-SQL备份命令,或者启动7-Zip进行文件压缩。 4. **T-SQL 备份命令**: 使用T-SQL的`BACKUP DATABASE`语句可以创建数据库的全备份。在脚本中,你需要指定要备份的数据库...

    基于SQLServer的数据库备份与还原

    ### 基于SQL Server的数据库备份与还原 #### 一、引言 在现代信息技术领域,数据库系统作为数据管理的核心部分,在企业运营和个人信息管理中扮演着至关重要的角色。然而,无论是硬件故障还是软件错误,都可能对...

    数据库技术与应用—SQLServer第数据库备份与还原PPT学习教案.pptx

    数据库技术与应用—SQL Server第数据库备份与还原PPT学习教案.pptx 本资源主要讲解了数据库备份与还原技术的相关知识点,涵盖了数据库备份的重要性、备份方式、备份设备的创建与管理、数据库备份的实现等方面。 1....

    SQL Server 2000一键10秒极速安装.part5

    原理:其实只是提取了SQL server 2000安装后写入硬盘的文件及注册表信息等,所以只有83M, 比原来的安装包几百M小了非常多,但大家不要怀疑,这些绝对是完整的所有文件了。 将提取的文件制成了这个后缀为exe的自...

    sqlserver 数据库的备份与还原

    ### SQL Server数据库的备份与还原知识点...通过上述知识点的介绍,我们可以了解到SQL Server数据库备份与还原的具体方法及注意事项。正确地实施备份策略并遵循最佳实践,对于维护数据库系统的稳定运行具有重要意义。

    SqlServer2000数据库备份还原工具

    《SQL Server 2000数据库备份与还原详解》 SQL Server 2000作为一款广泛应用的关系型数据库管理系统,其数据的安全性和可恢复性至关重要。数据库备份与还原是数据库管理中的基础操作,确保在系统故障、数据丢失或...

    sqlserver多数据库自启动定时备份工具批量执行脚本

    "sqlserver多数据库自启动定时备份工具批量执行脚本"是一种高效的方法,它允许管理员自动化备份多个数据库,特别是在系统启动时自动运行,以确保数据始终得到保护。这个解决方案尤其适用于那些包含大量关键数据和对...

    sqlserver一键卸载工具

    针对这种情况,"sqlserver一键卸载工具"就是为了帮助用户彻底、干净地卸载SQL Server而设计的。 该工具的主要功能包括: 1. **全面扫描**:一键卸载工具能够全面扫描系统中的SQL Server组件,包括数据库引擎、分析...

Global site tag (gtag.js) - Google Analytics