SQL Server 資料庫基於備份文件的【一鍵還原】

来源:https://www.cnblogs.com/xuliuzai/archive/2019/04/23/10752629.html
-Advertisement-
Play Games

1. 備份與還原的基礎說明 我們知道在DBA的日常工作中，SQL Server 資料庫的恢復請求偶有發生，可能是用作數據的追蹤，可也可能能是資料庫的災難恢復。 資料庫常用的備份命令如下： 備份文件的命名格式為：資料庫名字_備份類型（Full或Diff或Trn的一種）_時間格式.文件類型（bak或tr ...


1. 備份與還原的基礎說明

我們知道在DBA的日常工作中，SQL Server 資料庫的恢復請求偶有發生，可能是用作數據的追蹤，可也可能能是資料庫的災難恢復。

資料庫常用的備份命令如下：

----完整備份
Declare @FullFileName Varchar(200)
Declare @FileFlag varchar(20)
Set @FileFlag=REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-','')+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '')
Set @FullFileName='文檔路徑\資料庫名字_FULL'+@FileFlag+'.bak'
BackUp DataBase 資料庫名字 To Disk=@FullFileName with init

----差異備份
Declare @DiffFileName varchar(200)
Declare @FileFlag varchar(200)
Set @FileFlag=REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-','')+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '')
Set @DiffFileName='文檔路徑\資料庫名字_Diff_'+@FileFlag+'.bak'
BackUp DataBase 資料庫名字 To Disk=@DiffFileName with init,differential 

----事務日誌備份
Declare @FileName Varchar(200)
Declare @FileFlag varchar(20)
Set @FileFlag=REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-','')+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '')
Set @FileName='文檔路徑\資料庫名字_Trn_'+@FileFlag+'.trn'
BackUp Log 資料庫名字 To Disk=@FileName with init

 

備份文件的命名格式為：資料庫名字_備份類型（Full或Diff或Trn的一種）_時間格式.文件類型（bak或trn的一種） 

其中的時間格式為：年月日時分秒 ，例如：20190423140813。

例如，資料庫TestRestoreOP的備份文件如下：

 

我們花費篇章來說明備份文件的格式化，主要是因為【一鍵】還原的基礎是文件的標準化。

 

相應的還原命令如下：

----完整備份還原
RESTORE DATABASE 資料庫名字 FROM 
DISK = '完整備份的文件'---'TTTTTTT.BAK' 
WITH NORECOVERY, MOVE '資料庫名字_Data' TO 'D:\指定路徑\資料庫名字_Data.mdf',
MOVE '資料庫名字_Log' TO 'D:\指定路徑\資料庫名字_Log.ldf'

----差異備份還原
RESTORE DATABASE 資料庫名字 FROM 
DISK = '差異備份的文件'------'SSSSSSSSS.BAK' 
WITH NORECOVERY, MOVE '資料庫名字_Data' TO 'D:\指定路徑\資料庫名字_Data.mdf',
MOVE '資料庫名字_Log' TO 'D:\指定路徑\資料庫名字_Log.ldf'

----log備份還原
RESTORE Log 資料庫名字 
FROM DISK ='事務日誌備份的文件' -----'XXXXXXXX.trn'
WITH NORECOVERY

2.遠程備份文件的【一鍵】還原

實際的生產中，我們常將備份文件Copy至遠程伺服器上，所以還原的時候，還要將這些文件Copy到指定服務上再進行還原。還有一種情況，就是Log還原可能需要逐一還原多個日誌文件，有時候，甚至十幾個文件需要還原。

針對這種這種情況，日常工作中，我們逐漸提煉成了以下SQL，替換參數後，基本實現 一鍵還原。

主要實現的功能有四點：

（1）將遠程Server 上的指定備份路徑下的文件Copy值本地指定路徑；（如果文件以Copy值本地，這一步可以省略，對應的代碼為1和2部分）

（2） 將這些文件屬性讀到表BackupFile；

（3）根據文件命名的時間屬性，還原最近的一個完整備份 和一個差異備份；

（4）還原差異備份後產生的所有日誌備份。

3.代碼實現

---0 --定義要還原的資料庫名字
    DECLARE @Cmd varchar (1024)
     DECLARE @dbName sysname 
     Set @dbName='TestRestoreOP'

--- 1 --定義遠程備份文件所在目錄
    DECLARE @sourceFile NVARCHAR(500)  
    SET @sourceFile = '\\169.XXX.XXX.XXX\d$\SQL_BackFile'

----2 -- 將遠程備份的目錄，Copy至本地 D:\SQL_RestoreFile 目錄下，MAXAGE:3 代表Copy最近3天的文件
    SET @Cmd=('master.dbo.xp_cmdshell '+'''ROBOCOPY.exe  "'+@sourceFile+'" "D:\SQL_RestoreFile" *.* /E /XC /XN /X /MAXAGE:3 /MINAGE:0 ''')
    print @Cmd
    EXEC (@Cmd)

      Print '將遠程需要還原的文件Copy至本地'

