Резервное копирование и восстановление SQL Server с помощью командлетов SQL Server 2012 PowerShell

Есть много случаев, когда имеет смысл выполнять резервное копирование и восстановление сценариев в PowerShell. Как показывает Аллен, Microsoft приложила усилия, чтобы сделать это намного проще.

Одна из самых распространенных шуток, которую вы можете услышать, говоря о наиболее важных задачах для администратора базы данных, звучит так: администратору баз данных требуется одна из двух вещей: хорошая резервная копия или хорошее резюме. Это так. Если вы не делаете резервные копии и не гарантируете возможность восстановления баз данных из этих резервных копий, вы подвергаете себя и свою компанию риску потери данных.

Команда BACKUP DATABASETransact-SQL существует уже давно (предшествовала DUMP DATABASE команда для тех из нас, кто работал с SQL Server достаточно долго, чтобы помнить ее). Это по-прежнему отличный способ защитить ваши базы данных, но чтобы помочь нам перейти к более способы выполнения этих повседневных задач, теперь нам доступны дополнительные методы. В этой статье я опишу на практических примерах, как можно эффективно создавать сценарии резервного копирования из PowerShell с помощью SMO и SQLPS, и как процесс создания сценариев значительно упрощается с помощью командлетов PowerShell, появившихся в SQL Server 2012.

В SQL Server 2012 Microsoft добавила четыре новых командлета для резервного копирования и восстановления:

• Backup-SqlDatabase
• Restore-SqlDatabase
• Backup-ASDatabase
• Restore-ASDatabase

Зачем рассматривать сценарии резервного копирования из внешнего процесса.

Когда процесс резервного копирования становится более сложным, необходимо выполнять все больше и больше работы с файловой системой. Эти задания могут потребовать именования и размещения резервных копий в каталогах, удаления старых резервных копий, которые больше не требуются, копирования их за пределы площадки, проверки целостности резервных копий, записи в журналы и т. д. Возможно, вам потребуется выполнить резервное копирование за одну операцию нескольких баз данных. , даже на разных серверах или скопируйте базу данных на несколько серверов или виртуальных машин для тестирования. В какой-то момент необходимо рассмотреть резервное копирование и восстановление по сценарию, и PowerShell идеально подходит для этого.

Скрипты с PowerShell и SMO 

В 2009 году я опубликовал технический документ для Microsoft, в котором я представил сценарий PowerShell под названием backup.ps1, использующий библиотеку объектов управления сервером (SMO) для резервного копирования ваших баз данных, и, поскольку SMO поддерживается для версий SQL Server с 2000 по 2012, он все еще работает. ( Понимание и использование поддержки PowerShell в SQL Server 2008 ) На самом деле, Microsoft не добавила много функций в эту область SMO в SQL Server 2012, поэтому здесь не так много нового для изучения.

В техническом документе я рассмотрел оснастки SQL Server для PowerShell и «мини-оболочку» SQLPS.exe, которая включена в SQL Server 2008 и SQL Server 2008 R2. Хотя это и было предписано группой PowerShell для расширения PowerShell, когда был представлен SQL Server 2008, технология изменилась, и команда PowerShell теперь продвигает «модульную» модель для расширения PowerShell. В результате PowerShell версии 2.0 является минимальным требованием на сервере перед установкой SQL Server 2012, а программа SQLPS.exe была заменена новым модулем под названием — подождите — SQLPS. 

Microsoft предоставила ряд новых командлетов в модуле SQLPS, большинство из которых предназначены для управления группами доступности и высокой доступностью/аварийным восстановлением. Но помимо этого, они предоставили четыре новых командлета, специфичных для операций резервного копирования и восстановления — Backup-SqlDatabase, Restore-SqlDatabaseи .Backup-ASDatabaseRestore-ASDatabase

 Установка модуля SQLPS

Когда вы запускаете PowerShell и хотите работать с модулем SQLPS, вам нужно использовать Import-Moduleкомандлет. Начиная с PowerShell версии 2.0, Microsoft проверяет имена объектов в процессе импорта по списку утвержденных глаголов. Поскольку команды Backup и Restore отсутствуют в утвержденном списке, вы получите сообщение о том, что в модуле существуют неутвержденные команды. Вы можете избежать этой ошибки, включив DisableNameCheckingпараметр – в командлет Import-Module при импорте модуля SQLPS. (Обратите внимание, что при импорте модуля SQLPS ваше местоположение будет установлено в корень поставщика SQL Server. Это ожидаемое поведение.)

