Руководство предназначено для начинающих администраторов БД, в нем если будем касаться смежных, но важных тем, то эти смежные темы мы здесь не будем полностью рассматривать. Материал разбит на 5 глав, от простого к сложному.

Предлагаю создать на нашем тестовом сервере, где установлен SQL Server, папку на диске C:\test. В этой папке мы будем хранить наши тестовые базы данных которые будем использовать для исследования создания резервных копий и сами файлы наших тестовых баз данных.

Ну и создадим сразу же нашу базу данных скриптом:

create database backup_test
on  primary -- имя файловой группы
(
	name = N'backup_test', -- имя файла данных внутри БД
	filename = N'C:\test\backup_test.mdf', -- имя файлов данных в файловой системе сервера
	size = 5mb, -- начальный размер файла (минимальный начальный размер файла 5 мегабайтов, меньше не получится создать)
	filegrowth = 1mb -- приращение файла в мегабайтах (можно задавать в процентах)
)
log on -- файл журнала транзакций, наш журнал состоит из одного файла, как и в большинстве встречающихся случаев
(
	name = N'backup_test_log',
	filename = N'C:\test\backup_test_log.ldf',
	size = 512kb , -- начальный размер файла (минимальный начальный размер файла 512 килобайтов, меньше не получится создать)
	filegrowth = 10% -- приращение файла в процентах (можно задавать в кило/мега/гига/тера байтах)
)

Команда CREATE DATABASE подробно описана в справке Microsoft Books online help, сокращенно BOL.