----3 -- 獲取還原文件List
        DECLARE @Path VARCHAR(260)
        SET @Path = 'D:\SQL_RestoreFile'
        IF RIGHT(@Path, 1) <> '\'
         SET @Path = @Path + '\'
         Print @Path
      ----判斷表BackupFile是否已經存在,不存在則創建
          IF (SELECT COUNT(*) FROM SYS.sysobjects WHERE name='BackupFile'and xtype='U')=0
            BEGIN
                CREATE TABLE BackupFile
                (
                  id INT ,  --編號
                  directory VARCHAR(260) ,  --路徑
                  depth INT , --深度，相對與@path
                  IsFile BIT ,
                  filename VARCHAR(260),
                  IsRestore int ,--是否還原
                )--0文件夾1文件名成
            END 

            Truncate table BackupFile

      -----判斷表TMP_BackupFile是否已經存在,存在則刪除再創建
          IF (SELECT COUNT(*) FROM SYS.sysobjects WHERE name='TMP_BackupFile'and xtype='U')<>0
            BEGIN
                DROP TABLE TMP_BackupFile  
            END 
        CREATE TABLE TMP_BackupFile
            (
              id INT IDENTITY ,  --編號
              directory VARCHAR(260) ,  --路徑
              depth INT , --深度，相對與@path
              IsFile BIT ,
              filename VARCHAR(260),
              IsRestore int ,--是否還原
            )--0文件夾1文件名成


       ----將@Path 目錄下結構讀入到表TMP_BackupFile中
        INSERT  TMP_BackupFile
                ( directory ,
                  depth ,
                  IsFile
                )
        
        EXEC master.dbo.xp_dirtree @path = @Path, @depth = 0, @file = 1

        update TMP_BackupFile set filename=directory,IsRestore=0

       Print '將需要還原的文件信息讀入到表TMP_BackupFile中'


        -----設置不需要還原的資料庫文件,即刪除
         DELETE FROM dbo.TMP_BackupFile WHERE directory  NOT LIKE '%'+@dbName+'%'

        ----設置刪除不符合日期規則的文件
        DELETE FROM dbo.TMP_BackupFile WHERE  left(right([filename],18),14)<'20190413015000'

        -----更新目錄

        UPDATE  TMP_BackupFile
        SET     directory = @Path + directory
        WHERE   depth = 1

        ------
        if exists(  select * from TMP_BackupFile  WHERE   depth > 1)
        begin

        Print 'Error:備份文件所在的路徑不對，或者@Path包含了不應該存在的文件夾目錄！'
        
        end  
        -------

        INSERT INTO BackupFile (directory, depth, IsFile, [filename], IsRestore)
        SELECT B.directory, B.depth, B.IsFile, B.[filename], B.IsRestore FROM TMP_BackupFile B 
        left join BackupFile e on B.[filename]=e.[filename] where e.[filename] is null 


---4 --定義5/6/7 步驟需要的參數

    DECLARE @filename NVARCHAR(500) 
    DECLARE @backupPath NVARCHAR(500)


-- 5 -- 找到需要還原的完整備份文件，進行完整還原

   SELECT top 1  @filename =[filename] FROM BackupFile WHERE IsRestore=0 AND directory LIKE '%FULL%.bak' ORDER BY left(right([filename],18),14) desc
   Print @filename 
   
   Print 'Msg:完整備份文件：' + @filename  + '開始還原！'

   SELECT @backupPath=directory 
   FROM BackupFile WHERE filename=@filename AND  IsRestore=0
   print @dbName
   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
       + @backupPath + ''' WITH  FILE = 1,  MOVE N'''+@dbName+''' TO N''D:\sql_data\'+@dbName+'.MDF'', MOVE N'''+@dbName+'_Log'' TO N''D:\sql_log\'+@dbName+'.LDF'',NORECOVERY,    NOUNLOAD,  STATS = 5' 
   exec (@cmd)
   PRINT @cmd

   UPDATE BackupFile SET IsRestore=1 WHERE filename=@filename AND  IsRestore=0

   Print 'Msg:完整備份文件：' + @filename  + '還原完成！'

