Sql-Server

提高恢復速度

  • March 11, 2016

我有一個來自 QA 數據庫的 ~3GB 備份文件，以及每日差異 (~500MB)。出於數據複雜性的原因，我們的一些開發人員喜歡使用該數據庫的定期備份，因此我們有一個流程可以每晚自動將其恢復到多個數據庫中（即每個開發人員 1 個數據庫）。

解壓後，數據庫大小報告為 ~14GB。

在我們的一台伺服器上，我們在 SSD 上執行這些數據庫，因此每個數據庫的恢復時間是可接受的 3 分鐘左右。但是，在另一台伺服器上，添加 SSD 不是一種選擇，並且僅兩個數據庫的恢復大約需要 17 分鐘。

恐怕這不會很好地擴展 - 我需要在該實例上為各種開發人員提供更多數據庫，並且恢復時間令人望而卻步。

我讀過將備份分成多個文件通常可以幫助恢復速度，但我無法控製備份過程本身。我可以與 dbas 交談，但他們很可能不想做出任何改變。

還原本身由以下 powershell 腳本管理：

[CmdletBinding()]
Param(   
   [Parameter(Mandatory=$True)]
   [string[]]$dbNames
)
$sourcePath = "C:\SQL\Backups\"
$baseDbFolder = "C:\SQL\Data\"
$dbServer = "MyServer"
$dbToRestore = "SourceDatabase"

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

if ( Get-Command "Get-PSSnapin" -errorAction SilentlyContinue -and Get-PSSnapin -Registered | where {$_.name -eq 'SqlServerCmdletSnapin100'} )
{
   if( !(Get-PSSnapin | where {$_.name -eq 'SqlServerCmdletSnapin100'}))
   { 
       Add-PSSnapin SqlServerCmdletSnapin100 | Out-Null
   } ;
}
else
{
   if (Get-Command "Get-PSSnapin" -errorAction SilentlyContinue)
   {
       if( !(Get-Module | where {$_.name -eq 'sqlps'}))
       { 
           Import-Module 'sqlps' -DisableNameChecking ;
       }
   } ;
}

$sourcePathFull = join-path (join-path $sourcePath (join-path Full $dbToRestore)) *.bak
$sourcePathDiff = join-path (join-path $sourcePath (join-path Diff $dbToRestore)) *.bak
$sourceFileFull = gci $sourcePathFull | sort LastWriteTime | select -last 1
$sourceFileDiff = gci $sourcePathDiff | sort LastWriteTime | select -last 1
if ((Get-Item $sourceFileFull).LastWriteTime -gt (Get-Item $sourceFileDiff).LastWriteTime)
{
   $sourceFileDiff = $null
}