Резервное копирование баз данных с помощью CmdLet Backup-SqlDatabase

 Простые резервные копии с помощью Backup-SQLDatabase

После того, как вы загрузили модуль, вы можете легко создать резервную копию базы данных с помощью такой команды:

{$dt = Get-Date -Format yyyyMMddHHmmss
$dbname = 'AdventureWorks'
Backup-SqlDatabase -ServerInstance TESTSQL -Database $dbname -BackupFile "E:\Backup\$($dbname)_db_$($dt).bak"

Конечно, есть несколько способов вызвать этот командлет. Другой вариант — использовать провайдера, перейти в каталог Databases в вашем экземпляре SQL Server и использовать командлет Get— ChildItem для перебора ваших баз данных для резервного копирования каждой из них. Поскольку вы уже подключены к своему экземпляру, вам не нужен ServerInstanceпараметр –.

В самом простом случае, если вы можете просто ввести

Set-Location SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases
get-childitem|Backup-SqlDatabase

.. который выполняет резервное копирование в каталог резервного копирования по умолчанию и использует имя базы данных в качестве имени файла резервной копии. Если вам нужно указать имя файла резервной копии или любой другой из множества возможных параметров, то вы можете сделать это.

Set-Location SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases
foreach ($db in (Get-ChildItem))
    {
        $dbname = $db.Name
        $dt = Get-Date -Format yyyyMMddHHmmss
        Backup-SqlDatabase -Database $dbname -BackupFile "$($dbname)_db_$($dt).bak"
    }

Это создаст резервную копию каждой из ваших пользовательских баз данных в каталоге резервного копирования по умолчанию и будет использовать имя базы данных, а также текущую дату и время резервного копирования в имени файла резервной копии. (Обратите внимание, что вы можете использовать Forceпараметр – с командлетом Get– ChildItemforeach в цикле, чтобы включить системные базы данных, но просто убедитесь, что вы отфильтровали tempdbбазу данных.)

get-childitem -force|where name -ne 'TempDB'| Backup-SqlDatabase

Вы также можете создать переменную, содержащую объект SMO Server, и использовать InputObjectпараметр – вместо – ServerInstanceобъекта. Хотя мы покажем, как это происходит для одной базы данных, это полезно, когда у вас есть список баз данных на разных серверах, для которых необходимо выполнить резервное копирование.

$dt = Get-Date -Format yyyyMMddHHmmss
$dbname = 'AdventureWorks'
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') 'TESTSQL'
Backup-SqlDatabase -InputObject $svr -Database $dbname -BackupFile "E:\Backup\$($dbname)_db_$($dt).bak"

Или вы можете использовать переменную, содержащую объект базы данных SMO для целевой базы данных, и использовать параметр -DatabaseObject.

$dt = Get-Date -Format yyyyMMddHHmmss
Set-Location SQLSERVER:\SQL\TESTSQL\DEFAULT\Databases\AdventureWorks
$db = Get-Item .
$dbname = $db.Name
Backup-SqlDatabase -DatabaseObject $db -BackupFile "E:\Backup\$($dbname)_db_$($dt).bak"

В каждом из этих примеров я использовал минимальное количество параметров, чтобы сделать их простыми. В своем коде вы должны обратить внимание на другие параметры, чтобы получить правильное решение для резервного копирования, соответствующее вашим потребностям. Так что же это за параметры и как их использовать?

 SQL-Backup: ключевые параметры

Параметр, который я бы всегда включал, — BackupAction. Возможные значения: «База данных», «Файлы» или «Журнал», а значение по умолчанию, если оно не включено, — «База данных». Если вы хотите сделать разностную резервную копию, укажите BackupActionбазу данных как базу данных и добавьте параметр -Incremental. Если вы хотите сделать резервную копию отдельных файлов или групп файлов, вы должны использовать параметр «Файлы», и, конечно же, параметр «Журнал» позволяет вам делать резервные копии журнала транзакций. Для экономии места в этих примерах этот параметр исключен, поэтому создаются полные резервные копии базы данных.

 Например, я бы всегда включал параметр –CompressionOption On . Я бы также обязательно включил этот -ConnectionTimeoutпараметр и установил для него значение 0, потому что вы не хотите, чтобы ваши резервные копии прерывались из-за того, что соединение между сценарием и SQL Server простаивает во время резервного копирования.

Если мы расширим список параметров Backup-SqlDatabaseкомандлета из вывода get-help для этого командлета, мы получим этот список:

Backup-SqlDatabase

[-Database]

[-BackupFile] ]