-- 6 --- 找到需要還原的差異備份文件，進行增量還原

    SELECT top 1 @filename=[filename] FROM BackupFile WHERE IsRestore=0 AND directory LIKE '%Diff%.bak'  ORDER BY left(right([filename],18),14) desc
    print @filename

    Print 'Msg:獲取得知需要還原的差異備份文件：' + @filename  + ',此時將不需要還原的差異文件/日誌文件設置為不需要還原'
    update  BackupFile  set IsRestore=10  
    WHERE IsRestore=0 AND 
    (directory LIKE '%Diff%.bak' or directory LIKE '%TRN%.TRN'  ) and left(right([filename],18),14)<left(right( @filename,18),14)

      Print 'Msg:差異備份文件：' + @filename  + '開始還原！'

    SELECT @backupPath=directory FROM BackupFile WHERE filename=@filename AND  IsRestore=0
   
    SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''  
       + @backupPath + ''' WITH NORECOVERY' 
    exec(@cmd)
    PRINT @cmd

    UPDATE BackupFile SET IsRestore=1 WHERE filename=@filename AND  IsRestore=0

     Print 'Msg:差異備份文件：' + @filename  + '還原完成！'

-- 7  --日誌備份文件還原
    DECLARE filenames CURSOR FOR  
        SELECT [filename] FROM BackupFile WHERE IsRestore=0 AND directory LIKE '%TRN%.TRN'  ORDER BY left(right([filename],18),14) asc
    OPEN filenames  
    -- Loop through all the files for the database  
    FETCH NEXT FROM filenames INTO @filename  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  

       Print 'Msg:日誌備份文件：' + @filename  + '開始還原！'
    
       SELECT @backupPath=directory FROM BackupFile WHERE filename=@filename AND  IsRestore=0
       SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''  
           +@backupPath+ ''' WITH NORECOVERY' 
       exec(@cmd)
   
       UPDATE BackupFile SET IsRestore=1 WHERE filename=@filename AND  IsRestore=0

        Print 'Msg:日誌備份文件：' + @filename  + '還原完成！'

       PRINT @cmd
       FETCH NEXT FROM filenames INTO @filename  
    END 
    CLOSE filenames  
    DEALLOCATE filenames  

-- 8 -- 將資料庫的狀態由真正還原Restore正常狀態！ 

 Print '將資料庫的狀態由真正還原Restore正常狀態！'
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 
PRINT @cmd

Print '資料庫所有的還原操作都已完成！'---0 --定義要還原的資料庫名字
    DECLARE @Cmd varchar (1024)
     DECLARE @dbName sysname 
     Set @dbName='TestRestoreOP'

--- 1 --定義遠程備份文件所在目錄
    DECLARE @sourceFile NVARCHAR(500)  
    SET @sourceFile = '\\169.XXX.XXX.XXX\d$\SQL_BackFile'

----2 -- 將遠程備份的目錄，Copy至本地 D:\SQL_RestoreFile 目錄下，MAXAGE:3 代表Copy最近3天的文件
    SET @Cmd=('master.dbo.xp_cmdshell '+'''ROBOCOPY.exe  "'+@sourceFile+'" "D:\SQL_RestoreFile" *.* /E /XC /XN /X /MAXAGE:3 /MINAGE:0 ''')
    print @Cmd
    EXEC (@Cmd)

      Print '將遠程需要還原的文件Copy至本地'

----3 -- 獲取還原文件List
        DECLARE @Path VARCHAR(260)
        SET @Path = 'D:\SQL_RestoreFile'
        IF RIGHT(@Path, 1) <> '\'
         SET @Path = @Path + '\'
         Print @Path
      ----判斷表BackupFile是否已經存在,不存在則創建
          IF (SELECT COUNT(*) FROM SYS.sysobjects WHERE name='BackupFile'and xtype='U')=0
            BEGIN
                CREATE TABLE BackupFile
                (
                  id INT ,  --編號
                  directory VARCHAR(260) ,  --路徑
                  depth INT , --深度，相對與@path
                  IsFile BIT ,
                  filename VARCHAR(260),
                  IsRestore int ,--是否還原
                )--0文件夾1文件名成
            END 

            Truncate table BackupFile

      -----判斷表TMP_BackupFile是否已經存在,存在則刪除再創建
          IF (SELECT COUNT(*) FROM SYS.sysobjects WHERE name='TMP_BackupFile'and xtype='U')<>0
            BEGIN
                DROP TABLE TMP_BackupFile  
            END 
        CREATE TABLE TMP_BackupFile
            (
              id INT IDENTITY ,  --編號
              directory VARCHAR(260) ,  --路徑
              depth INT , --深度，相對與@path
              IsFile BIT ,
              filename VARCHAR(260),
              IsRestore int ,--是否還原
            )--0文件夾1文件名成


       ----將@Path 目錄下結構讀入到表TMP_BackupFile中
        INSERT  TMP_BackupFile
                ( directory ,
                  depth ,
                  IsFile
                )
        
        EXEC master.dbo.xp_dirtree @path = @Path, @depth = 0, @file = 1

        update TMP_BackupFile set filename=directory,IsRestore=0

       Print '將需要還原的文件信息讀入到表TMP_BackupFile中'


        -----設置不需要還原的資料庫文件,即刪除
         DELETE FROM dbo.TMP_BackupFile WHERE directory  NOT LIKE '%'+@dbName+'%'

        ----設置刪除不符合日期規則的文件
        DELETE FROM dbo.TMP_BackupFile WHERE  left(right([filename],18),14)<'20190413015000'

        -----更新目錄

        UPDATE  TMP_BackupFile
        SET     directory = @Path + directory
        WHERE   depth = 1

        ------
        if exists(  select * from TMP_BackupFile  WHERE   depth > 1)
        begin

        Print 'Error:備份文件所在的路徑不對，或者@Path包含了不應該存在的文件夾目錄！'
        
        end  
        -------

        INSERT INTO BackupFile (directory, depth, IsFile, [filename], IsRestore)
        SELECT B.directory, B.depth, B.IsFile, B.[filename], B.IsRestore FROM TMP_BackupFile B 
        left join BackupFile e on B.[filename]=e.[filename] where e.[filename] is null 