function GetCreateDbSql ([string]$baseDbFolder, [string]$dbName)
{
return @"
USE [master]
GO
IF db_id('$dbName') IS NOT NULL
   SET NOEXEC ON
GO
CREATE DATABASE [$dbName] ON  PRIMARY 
( NAME = N'AFO_PRIMARY', FILENAME = N'$baseDbFolder$dbName.mdf' , SIZE = 16240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'AFO_log', FILENAME = N'$baseDbFolder$dbName.ldf' , SIZE = 1240KB , MAXSIZE = 2GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'$dbName', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [$dbName].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [$dbName] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$dbName] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$dbName] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$dbName] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$dbName] SET ARITHABORT OFF
GO
ALTER DATABASE [$dbName] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$dbName] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$dbName] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$dbName] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$dbName] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$dbName] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [$dbName] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$dbName] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$dbName] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$dbName] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$dbName] SET  DISABLE_BROKER
GO
ALTER DATABASE [$dbName] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$dbName] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$dbName] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [$dbName] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE [$dbName] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$dbName] SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE [$dbName] SET  READ_WRITE
GO
ALTER DATABASE [$dbName] SET RECOVERY SIMPLE
GO
ALTER DATABASE [$dbName] SET  MULTI_USER
GO
ALTER DATABASE [$dbName] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [$dbName] SET DB_CHAINING OFF
GO
ALTER DATABASE [$dbName] SET RECOVERY SIMPLE
GO
SET NOEXEC OFF
"@
}
function GetRestoreDbSql ([string]$fullBackupFile, [string]$diffBackupFile, [string] $baseDbFolder, [string]$dbName)
{
   $singleUser = @"
USE [master]

Alter Database [$dbName]
 SET SINGLE_USER With ROLLBACK IMMEDIATE
"@
   if ([string]::IsNullOrEmpty($diffBackupFile)) {
   $restoreFull = @"

RESTORE DATABASE [$dbName] FROM  DISK = N'$fullBackupFile' WITH  FILE = 1, RECOVERY, REPLACE,  STATS = 10
   , MOVE 'AFO_PRIMARY' TO '$baseDbFolder$dbName.mdf'
   , MOVE 'AFO_log' TO '$baseDbFolder$dbName.ldf'
"@
   } else {
   $restoreFull = @"

RESTORE DATABASE [$dbName] FROM  DISK = N'$fullBackupFile' WITH  FILE = 1, NORECOVERY, REPLACE,  STATS = 10
   , MOVE 'AFO_PRIMARY' TO '$baseDbFolder$dbName.mdf'
   , MOVE 'AFO_log' TO '$baseDbFolder$dbName.ldf'
"@
   }
   $restoreDiff = if ([string]::IsNullOrEmpty($diffBackupFile)) { "" } else { @"

RESTORE DATABASE [$dbName] FROM  DISK = N'$diffBackupFile' WITH  FILE = 1, STATS = 10, RECOVERY
"@ }    
   return "$singleUser$restoreFull$restoreDiff"
}
function GetTempSqlFilename ([string]$filename)
{
   return "$env:TEMP\$filename"
}
function WriteTempSqlFile ([string]$sql, [string]$filename)
{
   $stream = [System.IO.StreamWriter] $filename
   $stream.Write($sql)
   $stream.Close()
}

foreach ($dbName in $dbNames) {
   $createDbSqlFilename = GetTempSqlFilename "$dbName.create.sql"
   $restoreDbSqlFilename = GetTempSqlFilename "$dbName.restore.sql"
   $createDbSql = GetCreateDbSql $baseDbFolder $dbName
   $fileDiff = if ($fullOnly -eq $TRUE) { $null } else { $sourceFileDiff }
   $restoreDbSql = GetRestoreDbSql $sourceFileFull $fileDiff $baseDbFolder $dbName
   WriteTempSqlFile $createDbSql $createDbSqlFilename
   #Write-Host $createDbSqlFilename
   Invoke-Sqlcmd -InputFile $createDbSqlFilename -ServerInstance $dbServer -QueryTimeout 600
   WriteTempSqlFile $restoreDbSql $restoreDbSqlFilename
   #Write-Host $restoreDbSqlFilename
   Invoke-Sqlcmd -InputFile $restoreDbSqlFilename -ServerInstance $dbServer -QueryTimeout 600
}

在我的控制範圍內（即伺服器本身和恢復腳本），有沒有辦法改進恢復的性能？

正如史蒂夫在評論中所說，確保打開即時文件初始化。Stings 的回答討論了多個文件和壓縮，但不幸的是，這些都將放在BACKUP一邊。

但您也可以嘗試使用RESTOREBLOCKSIZE命令的、BUFFERCOUNT和MAXTRANSFERSIZE選項修改恢復命令。

不幸的是，用於 RESTORE 的 BOL 並沒有說明太多，因此您必須查看BACKUP DATABASE命令的 BOL。

  • BLOCKSIZE 指定物理塊大小，以字節為單位。支持的大小為 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 字節。磁帶設備的預設值為 65536，否則為 512。
  • BUFFERCOUNT指定要用於備份操作的 I/O 緩衝區的總數。您可以指定任何正整數；但是，由於 Sqlservr.exe 程序中的虛擬地址空間不足，大量緩衝區可能會導致“記憶體不足”錯誤。緩衝區使用的總空間由以下公式確定：buffercount * maxtransfersize。
  • MAXTRANSFERSIZE指定要在 SQL Server 和備份媒體之間使用的最大傳輸單位（以字節為單位）。可能的值是 65536 字節 (64 KB) 的倍數，最高可達 4194304 字節 (4 MB)。

這篇文章有一個很好的類比和MAXTRANSFERSIZE警告BUFFERCOUNT

這可能是一個棘手的使用選項，指定多個緩衝區可能會導致“記憶體不足”錯誤！始終小心使用此選項！恢復過程使用的總記憶體為 MaxTransferSize x BufferCount = 恢復所需的記憶體 如果您的伺服器上沒有所需的記憶體量，您將收到錯誤！

您可以將 SQL Server 還原過程視為使用儲存桶來滅火。

BufferCount 設置用於滅火的桶數，而 MaxTransferSize 設置這些桶應該有多滿。正如您可以想像的那樣，使用許多水桶並且只裝滿一半會影響滅火所需的時間，就像只使用幾個水桶但將它們一直裝到頂部可能會產生負面影響，如果水桶是太重舉不起來。為每個環境設置 MaxTransferSize 和 BufferCount 選項可能不同，因為您需要優化這些值以匹配您的儲存和記憶體配置。因此，請使用不同的值，直到獲得優化的恢復時間！

正如他最後所說，每台伺服器對它們的使用會有所不同，因此您必須稍微使用它們才能獲得最佳恢復速度。

這是一個很好的問題，在相當多的細節中使用了它們。它正在談論，BACKUPS但同樣，選項是相同的。

編輯我整天都在尋找這個。Nic Cain 有一個腳本，它會一遍又一遍地嘗試備份，並通過更改設置來幫助您找到最佳結果。 連結在這裡。 您應該能夠輕鬆地修改此腳本以改為休息 RESTORE。

要減少 BACKUP/RESTORE 時間，請嘗試使用多個 T-SQL 磁碟和壓縮

通過在備份和恢復 T-SQL 命令中添加多個磁碟，我注意到備份和恢復的顯著速度提升。在您修改 PS 腳本之前，請先嘗試此基本測試併計算結果時間。也嘗試使用壓縮。與您的 DBA 合作，向他們展示差異。作為 DBA 本人來說，這些更改很容易實現。如果他們無法做出這些微小的改變，我會想知道他們實際上做了什麼。

選項 A：

使用您的源系統將數據庫備份到目標 QA 系統，使用多個文件磁碟和壓縮以最小化 I/O 並最大化執行緒：

在源 SQL Server 上：

BACKUP DATABASE [MyDatabase] 
to  **DISK** = N'\\\QASystem\ShareLocation\FullBackupFile1.bak'
, **DISK** = N'\\\QASystem\ShareLocation\FullBackupFile2.bak' 
WITH INIT, COMPRESSION

在目標 SQL Server 上：

RESTORE DATABASE [MyDatabase] 
FROM **DISK** = N'X:\LocalDirectory\FullBackupFile1.bak'
, **DISK** = N'X:\LocalDirectory\FullBackupFile2.bak' WITH  FILE = 1
, RECOVERY
, REPLACE
, STATS = 10
, MOVE 'AFO_PRIMARY' TO 'MyDatabase.mdf'
, MOVE 'AFO_log' TO 'MyDatabase.ldf'

選項 B：

如果您的網路吞吐量非常高——我們的吞吐量是 10GB，那麼您可能應該使用 SSD 磁碟作為文件的恢復位置，而不是通過網路將它們備份到速度較慢的磁碟。

在源 SQL Server 上：

BACKUP DATABASE [MyDatabase] 
to **DISK** = N'Z:\SSDLocalDirectory\FullBackupFile1.bak'
, **DISK** = N'Z:\SSDLocalDirectory\FullBackupFile2.bak' 
WITH INIT, COMPRESSION

在目標 SQL Server 上：

RESTORE DATABASE [MyDatabase] 
FROM **DISK** = N'\\\SSDSystem\ShareLocation\FullBackupFile1.bak'
, **DISK** = N'\\\SSDSystem\ShareLocation\FullBackupFile2.bak' WITH  FILE = 1
, RECOVERY
, REPLACE
, STATS = 10      
, MOVE 'AFO_PRIMARY' TO 'MyDatabase.mdf' 
, MOVE 'AFO_log' TO 'MyDatabase.ldf'

注意： 如果您發現性能有所提高，請嘗試添加更多磁碟文件，看看它是否會進一步提高。

選項 C：

我不認為這是我會選擇的，但它確實也專門回答了您的問題，並且可能會繞過頑固的 DBA。使用 SQL Server 2008 R2 及更高版本，您可以創建附加到文件共享儲存的數據庫。理論上，您可以共享 SSD 磁碟並使數據庫位於實際的數據庫共享中。我想那裡的備份會快得多，但是您的網路可能會飽和，具體取決於您訪問這些數據庫的程度。使用此策略分離/重新連接也是一種選擇。

https://support.microsoft.com/en-us/kb/304261

引用自：https://dba.stackexchange.com/questions/131693

相關問答

Sql-Server

執行 DELETE 儲存過程，但發現 WHILE 循環中的性能隨著迭代次數的增加而下降

  • March 23, 2022
檢視問答
Sql-Server

Powershell 使用 Restore-Database 將 Azure SQL 數據庫還原到本地

  • March 14, 2022
檢視問答
Sql-Server

為什麼添加 TOP 1 會顯著降低性能？

  • December 29, 2021
檢視問答
Sql-Server

SQL Server 如何知道謂詞是相關的？

  • December 21, 2021
檢視問答
Sql-Server

啟用觸發器時緩慢刪除記錄

  • October 3, 2021
檢視問答
Sql-Server

從 RESTORE HEADERONLY 中提取欄位

  • July 23, 2021
檢視問答