[-ServerInstance ]

[-BackupAction ]

[-BackupDevice ]

[-BackupSetDescription ]

[-BackupSetName ]

[-BlockSize ]

[-BufferCount ]

[-Checksum]

[-CompressionOption ]

[-ConnectionTimeout ]

[-ContinueAfterError]

[-CopyOnly]

[-Credential ]

[-DatabaseFile ]

[-DatabaseFileGroup ]

[-ExpirationDate ]

[-FormatMedia]

[-Incremental]

[-Initialize]

[-LogTruncationType ]

[-MaxTransferSize ]

[-MediaDescription ]

[-MediaName ]

[-MirrorDevices ]

[-NoRecovery]

[-NoRewind]

[-Passthru]

[-Restart]

[-RetainDays ]

[-Script]

[-SkipTapeHeader]

[-UndoFileName ]

[-UnloadTapeAfter]

[-Confirm]

[-WhatIf]

[]

Преимущества перед СМО

Для меня очевидно, что они добавили функциональности этому командлету по сравнению с тем, что доступно в SMO. Кое-что, что было доступно через Transact-SQL и отсутствовало в SMO, — это возможность устанавливать размер блока и количество буферов для резервных копий, и они включили эти свойства. Вот эквивалентные свойства и методы, доступные нам через объект SMO Backup:

(Это из обозревателя объектов в Visual Studio после загрузки SMOExtended DLL, где находятся объекты резервного копирования и восстановления.)

Восстановление баз данных с помощью CmdLet Restore-SQLDatabase.

Существует множество различных причин, по которым нам необходимо восстанавливать базы данных, поэтому вариантов восстановления намного больше, чем резервных копий.

Самый простой способ продемонстрировать восстановление — просто восстановить базу данных из полной резервной копии, установив параметр на перезапись существующей базы данных.