---4 --定義5/6/7 步驟需要的參數

    DECLARE @filename NVARCHAR(500) 
    DECLARE @backupPath NVARCHAR(500)


-- 5 -- 找到需要還原的完整備份文件，進行完整還原

   SELECT top 1  @filename =[filename] FROM BackupFile WHERE IsRestore=0 AND directory LIKE '%FULL%.bak' ORDER BY left(right([filename],18),14) desc
   Print @filename 
   
   Print 'Msg:完整備份文件：' + @filename  + '開始還原！'

   SELECT @backupPath=directory 
   FROM BackupFile WHERE filename=@filename AND  IsRestore=0
   print @dbName
   SET @cmd =
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux上的壓縮與歸檔
    非常非常非常簡要的描述而已。 壓縮 壓縮的簡要原理是通過一些演算法，拿CPU的計算時間去換磁碟上存儲的空間。同時還可節省網路傳輸中的帶寬。 對於文本文件的壓縮效果比較好，對二進位程式、圖片等文件的壓縮效果就很差。 compress, uncompress 文件名：*.Z 古老的壓縮工具，目前已經不使用 ...
  • 016.OpenStack及雲計算（面試）常見問題
    什麼是雲計算? 雲計算是一種採用按量付費的模式，基於虛擬化技術，將相應計算資源（如網路、存儲等）池化後，提供便捷的、高可用的、高擴展性的、按需的服務（如計算、存儲、應用程式和其他 IT 資源）。 雲計算的基本特征？ 自主服務：可按需的獲取雲端的相應資源（主要指公有雲）； 網路訪問：可隨時隨地使用任何 ...
  • 使用sqlserver 鏈接遠程伺服器進行查詢
    參考：http://blog.csdn.net/pengxuan/article/details/51742296 ...
  • SQL Server 資料庫部分常用語句小結（三）
    21.SQL運行Log的讀取 .EXEC xp_readerrorlog 0,1,null,null,'開始時間','結束時間' 22. Alwayson 狀況及傳輸情況監控 23. （1）列出高級配置選項 Step 1， 先將 show advanced option 設為 1 Step 2， 運 ...
  • redis.conf常用配置說明
    最近學了 Redis，在 Linux 上安裝的，接下來就簡單講解一下修改 Redis 配置文件 修改密碼： 新安裝的 Redis 是預設沒有密碼的，可以給Redis設置一個密碼 先進入 Redis 的配置文件， 按 “/” 輸入 “requirepass” 搜索， 按 “n” 跳到下一個，按 “N” ...
  • 騰訊雲centos7.2安裝mysql
    一、查看是否安裝mysql 什麼都沒顯示，說明沒有安裝 二、進入到opt目錄下，使用wget下載官方yum源的rpm包 三、安裝啟動mysql yum安裝： 然後會提示輸入y，輸入即可，安裝完成如下 啟動mysql服務： 查看mysql狀態： 關閉mysql服務： 開機啟動： 重啟systemctl ...
  • mssql sqlserver 保留小數位指定位數的2種方法分享
    摘要: 下文講述將"sql數值型"類型數值轉換為指定小數位的數據 方法1:採用 cast 方式轉換數值類型至指定小數位： 方法2:採用 convert 方式轉換數值類型至指定小數位： 參閱:http://www.maomao365.com/?p=6220 ...
  • 資料庫設計-物理設計
    資料庫設計 物理設計： 選擇合適的資料庫管理系統。 考慮因素：成本，業務場景，開發語言，功能，操作系統等。 選擇合適的資料庫管理系統。 考慮因素：成本，業務場景，開發語言，功能，操作系統等。 Oracle 2.定義資料庫，表及欄位，要符合命名規範。 選擇存儲引擎：這裡以MySQL為例 選擇存儲引擎： ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類