Надеюсь читатель при необходимости будет пользоваться BOL, а здесь отмечу то, что наверняка уже итак известно тебе. У любой БД в SQL Server есть как минимум одна файловая группа с именем PRIMARY, хотя конечно их можно создавать больше со своими именами. У этой файловой группы должен быть как минимум один файл, который хранится на сервере в файловой системе (файлов в файловой группе так же может быть больше чем один, и необходимость такая возникает, например, мы не имеем возможности увеличить диск где хранится файл базы данных, а имеем возможность подключить еще один или более дисков и на этих дисках разместить другие файлы, тем самым все новые данные будут сохраняться сразу в несколько файлов, равномерно заполняя их данными.

Т.е. данные таблицы которая привязана к файловой группе, в свою очередь файловая группа имеет несколько файлов, не будут «дублироваться» в файлах, а будут разбросаны по файлам, причем в неизвестном нам порядке, т.е. 1-я строка таблицы с данными может оказаться в 1-ом файле или во 2-ом, но только в одном, а десятитысячная строка может быть совершенно в другом файле, или же случайно оказаться в 1-ом файле. Поэтому при копировании или восстановлении базы данных с несколькими файлами, нужно иметь все файлы, которые использует наша БД.

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

FULL BACKUP

Полный (full) backup базы данных
Итак, самый простой случай это полный backup базы данных. Сделаем его командой T-SQL:

backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'

и еще один раз повторим эту же команду. Мы с тобой сделали только что 2 резервных копии нашей тестовой базы данных на диск.
Здесь хочу отметить то, что в один и тот же файл (в нашем случае этот файл C:\test\backup_test_FULLBACKUP.bak) можно сохранить несколько резервных копий базы данных, и более того, разных баз данных. А теперь представим такую ситуацию, мы нашли в файловом архиве где хранятся резервные копии баз данных какой-то файл, и по названию нам не понять какие в нем копии и каких баз данных. Логично предположить, что должна быть возможность исследовать этот файл, и понять какие же копии баз данных в какое время в нем находятся.
Конечно же такая возможность есть и для восстановления информации о резервных копиях для этого нам потребуется команда RESTORE HEADERONLY. Предлагаю ее сейчас выполнить:

restore headeronly from disk = N'C:\test\backup_test_FULLBACKUP.bak'

Если ты выполнил 2 раза, как я и просил ранее, команду BACKUP DATABASE, то ты увидишь результатом команды таблицу с набором полей и с 2мя строками, которые, как ты уже и догадался, содержат информацию о каждом сделанном ранее backup в этот файл. Предлагаю тебе бегло посмотреть все поля нашего выведенного результата, ты наверняка поймешь большинство из них. Сейчас на объяснении каждой колонки я не буду останавливаться (но при желании у тебя есть BOL), а пока предлагаю рассмотреть 1 поле: Position – у тебя должно быть значения 1,2. Это номер «резервной копии» внутри рассматриваемого файла. Т.е. по сути, чем выше номер, тем позже сделан backup. И этот номер нам понадобится при восстановлении, мы можем восстановить нашу БД как из 1й копии из нашего файла, так и из 2й.
Ну и еще сразу обращу внимание на поле DatabaseBackupLSN, как ты видишь у 1й копии, это значение равно нулю, что означает, что это самый первый backup после создания нашей базы данных. При следующих операциях создания резервных копий это значение будет постоянно увеличиваться. Про LSN мы подробнее поговорим попозже в другой главе, а пока материал предлагаю усваивать постепенно.
Итак, мы увидели, что в файле backup – C:\test\backup_test_FULLBACKUP.bak есть две полные резервные копии (то что резервные копии именно полные мы видим из колонки BackupType предыдущего выполненного запроса), и я предлагаю перейти к восстановлению 2й копии из этого файла на наш же тестовый сервер, но БД назовем новым именем – backup_test_2, по причине того, что имя базы данных в пределе одного SQL Server должно быть уникально, и мы пока не хотим удалять оригинальную БД. Вообще нам потребуется информация о том, сколько и какие файлы в 2й резервной копии нашего backup есть и команда RESTORE HEADERONLY нам эту информацию не отобразила, чтобы уточнить эту информацию (а не вспоминать ее, или в случае «когда нашли файл backup неизвестный и мы его еще только исследуем») воспользуемся командой – RESTORE FILELISTONLY:

restore filelistonly from disk = N'C:\test\backup_test_FULLBACKUP.bak' with file=2

Параметры этой команды надеюсь тебе понятны: путь файла backup, и file=2 это, как ты уже догадался, номер резервной копии в этом файле.
Выполни эту команду.
Результирующий набор отображает:
1. имена файлов внутри БД – LogicalName. Это имя файла останется неизменным в нашей копии, его нельзя менять.
2. PhysicalName – в каком месте располагались файлы нашей БД во время резервного копирования, мы их поменяем при восстановлении, по причине того, что у нас в папке C:\test не может несколько файлов с одинаковыми именами
3. ну и прочая информация которую я надеюсь ты сам сможешь интерпретировать (не забывай пользоваться BOL).

Ну и давай теперь восстановим копию нашей базы данных:

restore database backup_test_2 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 2,
	move N'backup_test' to N'C:\test\backup_test_2.mdf',
	move N'backup_test_log' to N'C:\test\backup_test_log_2.ldf'

Собственно, таким образом мы восстановили базу данных с новым именем, и конечно же имена физических файлов в файловой системе не должны быть занятыми, иначе операция завершится с ошибкой. Ну и если бы мы не указали file = 2, то по умолчанию восстановление бы шло с 1й позиции backup.
И для закрепления материала, рекомендую тебе самостоятельно создать еще одну базу данных, сделать несколько резервных копий (между этими операциями создать таблицу в твоей базе данных, добавлять новые строки) в наш ранее созданный файл и в новые файлы, и восстановить из разных файлов эти резервные копии.
Напоследок хочу сказать, то что помимо full backup могут быть другие типы резервных копий, но они будут привязаны именно к последнему full backup и без него, смысла в других типах резервных копий абсолютно нет. И к тому же, пока мы не сделали полную резервную копию, другой тип резервной копии SQL Server для этой базы нам просто сделать не позволит. (это верно пока мы не затронули тему восстановление по файлам, но эта тема не этого раздела и пока предлагаю оставить ее «на потом»).

DIFFERENTIAL BACKUP

Разностный (differential) backup базы данных
Смысл differential backup в том, что этот процесс сохраняет только измененные данные (страницы) в файл backup. Т.е. если у нас размер базы данных достаточно большой, например, архив за несколько лет, и изменения происходят не всех данных за период между процессами резервного копирования, а лишь небольшой процент добавляется/изменяется, то в этом случае разностное резервирование вполне оправдано, для сокращения времени создания и занимаемого места этой копии. В случае если база данных за промежуток между backup может на 50% или 100% поменяться полностью, то смысла в differential backup нет. Так же необходимо знать, что в differential backup сохраняются изменения от последнего full backup, т.е. если мы в воскресенье сделали full backup, а в пн., вт., … сб. делаем differential backup, и хотим восстановить базу данных на пятницу (пятничную копию), то нам нужно будет восстанавливать базу из полной копии за воскресенье, и из разностной за пятницу, другие копии нам не понадобятся.
Предлагаю в SQL Server Management Studio (SSMS в дальнейшем) выполнить следующий скрипт, и далее мы будем разбирать пошагово этот скрипт:

-- разностная резервная копия
backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
with differential
go
-- добавляем файлы в нашу базу данных
alter database backup_test
add file
(
	name = N'backup_test2',
	filename = N'C:\test\backup_test2.ndf',
	size = 5Mb,
	filegrowth = 1Mb
)
to filegroup [primary]
go
alter database backup_test
add log file
(
	name = N'backup_test_log2',
	filename = N'C:\test\backup_test_log2.ldf',
	size = 512kb,
	filegrowth = 10%
)
go
-- еще одна разностная резервная копия
backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
with differential
go
-- а теперь полная резервная копия
backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
go
-- ну и еще одна разностная резервная копия
backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
with differential
go

Итак, у нас в нашем файле backup было 2 полных копии нашей базы данных и скриптом мы добавили еще разностную копию, добавили к оригинальной базе данных 2 файла (в файловую группу PRIMARY и в журнал транзакций по файлу), снова сделали разностную копию, затем полную копию и снова разностную. Вспоминаем как посмотреть содержимое нашего файла backup:

restore headeronly from disk = N'C:\test\backup_test_FULLBACKUP.bak'

Для наших исследований нам потребуются 2 табличных типа, предлагаю создать их для того, чтобы скрипты дальнейшие не были загружены большим перечнем полей. Единственное, тебе будет необходимо сверить количество и типы полей, выводимые командами RESTORE HEADERONLY/FILELISTONLY с полями, которые я предлагаю в табличных типах dbo.headeronlytype/filelistonlytype, здесь в разных версиях поля могут не совпадать, моя версия SQL на момент написания статьи – SQL Server 2014. Создадим наши типы (после перезагрузки сервера, база данных tempdb пере создастся и наши типы пропадут. Если тебе требуется, что бы эти типы сохранились, создай их в другой базе данных):

use tempdb
go

if not exists(select * from sys.types where name = 'headeronlytype')
create type dbo.headeronlytype as table
(
	 BackupName	nvarchar(128)
	,BackupDescription	nvarchar(255)
	,BackupType	smallint
	,ExpirationDate	datetime
	,Compressed	bit
	,Position	smallint
	,DeviceType	tinyint
	,UserName	nvarchar(128)
	,ServerName	nvarchar(128)
	,DatabaseName	nvarchar(128)
	,DatabaseVersion	int
	,DatabaseCreationDate	datetime
	,BackupSize	numeric(20,0)
	,FirstLSN	numeric(25,0)
	,LastLSN	numeric(25,0)
	,CheckpointLSN	numeric(25,0)
	,DatabaseBackupLSN	numeric(25,0)
	,BackupStartDate	datetime
	,BackupFinishDate	datetime
	,SortOrder	smallint
	,CodePage	smallint
	,UnicodeLocaleId	int
	,UnicodeComparisonStyle	int
	,CompatibilityLevel	tinyint
	,SoftwareVendorId	int
	,SoftwareVersionMajor	int
	,SoftwareVersionMinor	int
	,SoftwareVersionBuild	int
	,MachineName	nvarchar(128)
	,Flags	int
	,BindingID	uniqueidentifier
	,RecoveryForkID	uniqueidentifier
	,Collation	nvarchar(128)
	,FamilyGUID	uniqueidentifier
	,HasBulkLoggedData	bit
	,IsSnapshot	bit
	,IsReadOnly	bit
	,IsSingleUser	bit
	,HasBackupChecksums	bit
	,IsDamaged	bit
	,BeginsLogChain	bit
	,HasIncompleteMetaData	bit
	,IsForceOffline	bit
	,IsCopyOnly	bit
	,FirstRecoveryForkID	uniqueidentifier
	,ForkPointLSN	numeric(25,0) NULL
	,RecoveryModel	nvarchar(60)
	,DifferentialBaseLSN	numeric(25,0) NULL
	,DifferentialBaseGUID	uniqueidentifier
	,BackupTypeDescription	nvarchar(60)
	,BackupSetGUID	uniqueidentifier NULL
	,CompressedBackupSize	bigint
	,containment	tinyint not NULL
)
if not exists(select * from sys.types where name = 'filelistonlytype')
create type dbo.filelistonlytype as table
(
	 LogicalName	nvarchar(128)
	,PhysicalName	nvarchar(260)
	,Type	char(1)
	,FileGroupName	nvarchar(128)
	,Size	numeric(20,0)
	,MaxSize	numeric(20,0)
	,FileID	bigint
	,CreateLSN	numeric(25,0)
	,DropLSN	numeric(25,0)NULL
	,UniqueID	uniqueidentifier
	,ReadOnlyLSN	numeric(25,0) NULL
	,ReadWriteLSN	numeric(25,0)NULL
	,BackupSizeInBytes	bigint
	,SourceBlockSize	int
	,FileGroupID	int
	,LogGroupGUID	uniqueidentifier NULL
	,DifferentialBaseLSN	numeric(25,0)NULL
	,DifferentialBaseGUID	uniqueidentifier
	,IsReadOnly	bit
	,IsPresent	bit
	,TDEThumbprint	varbinary(32)
)

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

use tempdb
go
declare @headeronly dbo.headeronlytype

insert @headeronly exec ('restore headeronly from disk = N''C:\test\backup_test_FULLBACKUP.bak''')
select
	Position,
	DatabaseName,
	BackupType,
	BackupTypeDescription,
	FirstLSN,
	DatabaseBackupLSN

from @headeronly
order by Position

У меня получился следующий результат этого запроса:

Здесь, как ты наверняка уже догадался, я подсветил цветами LSN по совпадениям, которых можно отследить какой differential backup привязан к full backup. Как мы видим, мы можем восстановить full backup 2 и после него 3 или 4 разностный. На 2ю резервную копию мы не сможем накатить 6ю разностную копию, разные LSN. Поэтому важно понимать то, что, если мы имеем план резервного копирования базы данных, и не санкционированно кто-то из сотрудников сделал для себя полную копию базы данных (даже не умышленно, а не понимая как работают полные и разностные резервные копии. К примеру, тестировщик решил развернуть у себя на сервере свежую копию БД), которой у тебя нет, в случае потребности восстановления базы данных твоя разностная резервная копия вполне может не подойти к твоему full backup. Для обхода этой ситуации можно делать полную резервную копию с опцией WITH COPY_ONLY, опция позволяет сделать полную резервную копию базы данных, не влияя на последующие разностные резервные копии (backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak' with copy_only).
Пожалуй, настало время приступить к восстановлению наших баз данных из подготовленных резервных копий. Предлагаю восстановить дважды нашу базу данных до добавления файлов в БД (цепочка 2,3) с именем backup_test_3 и после добавления файлов в БД (цепочка 2,4) с именем backup_test_4. Выполняем скрипт восстановления 3 и 4 копий БД.

restore database backup_test_3 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 2, norecovery,
	move N'backup_test' to N'C:\test\backup_test_3.mdf',
	move N'backup_test_log' to N'C:\test\backup_test_log_3.ldf'

restore database backup_test_3 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 3
go

restore database backup_test_4 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 2, norecovery,
	move N'backup_test' to N'C:\test\backup_test_4.mdf',
	move N'backup_test_log' to N'C:\test\backup_test_log_4.ldf'

restore database backup_test_4 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 4, norecovery,
	move N'backup_test2' to N'C:\test\backup_test2_4.mdf',
	move N'backup_test_log2' to N'C:\test\backup_test_log2_4.ldf'

restore database backup_test_4 with recovery
go

Как ты, наверняка, заметил мы в скрипте используем опции восстановления NORECOVERY/RECOVERY (RECOVERY по умолчанию если не указываем NORECOVERY). Данная опция позволяет не выводить восстанавливаемую базу данных в ONLINE, что позволяет в этом состоянии продолжать пошаговое восстановление из следующих «не полных» резервных копий. Если мы переведем нашу базу данных в ONLINE, в этом случае другие резервные копии на этой базе данных мы накатить не сможем. Т.е. если мы случайно бы забыли при восстановлении full backup указать опцию NORECOVERY, то что бы восстановить цепочку из full + differential backup, нам бы пришлось удалить эту БД, подправить скрипт восстановления (добавить NORECOVERY) и заново повторить процесс восстановления базы данных.{/tabs}

BACKUP TRANSACTION LOG

Резервное копирование журнала транзакций (transaction log backup)
Прежде чем начать разбирать эту тему, нам необходимо понимать, что такое модель восстановления (REVOERY MODEL) базы данных. Итак, в SQL Server база данных может быть в одной из трех моделей восстановления:
1. Простая (SIMPLE)
2. Полная (FULL)
3. С неполным протоколированием (BULK_LOGGED)
Какая модель у баз данных мы можем выяснить запросом

select name, recovery_model_desc from sys.databases

Для обеспечения целостности транзакций, SQL Server в базах данных использует журнал транзакций (transaction log), т.е. любое изменение в базе данных записывается в журнал транзакций, при этом нумеруется 10 байтным уникальным номером – LSN (Log Sequential Number). По понятным причинам, каждая следующая операция имеет LSN больший нежели LSN предыдущей операции. За одну транзакцию SQL Server делает много операций, поэтому вы вряд ли увидите у двух идущих друг за другом сразу же транзакций одинаковый LSN. При FULL и BULK_LOGGED модели восстановления, журнал транзакций заполняется постоянно, до тех пор, пока не будет сделан backup transaction log. Как только сделана резервная копия журнала транзакций, все используемое место до момента BACKUP TRANSACTION LOG помечается «освобожденным» и следующие операции начинают писаться в это свободное место. При SIMPLE RECOVERY MODEL место освобождается сразу же по завершению транзакции, и следующая транзакция уже вполне может задействовать освобожденное место для своих нужд. Резервная копия журнала транзакций в принципе возможна только в том случае, если база данных в полной или с неполным протоколированием модели восстановления. Ну и упомяну еще то, что при FULL/BULK_LOGGED модели восстановления, после создания базы данных, журнал транзакции будет себя вести так же как в SIMPLE, до тех пор, пока кто-то не сделает первый FULL BACKUP DATABASE. В нагруженных серверах баз данных, когда транзакций проходит много, журнал транзакций будет расти быстро, и посему необходимо часто делать резервные копии журнала транзакций (конечно же если у тебя модель восстановления базы данных не SIMPLE).
Выполним трижды резервное копирование журнала транзакции нашей тестовой базы данных:

backup log backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
go 3

И затем еще несколько копий разных типов:

backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
go -- full backup
backup log backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
go -- backup log
backup database backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
with differential
go -- differential backup
backup log backup_test to disk = N'C:\test\backup_test_FULLBACKUP.bak'
go -- backup log

И посмотрим скриптом резервные копии в файле с backups:

use tempdb
go
declare @headeronly dbo.headeronlytype
insert @headeronly exec ('restore headeronly from disk = N''C:\test\backup_test_FULLBACKUP.bak''')
select
	Position,
	DatabaseName,
	BackupTypeDescription,
	FirstLSN,
	LastLSN,
	DatabaseBackupLSN
from @headeronly
order by Position

Здесь мы в результирующую выборку добавили поле LastLSN. Результат у меня получился такой:

Цветами отмечены совпадающие LSN и давай разбираться что это все означает. У резервных копий нам интересны FirstLSN и LastLSN они начальный и конечный LSN соответственно сохраненный в этой копии. Т.е. как ты уже видишь последующая резервная копия журнала транзакции привязывается не к последнему FULL BACKUP как было в случае DIFFERENTIAL BACKUP, а к предыдущему BACKUP LOG (за исключением самого первого BACKUP LOG – он привязан к самому первому LSN нашей базы данных, который сформировался в момент создания БД).
Восстановление из резервных копий журналов транзакций происходит следующим образом:
1. Восстанавливается нужный FULL BACKUP, например, последний №10
2. Восстанавливается нужный DIFFERENTIAL BACKUP, последний это №12 – он последний и привязан к полному десятому
3. И восстанавливаются все журналы транзакций сделаны после последнего восстановленного BACKUP, в нашем случае последний был №12 и нам остается восстановить TRANSACTION LOG BACKUP №13.
По сути эту же базу данных можно восстановить и без разностной резервной копии, тогда цепочка восстановления будет в нашем примере:
1. FULL BACKUP 10
2. LOG BACKUP 12
3. LOG BACKUP 13
Или вообще даже так:
1. FULL BACKUP 1
2. LOG BACKUP 7,8,9,11,13
Логику проверки цепочек LSN ты можешь отследить уже самостоятельно, у резервной копии журнала транзакции FirstLSN и EndLSN должны вмещать в себя LSN последней резервной копии если копия не журнала транзакций, или первый LSN текущей копии журнала транзакции должен быть равен последнему LSN предыдущей копии. Ну и не забываем добавлять WITH MOVE, когда добавили новые файлы в БД. Скрипт восстановления последнего примера предлагаю выполнить:

restore database backup_test_5 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 1, norecovery,
	move N'backup_test' to N'C:\test\backup_test_5.mdf',
	move N'backup_test_log' to N'C:\test\backup_test_log_5.ldf'

restore log backup_test_5 from
	disk = N'C:\test\backup_test_FULLBACKUP.bak'
with file = 7, norecovery,
	move N'backup_test2' to N'C:\test\backup_test2_5.mdf',
	move N'backup_test_log2' to N'C:\test\backup_test_log2_5.ldf'

restore log backup_test_5 from disk = N'C:\test\backup_test_FULLBACKUP.bak' with file = 8, norecovery
restore log backup_test_5 from disk = N'C:\test\backup_test_FULLBACKUP.bak' with file = 9, norecovery
restore log backup_test_5 from disk = N'C:\test\backup_test_FULLBACKUP.bak' with file = 11, norecovery
restore log backup_test_5 from disk = N'C:\test\backup_test_FULLBACKUP.bak' with file = 13, norecovery
restore database backup_test_5 with recovery

Здесь же имеет смысл сказать о том, что при восстановлении журнала транзакций, можно указать точку восстановления, т.е. не на время окончания этой резервной копии, а на любое время, конечно только то, в период которого были изменения и в backup transaction log это время вошло. Т.е. например если предыдущий пример мы бы хотели восстановить базу данных скажем на 15:22 20 января 2016 года. И как раз позиция 11 в нашем backup была до этого времени, а вот позиция 13 уже после этого времени то мы могли бы воспроизвести этот скрипт таким образом:

restore log backup_test_5 from disk = N'C:\test\backup_test_FULLBACKUP.bak' with file = 11, norecovery
restore log backup_test_5 from disk = N'C:\test\backup_test_FULLBACKUP.bak'
	with file = 13, norecovery, stopat = '2016-01-20 15:22'

Так же можно использовать при восстановлении из копии журнала транзакций опцию STOPATMARK где указывать следует имя помеченной ранее транзакции (STOPATMARK=’MYTRAN_155’), или LSN (STOPATMARK=’lsn: 34000000032400001’). Ну по LSN, честно говоря, я слабо вообще представляю ситуацию восстановления, а вот по имени транзакции, которую заранее можно сделать (например, пометить начало контрольных изменений, и в случае если изменения будут ошибочны, можно будет восстановить всю базу данных до этих изменений) вполне возможно.{/tabs}

RESTORE FROM DATABASE SNAPSHOT

Восстановление базы данных с моментального снимка
Предыдущую главу мы закончили упоминанием «запланированных изменений» в корректности которых нет 100% уверенности, и рассмотрели варианты восстановления журнала транзакций с метками или с привязкой ко времени. С версии SQL Server 2008 (и выше) появился механизм моментальных снимков базы данных. Моментальный снимок можно создавать на базу данных имеющую любую из возможных модель восстановления и FULL, BULK_LOGGED и SIMPLE. К нему можно делать connect и читать с него данные как с обычной базы данных, менять и писать данные в моментальный снимок нельзя. Моментальный снимок создается на всю БД, и при создании его необходимо указывать все файлы данных (не журнала транзакций), в которых флаг READONLY не выставлен (в нашей БД нет файлов «только для чтения»). Предлагаю на практике создать снимок, восстановить с него БД и удалить снимок после восстановления:

create database snapshot_backup_test on
(name=backup_test, filename=N'C:\test\backup_test.ss'),
(name=backup_test2, filename=N'C:\test\backup_test2.ss')
as snapshot of backup_test

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

restore database backup_test from database_snapshot = 'snapshot_backup_test'

drop database snapshot_backup_test

Вообще, формально мы можем использовать несколько моментальных снимков на одну и туже базу данных одновременно, и читать с них данные. Но… когда на текущий момент времени у БД больше одного снимка, восстановление произвести не получится, т.е. что бы восстановить БД на момент состояния моментального снимка, он для этой базы данных должен быть единственным. Также, огромный плюс восстановления из моментального снимка – это быстрое время восстановления. Т.е. что бы нам восстановить не мелкую БД, на определенный момент времени, нам необходимо восстановить полный backup, возможно еще разностный, и все нужные backup журнала транзакций. А с моментальным снимком, мы откатываем последние изменения со снимка, что по времени существенно быстрее.{/tabs}

BACKUP FILES AND FILEGROUPS

Сложные варианты резервного копирования и восстановления БД
Данная глава начинает быть актуальной тогда, когда размеры базы данных начинают быть просто огромными, и время полного резервного копирования, начинает быть неприемлемым (например, в моей в практике была база данных размером порядка 30 терабайтов и время FULL BACKUP занимало больше 7 дней), когда же мы имеем время полного backup приемлемое в окно обслуживания (к примеру, за ночь полная резервная копия проходит), то более чем достаточно схем резервного копирования, описанных в предыдущих главах.
Для начала предлагаю попробовать резервное копирование всех файлов (у нас их 2) в файловой группе, ну и начнем делать резервные копии в новый файл:

backup database backup_test
	file = 'backup_test'
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
backup log backup_test
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
backup database backup_test
	file = 'backup_test2'
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
backup log backup_test
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
backup database backup_test
	file = 'backup_test'
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
	with differential
backup log backup_test
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
backup database backup_test
	file = 'backup_test2'
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
	with differential
backup log backup_test
	to disk = N'C:\test\backup_test_COMPOSITE.bak'
go
use tempdb
go
declare @headeronly dbo.headeronlytype;
insert @headeronly exec ('restore headeronly from disk = N''C:\test\backup_test_COMPOSITE.bak''')
select
	Position,
	DatabaseName,
	BackupTypeDescription
from @headeronly
order by Position
go

Как ты видишь, мы сделали поочередно полные резервные копии каждого файла, затем разностные резервные копии этих же файлов, и между каждой полной или разностной копией добавили копию журнала транзакций. Теперь для восстановления нашей базы данных, нам не потребуется полная резервная копия всей базы данных. Нам достаточно полная (плюс разностная) копия каждого файла. Когда использовать или не использовать разностную копию ты уже сам начал понимать, зависит от того, на какой момент времени ты хочешь восстановить базу данных. На последний момент времени восстановление нашей базы данных будет выглядеть следующим образом:

restore database backup_test_6 from -- full backup_test
	disk = N'C:\test\backup_test_COMPOSITE.bak'
	with file = 1, norecovery,
	move N'backup_test' to N'C:\test\backup_test_6.mdf',
	move N'backup_test_log' to N'C:\test\backup_test_log_6.ldf',
	move N'backup_test2' to N'C:\test\backup_test2_6.mdf',
	move N'backup_test_log2' to N'C:\test\backup_test_log2_6.ldf'

restore database backup_test_6 from disk = N'C:\test\backup_test_COMPOSITE.bak' with file = 3, norecovery -- full backup_test2
restore database backup_test_6 from disk = N'C:\test\backup_test_COMPOSITE.bak' with file = 5, norecovery -- diff backup_test
restore database backup_test_6 from disk = N'C:\test\backup_test_COMPOSITE.bak' with file = 7, norecovery -- diff backup_test2
restore log backup_test_6 from disk = N'C:\test\backup_test_COMPOSITE.bak' with file = 6, norecovery -- tran захватывающий транзакции предыдущего бэкапа
restore log backup_test_6 from disk = N'C:\test\backup_test_COMPOSITE.bak' with file = 8, norecovery -- tran последний
restore database backup_test_6 with recovery

Как ты уже понял, пофайловая схема восстановления базы данных, возможна лишь при наличии резервных копий журнала транзакций, т.е. модель восстановления должна быть FULL или BULK_LOGGED (но не SIMPLE). Ну и таким образом мы можем делать резервные копии больших баз данных не круглосуточно несколько дней подряд, а, скажем, ночами в не рабочее время. В один день 1й файл, в следующий день 2й и так далее, пока не сделаем копию всей базы данных. Конечно не забываем в перерывах делать копии журнала транзакций. Так же можно делать копии не только файлов, но и файловых групп. В нашем случае мы пока имеем одну файловую группу PRIMARY, и backup, restore будет достаточно простой. Я предлагаю усложнить задачу и добавить к нашей базе данных еще 3 файловых группы, ну и сразу в каждой файловой группе создадим по таблице продажи, и немного заполним каждую таблицу:

alter database backup_test add filegroup fg2014
alter database backup_test add filegroup fg2015
alter database backup_test add filegroup fg2016

alter database backup_test add file (name=f2014,filename='C:\test\f2014.ndf') to filegroup fg2014
alter database backup_test add file (name=f2015,filename='C:\test\f2015.ndf') to filegroup fg2015
alter database backup_test add file (name=f2016,filename='C:\test\f2016.ndf') to filegroup fg2016

use backup_test
go
create table dbo.sales2014 (id int identity) on fg2014
create table dbo.sales2015 (id int identity) on fg2015
create table dbo.sales2016 (id int identity) on fg2016
go
insert dbo.sales2014 default values
insert dbo.sales2015 default values
insert dbo.sales2016 default values
go 100

Здесь мы создали для таблиц sales файловые группы 2014, 2015, 2016 – означающие продажи какие продажи были в этом году. Конечно, на практике, в таблицах будет более чем одно поле, но нам для понимания работы резервного копирования этого будет достаточно. Далее я предложу сделать файловые группы прошлых (не текущего года) 2014 и 2015 только для чтения:

alter database backup_test modify filegroup fg2014 read_only
alter database backup_test modify filegroup fg2015 read_only

Такая ситуация возможна довольно таки часто, когда мы не удаляем старые данные, но и менять их будем уже вряд ли. И плюс такой ситуации «огромен». Мы можем сделать резервную копию каждой файловой группы единожды, которая в режиме «только для чтения», быть уверенным, что эти backup у нас никуда не денутся – лежат где-то в надежном архиве. И при этом в последующих резервных копиях эти файлы не копировать совсем (конечно если ты или кто-то переведет файловую группу в read_write на минуточку, и после обратно в read_only наш план резервного копирования «сломается» – будет несовпадение цепочки LSN, точнее нам потребуются заново делать копию этой файловой группы). Предлагаю сделать резервные копии наших файловых групп, и посмотреть результат RESTORE HEADERONLY в нашем сделанном backup:

-- делаем разово резервные копии READ_ONLY файловых групп
backup log backup_test to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup database backup_test filegroup = 'fg2014' to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup database backup_test filegroup = 'fg2015' to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup log backup_test to disk = N'C:\test\backup_test_FILEGROUPS.bak'
-- можем сделать резервные копии недостающих READ_WRITE файловых групп
backup database backup_test filegroup = 'fg2016' to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup log backup_test to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup database backup_test filegroup = 'primary' to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup log backup_test to disk = N'C:\test\backup_test_FILEGROUPS.bak'
-- а может сделать копию используя опцию READ_WRITE_FILEGROUPS
backup database backup_test read_write_filegroups to disk = N'C:\test\backup_test_FILEGROUPS.bak'
backup database backup_test read_write_filegroups to disk = N'C:\test\backup_test_FILEGROUPS.bak' with differential
go

use tempdb
go

declare @headeronly dbo.headeronlytype;
declare @filelistonly dbo.filelistonlytype;
insert @headeronly exec ('restore headeronly from disk = N''C:\test\backup_test_FILEGROUPS.bak''')
declare crs cursor local fast_forward for
select Position from @headeronly
open crs
declare @pos int, @cmd varchar(8000)
while 1=1 begin
	fetch next from crs into @pos
	if @@fetch_status != 0 break
	set @cmd = 'restore filelistonly from disk = N''C:\test\backup_test_FILEGROUPS.bak'' with file='+convert(varchar,@pos)
	insert @filelistonly
	(
		 LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID
		,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes
		,SourceBlockSize,FileGroupID,LogGroupGUID,DifferentialBaseLSN
		,DifferentialBaseGUID,IsReadOnly,IsPresent,TDEThumbprint
	)
	exec (@cmd)
	update @filelistonly set Position = @pos where Position = 0
end
close crs deallocate crs

select
	h.Position,
	h.DatabaseName,
	h.BackupTypeDescription,
	f.FileGroupName,
	f.LogicalName,
	--f.ReadOnlyLSN,
	f.CreateLSN
	--f.DifferentialBaseLSN,
	--h.FirstLSN,
	--h.LastLSN
from @headeronly h
left join @filelistonly f
	on  h.Position = f.Position
	and f.BackupSizeInBytes > 0
order by h.Position, f.FileID
go

В последних 9,10 позициях мы видим новый BackupTypeDescription = Partial, который получился командой BACKUP DATABASE … READ_WRITE_FILEGROUPS. Теперь что бы восстановить нашу базу данных, так же, как и раньше – пошагово, нам необходимо будет сначала восстановить файловую группу PRIMARY, затем все остальные файловые группы, и последние журналы транзакций – 6,8.

-- восстановим PRIMARY
restore database backup_test_7 -- full backup_test
	 file = 'backup_test', filegroup = 'primary'
	,file = 'backup_test2', filegroup = 'primary'
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 7, norecovery
	,move N'backup_test'		to N'C:\test\backup_test_7.mdf'
	,move N'backup_test_log'	to N'C:\test\backup_test_log_7.ldf'
	,move N'backup_test2'		to N'C:\test\backup_test2_7.mdf'
	,move N'backup_test_log2'	to N'C:\test\backup_test_log2_7.ldf'

-- восстановим fg2014
restore database backup_test_7 -- full backup_test
	 file = 'f2014', filegroup = 'fg2014'
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 2, norecovery
	,move N'f2014'				to N'C:\test\f2014_7.ndf'
-- восстановим fg2015
restore database backup_test_7 -- full backup_test
	 file = 'f2015', filegroup = 'fg2015'
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 3, norecovery
	,move N'f2015'				to N'C:\test\f2015_7.ndf'
-- восстановим fg2016
restore database backup_test_7 -- full backup_test
	 file = 'f2016', filegroup = 'fg2016'
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 5, norecovery
	,move N'f2016'				to N'C:\test\f2016_7.ndf'


restore database backup_test_7 from disk = N'C:\test\backup_test_FILEGROUPS.bak' with file = 6, norecovery
restore database backup_test_7 from disk = N'C:\test\backup_test_FILEGROUPS.bak' with file = 8, norecovery

Вообще с READ_ONLY файловыми группами мы можем восстанавливать READ_WRITE файловые группы в состоянии RECOVERY (или убрать NORECOVERY или явно указать RECOVERY), и тогда наша база данных будет в ONLINE. Но вот именно в нашем случае у нас 2 файловые группы могут использоваться для записи, и по файловым группам нам так восстановиться не получится, т.к. нужно будет восстанавливать дополнительно копии журналов транзакций.
Последний из предлагаемых в этом обзоре способов восстановления базы данных это восстановление из PARTIAL копии – 9 (из 10 уже мы не сможем так восстановить базу данных, по причине того, чтобы ее накатить на 9ю нам потребуется 9ю оставить в состоянии norecovery, а так не получится наше online восстановление). Итак, восстанавливаем файловые группы, которые доступны на запись, и пробуем читать данные из таблиц sales2016, sales2015:

restore database backup_test_8 read_write_filegroups-- full backup_test
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 9, recovery
	,move N'backup_test'		to N'C:\test\backup_test_8.mdf'
	,move N'backup_test_log'	to N'C:\test\backup_test_log_8.ldf'
	,move N'backup_test2'		to N'C:\test\backup_test2_8.mdf'
	,move N'backup_test_log2'	to N'C:\test\backup_test_log2_8.ldf'
go
select top(10) * from backup_test_8.dbo.sales2016
go
select top(10) * from backup_test_8.dbo.sales2015
go

Как ты видишь, таблица sales2016 уже доступна для чтения (и записи), а вот таблица еще не в восстановленной файловой группе fg2015 не доступна нам для чтения. Т.е. после аварии мы можем восстановить небольшого объема оперативные данные – read_write filegroups и база данных доступна для работы (конечно же если приложение пользовательское сможет обрабатывать такую ситуацию – например реагировать на ошибки, когда доступ в READ_ONLY FILEGROUPS временно заблокирован). Продолжаем наше восстановление:

-- восстановим fg2015
restore database backup_test_8 -- full backup_test
	 file = 'f2015', filegroup = 'fg2015'
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 3, recovery
	,move N'f2015'				to N'C:\test\f2015_8.ndf'
go
select top(10) * from backup_test_8.dbo.sales2016
go
select top(10) * from backup_test_8.dbo.sales2015
go
select top(10) * from backup_test_8.dbo.sales2014
go

После этой операции мы так же продолжаем работать с нашей базой данных, плюс ко всему у нас стала доступна таблица sales2015. Ну и восстановим для завершения последний файл:

-- восстановим fg2014
restore database backup_test_8 -- full backup_test
	 file = 'f2014', filegroup = 'fg2014'
from disk = N'C:\test\backup_test_FILEGROUPS.bak'
with file = 2, recovery
	,move N'f2014'				to N'C:\test\f2014_8.ndf'
go
select top(10) * from backup_test_8.dbo.sales2016
go
select top(10) * from backup_test_8.dbo.sales2015
go
select top(10) * from backup_test_8.dbo.sales2014
go

Мы полностью восстановили нашу базу данных, которая могла быть очень большой, и время восстановления полностью могло бы занимать не один день (неделю). А таким образом мы после аварии вывели нашу БД в online очень быстро, и продолжали восстанавливать нашу БД.