Restore-SqlDatabase -ServerInstance TESTSQL -Database AdventureWorks `
-BackupFile "E:\Backup\AdventureWorks_db_20130420153024.bak" -ReplaceDatabase

Одна из причин, по которой мне приходилось часто восстанавливать базы данных, заключается в восстановлении данных из какой-то таблицы, которую пользователь непреднамеренно удалил, но впоследствии были внесены другие транзакционные изменения, которые нельзя было потерять. Для этого я восстанавливал резервную копию в другой именованной базе данных на том же сервере, обычно с исходным именем базы данных с добавленной к имени датой резервной копии. Затем я мог бы скопировать потерянные данные из резервной копии непосредственно в исходную таблицу базы данных и удалить копию, когда все снова будет хорошо.

Восстановление баз данных с помощью SMO

Вот как мы делаем это, используя SMO напрямую.

# Connect to the specified instance
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'TESTSQL'
# Get the default file and log locations
# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)
$fileloc = $srv.Settings.DefaultFile
$logloc = $srv.Settings.DefaultLog
if ($fileloc.Length -eq 0) {
    $fileloc = $srv.Information.MasterDBPath
    }
if ($logloc.Length -eq 0) {
    $logloc = $srv.Information.MasterDBLogPath
    }
# Identify the backup file to use, and the name of the database copy to create
$bckfile = 'E:\Backup\AdventureWorks_db_20101016135438.bak'
$dbname = 'AdventureWorks_20101016'
# Build the physical file names for the database copy
$dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'
$logfile = $logloc + '\'+ $dbname + '_Log.ldf'
# Use the backup file name to create the backup device
$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')
# Create the new restore object, set the database name and add the backup device
$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')
$rs.Database = $dbname
$rs.Devices.Add($bdi)
# Get the file list info from the backup file
$fl = $rs.ReadFileList($srv)
foreach ($fil in $fl) {
    $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
    $rsfile.LogicalFileName = $fil.LogicalName
    if ($fil.Type -eq 'D'){
        $rsfile.PhysicalFileName = $dbfile
        }
    else {
        $rsfile.PhysicalFileName = $logfile
        }
    $rs.RelocateFiles.Add($rsfile)
    }
# Restore the database
$rs.SqlRestore($srv)

Одной из потенциальных проблем при восстановлении копии существующей базы данных на тот же сервер являются конфликты физических имен, и для их устранения используются объекты SMO RelocateFile. Это эквивалентно использованию предложения WITH MOVE в Transact-SQL. Интересно, что Restore-SqlDatabaseкомандлетам требуются одни и те же объекты SMO для выполнения одной и той же задачи.

Единственная разница между использованием чистого SMO и использованием Restore-SqlDatabaseкомандлета для достижения этой цели заключается в том, как вы сохраняете и передаете объекты RelocateFile. Нам нужно создать пустую коллекцию, что мы делаем сразу после прочтения списка файлов резервной копии, и добавить в коллекцию каждый объект RelocateFile, после чего мы вызываем Restore-SqlDatabaseкомандлет. Вот только последняя часть того же кода, но с использованием нового командлета.

# Get the file list info from the backup file
$fl = $rs.ReadFileList($srv)
$rfl = @()
foreach ($fil in $fl) {
    $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
    $rsfile.LogicalFileName = $fil.LogicalName
    if ($fil.Type -eq 'D') {
        $rsfile.PhysicalFileName = $dbfile
        }
    else {
        $rsfile.PhysicalFileName = $logfile
        }
    $rfl += $rsfile
    }
# Restore the database
Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `
 -BackupFile "E:\Backup\AdventureWorks_db_20101016135438.bak" `
 -RelocateFile $rfl

В этом случае я буду придерживаться чистого метода SMO, так как считаю его более чистым.

Восстановление баз данных на момент времени

Последний пример, которым я поделюсь, включает восстановление базы данных на определенный момент времени. Иногда проблема, вызвавшая восстановление, произошла в известное вам время, и вы можете решить вернуть базу данных в этот момент.

В этом сценарии мы храним все наши файлы резервных копий в каталоге E:\Backup на локальном сервере. У нас есть несколько полных резервных копий, несколько дифференциальных резервных копий и несколько резервных копий журнала транзакций, включая резервные копии журналов, сделанные после нашего целевого момента времени. Имена файлов соответствуют соглашению DatabaseName_type_datetime.ext, где тип — db, diff или tran, а ext — либо bak, либо trn.

$dbname = 'AdventureWorks'
$restorept = '2013-04-20 15:30:00'
Set-Location 'E:\Backup'
$fullfile = Get-ChildItem -Filter "$($dbname)_db_*" | Where-Object {$_.LastWriteTime -lt $restorept} | Sort-Object LastWriteTime Desc | Select-Object -First 1
$difffile = Get-ChildItem -Filter "$($dbname)_diff_*" | Where-Object {$_.LastWriteTime -lt $restorept} | Sort-Object LastWriteTime Desc | Select-Object -First 1
$tranfile = Get-ChildItem -Filter "$($dbname)_tran_*" | Where-Object {$_.LastWriteTime -gt $difffile.LastWriteTime} | Sort-Object LastWriteTime Asc

Теперь $fullfile переменная содержит информацию о файле для последней полной резервной копии перед целевым временем, $difffileпеременная содержит информацию о файле для последней дифференциальной резервной копии до целевого времени, а $tranfileпеременная содержит информацию о файлах всех резервных копий журнала транзакций, сделанных с момента дифференциального резервного копирования. определены в $difffile.

Во-первых, нам нужно восстановить полную резервную копию, с опцией замены и указать без восстановления.

Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `
-BackupFile $fullfile.FullName -ReplaceDatabase `
-NoRecovery

Далее восстанавливаем последний дифференциал, снова указав без восстановления.

Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `
-BackupFile $difffile.FullName -ReplaceDatabase `
-NoRecovery

Наконец, мы восстанавливаем резервные копии журнала транзакций, перебирая файлы в $tranfileпеременной. Если LastWriteTimeсвойство меньше нашей $restoreptпеременной, то восстанавливаем без восстановления и переходим к следующему. Первая резервная копия журнала, которая была сделана после нашей точки восстановления, восстанавливается с -ToPointInTimeпараметром без параметра -NoRecovery , и мы устанавливаем наш индикатор, чтобы мы не пытались восстановить какие-либо другие резервные копии журнала транзакций.

$recovery = 0
foreach ($trnfile in $tranfile) {
     if ($trnfile.LastWriteTime -lt $restorept) {
         Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `
-BackupFile $trnfile.FullName -ReplaceDatabase `
-NoRecovery
         }
    else {
         if ($recovery -eq 0) {
             Restore-SqlDatabase -ServerInstance TESTSQL -Database $dbname `
-BackupFile $trnfile.FullName -ReplaceDatabase `
-ToPointInTime $restorept
$recovery = 1       
            }
        }
    }

Здесь много универсальности, а прямой доступ к функциям операционной системы делает автоматизацию этих резервных копий и восстановлений намного удобнее, чем попытки работать через Transact-SQL.

 Например, вот свойства и методы SMO для объекта Restore:

Соответственно, вот текст справки от Restore-SqlDatabaseкомандлета с указанием параметров:

Restore-SqlDatabase

[-Database]

[[-BackupFile] ]

-ServerInstance

[-BackupDevice ]

[-BlockSize ]

[-BufferCount ]

[-Checksum]

[-ClearSuspectPageTable]

[-ConnectionTimeout ]

[-ContinueAfterError]

[-Credential ]

[-DatabaseFile ]

[-DatabaseFileGroup ]

[-FileNumber ]

[-KeepReplication]

[-MaxTransferSize ]

[-MediaName ]

[-NoRecovery]

[-NoRewind]

[-Offset ]

[-Partial]

[-Passthru]

[-RelocateFile ]

[-ReplaceDatabase]

[-Restart]

[-RestoreAction ]

[-RestrictedUser]

[-Script]

[-StandbyFile ]

[-StopAtMarkAfterDate ]

[-StopAtMarkName ]

[-StopBeforeMarkAfterDate ]

[-StopBeforeMarkName ]

[-ToPointInTime ]

[-UnloadTapeAfter]

[-Confirm]

[-WhatIf]

[]

Резервное копирование служб Analysis Services с помощью Backup-ASDatabase

Базы данных служб Analysis Services также нуждаются в резервном копировании, хотя вариантов для этого гораздо меньше. Первое, что вам нужно сделать, это загрузить командлеты служб Analysis Services, так как они находятся в другом модуле.

Import-Module SQLASCMDLETS

Давайте посмотрим на параметры Backup-ASDatabaseкомандлета.

Backup-ASDatabase

[-BackupFile]

[-Name]

[-AllowOverwrite ]

[-BackupRemotePartitions ]

[-ApplyCompression ]

[-FilePassword ]

[-Locations ]

[-Server ]

[-Credentials ]

[]

Обратите внимание, что здесь нет вариантов для создания разностных резервных копий или резервных копий журнала транзакций. Простой бэкап базы данных AWDB выглядит так.

Backup-ASDatabase "E:\Backup\AWDB.abf" AWDB

Если мы хотим перезаписать резервную копию, если она уже существует, мы можем добавить -AllowOverwrite  параметр, если мы хотим сжать резервную копию (всегда хорошая идея), мы можем добавить -ApplyCompressionпараметр, и если мы хотим зашифровать резервную копию, мы можем добавить -FilePasswordпараметр.

Восстановление базы данных служб Analysis Services с помощью Restore-ASDatabase

Точно так же не так много вариантов, когда мы хотим восстановить базу данных служб Analysis Services.

Восстановить-ASDatabase

[-RestoreFile]

[-Name]

[-AllowOverwrite ]

[-Locations ]

[-Security ]

[-Password ]

[-StorageLocation ]

[-Server ]

[-Credentials ] []

Чтобы восстановить базу данных AWDB, мы можем сделать это следующим образом.

Restore-ASDatabase "E:\Backup\AWDB.abf" AWDB -Security:CopyAll

Это восстанавливает базу данных, а также восстанавливает роли и участников из резервной копии.

Резюме

Резервное копирование и восстановление являются критически важными действиями в управлении данными любой организации. Задачи резервного копирования и восстановления для администрирования SQL Server часто лучше всего создавать в сценариях с использованием PowerShell и SMO, особенно когда администратору баз данных необходимо выполнять более сложные повторяющиеся задания. Например, там, где базы данных должны быть высокодоступными, хорошей практикой является создание сценариев для их восстановления, их тщательное тестирование и последующее использование для репетиций. Командлеты резервного копирования и восстановления, поставляемые с SQL Server 2012, идеально подходят для максимального упрощения задачи.