Архивиране SQL Server база данни с нашето пълно ръководство за 2025 г. Подробни инструкции и най-добри практики за всички нива на умения.
1. Въведение в SQL Server Архивиране
1.1 Какво е SQL Server Резервно копие?
SQL Server Архивирането е процес на създаване на копия на файловете на вашата база данни, за да се предпази от загуба на данни. Архивирането улавя състоянието на вашата база данни в определен момент, което ви позволява да възстановите данните, ако възникнат хардуерни повреди, човешки грешки или бедствия.
SQL Server съхранява резервни копия в .bak файлове по подразбиране, които съдържат всички обекти на базата данни, включително таблици, съхранени процедури, изгледи, индекси и регистрационни файлове на транзакции.
1.2 Защо SQL Server Архивирането е критично
Архивирането на базата данни служи като последна линия на защита срещу загуба на данни. Без подходящи архиви, вашата организация рискува:
- Трайна загуба на данни от хардуерни повреди или повреда
- Удължен престой по време на опитите за възстановяване
- Прекъсване на бизнеса и загуба на приходи
- Нарушения на съответствието ако данните не могат да бъдат възстановени
- Увреждане на репутацията от прекъсвания на услугите
Редовен SQL Server Резервните копия осигуряват непрекъснатост на бизнеса и отговарят на регулаторните изисквания за защита на данните.
1.3 Често срещани сценарии за загуба на данни
Разбирането кога се случва загуба на данни ви помага да разработите ефективна стратегия за архивиране:
- Хардуерни повреди: Сривове на дискове, неизправности на сървъри или повреди в системата за съхранение
- Човешки грешки: Случайни изтривания, неправилни актуализации или премахнати таблици
- Софтуерни проблеми: Грешки в приложенията, повредени актуализации или системни сривове
- Пробиви в сигурността: Атаки с ransomware, злонамерени изтривания или неоторизиран достъп
- Природни бедствия: Пожари, наводнения или прекъсвания на електрозахранването, засягащи центрове за данни
2. разбиране SQL Server Типове резервни копия
SQL Server поддържа множество типове архивиране, всеки от които обслужва различни нужди от възстановяване и изисквания за съхранение.
2.1 Пълно архивиране
Пълното архивиране създава пълно копие на цялата ви база данни, включително всички файлове с данни и част от дневника на транзакциите, необходими за възстановяване.
2.1.1 Кога да използвате пълно архивиране
Пълните резервни копия са идеални за:
- Установяване на базова линия за други видове архивиране
- Малки до средни бази данни, където времето за архивиране е приемливо
- Седмични или месечни графици за архивиране
- Бази данни с редки промени
2.1.2 Предимства и ограничения на пълното архивиране
Предимства:
- Най-лесният процес на възстановяване – един файл съдържа всичко
- Самостоятелен и независим от други резервни копия
- Най-бързо време за възстановяване за пълно възстановяване на базата данни
Ограничения:
- Изисква значително място за съхранение
- По-дълго време за архивиране на големи бази данни
- По-висока консумация на ресурси по време на операции по архивиране
2.2 Диференциално архивиране
Диференциалното архивиране улавя само промените в данните след последното пълно архивиране, което намалява времето за архивиране и изискванията за съхранение.
2.2.1 Как работи диференциалното архивиране
Диференциалните резервни копия проследяват модификациите, използвайки променени екстенти. Когато възстановявате, SQL Server първо прилага последното пълно архивиране, след което прилага most скорошно диференциално архивиране.
2.2.2 Пълно срещу диференциално архивиране
| Аспект | Пълен архив | Диференциално резервно копие |
|---|---|---|
| Размер | Пълна база данни | Само промени след последното пълно архивиране |
| Време за архивиране | Най-дълго | По-бързо от пълно |
| Процес на възстановяване | Възстановяване на един файл | Изисква пълен + диференциал |
| Изисква се съхранение | Most пространство | По-малко място в началото, расте с времето |
2.3 Архивиране на дневника на транзакциите
Архивирането на регистрационните файлове на транзакциите улавя всички транзакции от последното архивиране на регистрационните файлове, което позволява възстановяване към определен момент.
2.3.1 Разбиране на регистрационните файлове на транзакциите
Журналът на транзакциите записва всяка промяна във вашата база данни. Резервните копия на логовете отрязват неактивната част от лога, предотвратявайки неограниченото му нарастване и запълване на диска.
2.3.2 Възстановяване към определен момент
Архивирането на регистрационните файлове на транзакциите ви позволява да възстановите базата данни до всеки конкретен момент в рамките на резервното копие на регистрационния файл. Това е от решаващо значение за възстановяване от случайни промени или изтривания на данни.
За да извършите възстановяване в определен момент, е необходимо:
- Последното пълно резервно копие
- Мost скорошно диференциално архивиране (по избор)
- Всички резервни копия на регистрационните файлове на транзакциите от пълния/диференциалния архив до вашия tarнамерете време
2.4 Архивиране на опашния лог
Архивирането на опашни записи от логове улавя записи от логове, които все още не са архивирани, предотвратявайки загуба на данни и поддържайки непокътната верига от логове. Преди възстановяване на SQL Server За да възстановите базата данни до най-актуалния момент, трябва да направите резервно копие на лог файла на транзакциите. Архивирането на лог файла е последното резервно копие, което представлява интерес в плана за възстановяване на базата данни.
Забележка: Не всички сценарии за възстановяване изискват архивиране на последното копие на лога. Не е необходимо такова, ако точката на възстановяване се съдържа в по-ранно архивиране на лога. Архивирането на последното копие на лога също не е необходимо, ако премествате или замествате (презаписвате) база данни и не е необходимо да я възстановявате до момент след нейното преместване.ost скорошно резервно копие.
2.4.1 Кога са необходими резервни копия на опашния лог
Следните сценарии описват кога трябва да направите резервно копие на опашния лог:
Възстановяване на онлайн база данни: Ако базата данни е онлайн и планирате да извършите операция по възстановяване на базата данни, започнете с архивиране на края на лога. За да избегнете грешка за онлайн база данни, трябва да използвате опцията WITH NORECOVERY на оператора BACKUP Transact-SQL, когато архивирате. SQL Server база данни.
Възстановяване на офлайн база данни: Ако базата данни е офлайн и не успее да сеtarи трябва да възстановите базата данни, първо архивирайте края на лога. Тъй като в този момент не могат да се извършват транзакции, използването на опцията WITH NORECOVERY е по избор. NORECOVERY е ефективно същото като архивиране само с копиране на лога на транзакциите в този сценарий.
Повредено архивиране на базата данни: Ако базата данни е повредена, опитайте да направите резервно копие на последното копие на лога, като използвате опцията WITH CONTINUE_AFTER_ERROR на оператора BACKUP. В повредена база данни, архивирането на последното копие на лога може да бъде успешно само ако лог файловете са неповредени, базата данни е в състояние, което поддържа архивиране на последното копие на лога, и базата данни не съдържа никакви групово регистрирани промени. Ако не може да се създаде резервно копие на последното копие на лога, всички транзакции, извършени след последната MS... SQL Server резервните копия на базата данни са lost.
2.4.2 Ключови опции за архивиране на опашни логове
С НОРЕКОВЪРСТВО: Използвайте WITH NORECOVERY, ако архивирате остатъка от лог на онлайн база данни, която планирате впоследствие да възстановите. NORECOVERY изключва базата данни от интернет. Можете също така да архивирате SQL Server лог на офлайн база данни. Ако искате да оставите базата данни офлайн, използвайте WITH NORECOVERY. Обърнете внимание, че логът ще бъде съкратен, освен ако не укажете опцията COPY_ONLY или NO_TRUNCATE.
С ПРОДЪЛЖАВАНЕ СЛЕД ГРЕШКА: Използвайте CONTINUE_AFTER_ERROR само ако архивирате опашката на повредена база данни. Когато архивирате опашката на лог на повредена база данни, някои от метаданните, обикновено записвани в резервните копия на логовете, може да не са налични.
2.5 Архивиране само с копиране
Резервните копия само с копиране създават независим архив, без да засягат нормалната последователност на архивиране. Те не нарушават диференциалните вериги за архивиране или непрекъснатостта на журнала на транзакциите.
Използвайте резервни копия само за копиране за:
- Създаване на тестови или развойни копия на база данни
- Ad-hoc архивиране без да се засягат планираните архиви
- Архивиране преди големи промени или тестване
2.6 Архивиране на файлове и файлови групи
Архивиране на файлове и файлови групи tarполучаване на конкретни файлове или файлови групи от базата данни, а не на цялата база данни. Този подход работи добре за много големи бази данни, където архивирането на всичко отнема твърде много време.
Ползите включват:
- По-бързи операции по архивиране на големи бази данни
- Паралелно архивиране на множество файлови групи
- Опции за гранулирано възстановяване
- Оптимизирани графици за архивиране за файлови групи само за четене
2.7 Частично архивиране
Частичните резервни копия включват всички данни в основната файлова група и всички файлови групи само за четене и запис, с изключение на файловите групи само за четене. Това намалява размера и времето за архивиране за бази данни със статични исторически данни, съхранявани във файлови групи само за четене.
3. SQL Server Модели за възстановяване
SQL Server Моделите за възстановяване определят кои типове резервни копия са налични и как се управляват регистрационните файлове на транзакциите.
3.1 Опростен модел за възстановяване
3.1.1 Характеристики и случаи на употреба
Простото възстановяване автоматично съкращава дневника на транзакциите след всяка контролна точка, освобождавайки място без да е необходимо архивиране на дневника.
Най-доброто за:
- Бази данни за разработка и тестване
- Бази данни, при които загубата на данни между резервните копия е приемлива
- Хранилища за данни с ETL процеси, които могат да се изпълняват повторно
- Бази данни само за четене или отчети
3.1.2 Налични опции за архивиране
Лесното възстановяване поддържа:
- Пълни резервни копия
- Диференциални резервни копия
- Архивиране на файлове и файлови групи
- Резервни копия само за копиране
Резервните копия на регистрационните файлове на транзакциите са не е наличен в опростен модел на възстановяване.
3.2 Модел на пълно възстановяване
3.2.1 Характеристики и предимства
Пълното възстановяване регистрира всички транзакции и запазва записите от лога, докато не ги архивирате. Това позволява пълно възстановяване на данните до всеки момент от времето в рамките на резервното копие на лога на транзакциите.
Ключови ползи:
- Минимален потенциал за загуба на данни
- Възможност за възстановяване в определен момент
- Поддържа доставка на лог файлове и огледално копиране на база данни
- Максимална гъвкавост при възстановяване
3.2.2 Управление на дневника на транзакциите
При пълно възстановяване е необходимо редовно да се извършват резервни копия на регистрационния файл на транзакциите, за да се:
- Предотвратяване на запълването на дисково пространство от дневника на транзакциите
- Поддържайте непрекъсната верига за архивиране
- Активиране на възстановяването на момента
- Контрол на растежа на лог файловете
Типичен график за архивиране: пълни архиви седмично, диференциални архиви ежедневно, архивиране на лог файлове на всеки 15-30 минути.
3.3 Модел за възстановяване чрез групово регистриране
3.3.1 Кога да се използва групово регистриране
Възстановяването с групово регистриране регистрира минимално групови операции като BULK INSERT, SELECT INTO и преизграждане на индекси, като същевременно поддържа пълно регистриране за редовни транзакции.
Използвайте групово регистрирано възстановяване, когато:
- Извършване на големи групови операции за импортиране
- Преизграждане на индекси на големи таблици
- Изпълнение на операции, които се възползват от минимално регистриране
- Необходимост от намаляване на размера на дневника на транзакциите по време на специфични операции
3.3.2 Ограничения и съображения
Важни ограничения:
- Възстановяването към определен момент не е налично по време на групови операции.
- Резервните копия на лог файловете са по-големи при извършване на групови операции
- Трябва да превключвате между пълно и групово регистриране, ако е необходимо
3.4 Избор на правилния модел за възстановяване
Изберете вашия модел за възстановяване въз основа на бизнес изискванията:
| Модел на възстановяване | Риск от загуба на данни | Възстановяване в даден момент | Най-добър за |
|---|---|---|---|
| Прост | Промени от последното архивиране | Не | Разработка/тест, приемлива загуба на данни |
| Пълен | Минимално (обикновено минути) | Да | Производствени бази данни, критични данни |
| Групово регистрирани | Промени от последното архивиране на лог файлове | Ограничено по време на групови операции | темпоrarизползвайте по време на операции в насипно състояние |
4. резервно копие SQL Server База данни, използваща SSMS
4.1 Предварителни изисквания и подготовка
Преди да архивирате вашите SQL Server база данни, уверете се, че:
- Имате съответните разрешения (db_owner или разрешение за РЕЗЕРВНО КОПИЕ НА БАЗАТА ДАННИ)
- Достатъчно дисково пространство за резервния файл
- SQL Server Инсталирано е Management Studio (SSMS)
- Мрежови пътища, достъпни при архивиране в мрежови местоположения
4.2 Стъпка по стъпка: Пълно архивиране със SSMS
Следвайте тези стъпки, за да създадете пълно резервно копие на вашите SQL Server база данни, използваща SSMS.
4.2.1 Отваряне SQL Server Студио за управление
- Стартиране SQL Server Студио за управление
- Въведете името на вашия сървър в Име на сървъра област
- Изберете метода си за удостоверяване
- Кликнете Свързване
4.2.2 Избор на опции за база данни и архивиране
- In Изследовател на обекти, разширете Данни възел
- Щракнете с десния бутон върху базата данни, която искате да архивирате
- Изберете Задачи -> Назад
- В Архивиране на базата данни прозорец, проверете името на базата данни
- Изберете Пълен като Тип архивиране
4.2.3 Конфигуриране на местоназначение за архивиране
- Под Дестинация, Щракнете върху премахни, за да изчистите пътя по подразбиране (ако е необходимо)
- Кликнете върху ДОБАВЯНЕ, за да посочите ново място за архивиране
- Въведете пътя и името на файла с .bak разширение
- Кликнете OK за да потвърдите дестинацията
4.2.4 Advanced Backup на Настройки.
- Кликнете Опции за медии в левия панел
- Изберете опции за архивиране:
- Презапишете всички съществуващи резервни копия – Заменя съществуващите резервни копия
- Добавяне към съществуващ набор от резервни копия – Добавя към съществуващ архивен файл
- Кликнете Опции за архивиране в левия панел
- Конфигурирайте допълнителни настройки:
- Компресиране на резервно копие – Намалява размера на резервните файлове
- Шифроване на архивиране – Защитава чувствителни данни
- Проверете резервното копие, когато сте готови – Проверява целостта на резервните копия
4.2.5 Изпълнение на архивирането
- Прегледайте всички настройки в Архивиране на базата данни прозорец
- Кликнете OK до starпроцеса на архивиране
- Изчакайте архивирането да завърши
- Съобщение за успех се появява, когато архивирането приключи
- Кликнете OK за да затворите диалоговия прозорец за потвърждение
4.3 Създаване на диференциално архивиране със SSMS
За да създадете диференциално архивиране, следвайте същите стъпки като за пълно архивиране, но изберете Диференциал като тип резервно копие в стъпка 4.2.2. Не забравяйте, че диференциалните резервни копия изискват предварително пълно резервно копие като базова линия.
4.4 Създаване на резервно копие на регистрационния файл на транзакциите със SSMS
Архивирането на регистрационните файлове на транзакциите е достъпно само за бази данни, използващи модели за пълно или групово регистриране на възстановяване.
- Щракнете с десния бутон върху базата данни в Изследовател на обекти
- Изберете Задачи -> Назад
- Изберете Дневник на транзакциите като тип резервно копие
- Конфигурирайте дестинацията и опциите, ако е необходимо
- Кликнете OK за създаване на резервно копие на лога
4.5 Създаване на резервно копие само за копиране с SSMS
Резервните копия само за копиране не пречат на обичайната ви последователност за архивиране.
- Следвайте стъпките за създаване на пълен резервен файл
- В Опции за архивиране страница
- Проверете Архивиране само с копиране опция
- Завършете процеса на архивиране по обичайния начин
5. резервно копие SQL Server База данни, използваща T-SQL
5.1 Основен синтаксис на резервната база данни
Командата T-SQL BACKUP DATABASE осигурява програмен контрол върху SQL Server архиви.
BACKUP DATABASE database_name
TO DISK = 'backup_file_path'
WITH options;5.2 Пълно архивиране на T-SQL команди
5.2.1 Прост скрипт за пълно архивиране
Създайте основен пълен архив с минимални опции:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
GO5.2.2 Пълно архивиране с опции
Добавете описателна информация и опции за форматиране:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH FORMAT,
INIT,
NAME = 'AdventureWorks-Full Database Backup',
DESCRIPTION = 'Full backup of AdventureWorks database',
STATS = 10
GOОбяснение на опциите:
- ФОРМАТ – Създава нов набор от резервни копия
- INIT – Презаписва съществуващия резервен файл
- NAME – Присвоява име на резервен набор
- ОПИСАНИЕ – Добавя описателен текст
- СТАТИСТИКА – Показва напредък на всеки 10%
5.3 T-SQL команди за диференциално архивиране
Диференциалните резервни копия използват опцията DIFFERENTIAL:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH DIFFERENTIAL,
INIT,
NAME = 'AdventureWorks-Differential Backup',
STATS = 10
GO5.4 T-SQL команди за архивиране на регистрационни файлове на транзакции
Използвайте BACKUP LOG за архивиране на регистрационни файлове на транзакции:
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH INIT,
NAME = 'AdventureWorks-Transaction Log Backup',
STATS = 10
GO5.5 Разширени опции за архивиране на T-SQL
5.5.1 Архивиране на множество файлове
Разпределете резервното копие между множество файлове за по-бърза производителност:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_1.bak',
DISK = 'D:\Backups\AdventureWorks_2.bak',
DISK = 'E:\Backups\AdventureWorks_3.bak'
WITH FORMAT, INIT
GO5.5.2 Архивиране с компресия
Намалете размера на резервните файлове и мрежовата честотна лента:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Compressed.bak'
WITH COMPRESSION,
INIT,
STATS = 10
GO5.5.3 Архивиране с криптиране
Защитете чувствителните данни с криптиране:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Encrypted.bak'
WITH COMPRESSION,
ENCRYPTION (
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCertificate
),
STATS = 10
GO5.5.4 Архивиране със защита с парола
Добавете защита с парола (отхвърлено, използвайте криптиране):
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH PASSWORD = 'StrongPassword123!',
INIT
GO5.5.5 Огледални резервни копия
Създавайте едновременни копия на различни места:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
MIRROR TO DISK = 'D:\Backups\AdventureWorks_Mirror.bak'
WITH FORMAT, INIT
GO5.6 Примери и скриптове за архивиране в T-SQL
Пълен скрипт за архивиране с обработка на грешки:
DECLARE @BackupPath NVARCHAR(500);
DECLARE @DatabaseName NVARCHAR(128) = 'AdventureWorks';
DECLARE @BackupDate NVARCHAR(20);
SET @BackupDate = CONVERT(NVARCHAR(20), GETDATE(), 112);
SET @BackupPath = 'C:\Backups\' + @DatabaseName + '_' + @BackupDate + '.bak';
BEGIN TRY
BACKUP DATABASE @DatabaseName
TO DISK = @BackupPath
WITH COMPRESSION,
INIT,
NAME = @DatabaseName + '-Full Backup',
STATS = 10;
PRINT 'Backup completed successfully: ' + @BackupPath;
END TRY
BEGIN CATCH
PRINT 'Backup failed: ' + ERROR_MESSAGE();
END CATCH
GO6. резервно копие SQL Server База данни с помощта на PowerShell
6.1 Командлети за архивиране в PowerShell
SQL Server Модулът PowerShell предоставя командлети за автоматизация на архивирането:
- Архивиране на SQLDatabase – Създава резервни копия на базата данни
- Възстановяване на SQLDatabase – Възстановява резервни копия на базата данни
- Get-SqlDatabase – Извлича информация от базата данни
Импортирайте SQL Server модул:
Import-Module SqlServer6.2 Създаване на скриптове за архивиране с PowerShell
Основна команда за архивиране в PowerShell:
Backup-SqlDatabase -ServerInstance "localhost" `
-Database "AdventureWorks" `
-BackupFile "C:\Backups\AdventureWorks.bak" `
-BackupAction Database `
-CompressionOption OnПример за диференциално архивиране:
Backup-SqlDatabase -ServerInstance "localhost" `
-Database "AdventureWorks" `
-BackupFile "C:\Backups\AdventureWorks_Diff.bak" `
-BackupAction Database `
-IncrementalАрхивиране на дневника на транзакциите:
Backup-SqlDatabase -ServerInstance "localhost" `
-Database "AdventureWorks" `
-BackupFile "C:\Backups\AdventureWorks_Log.trn" `
-BackupAction Log6.3 Автоматизиране на архивирането с PowerShell
Създайте автоматизиран скрипт за архивиране на множество бази данни:
# Configuration
$ServerInstance = "localhost"
$BackupPath = "C:\Backups"
$Databases = @("AdventureWorks", "TestDB", "ProductionDB")
$Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
# Create backup directory if not exists
if (-not (Test-Path $BackupPath)) {
New-Item -ItemType Directory -Path $BackupPath
}
# Backup each database
foreach ($Database in $Databases) {
$BackupFile = Join-Path $BackupPath "$Database`_$Timestamp.bak"
try {
Backup-SqlDatabase -ServerInstance $ServerInstance `
-Database $Database `
-BackupFile $BackupFile `
-BackupAction Database `
-CompressionOption On
Write-Host "Successfully backed up $Database to $BackupFile" -ForegroundColor Green
}
catch {
Write-Host "Failed to backup $Database : $_" -ForegroundColor Red
}
}
7. резервно копие SQL Server База данни с помощта на команден ред
SQL Server предоставя помощни програми от командния ред, които ви позволяват да архивирате SQL Server база данни без използване на SSMS или графични интерфейси. Тези инструменти са от съществено значение за сценарии за автоматизация, скриптове и дистанционно администриране.
7.1 Използване на резервна база данни на SQLCMD
SQLCMD е съвременната помощна програма за команден ред за SQL Server който замени OSQL. Той предоставя подобрена функционалност и е препоръчителният инструмент за изпълнение на T-SQL команди от командния ред.
7.1.1 Основен синтаксис на SQLCMD
sqlcmd -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"- -С: Указва SQL Server име на екземпляр
- -д: Указва името на базата данни
- -В: Изпълнява заявка и излиза
- -И: Използва удостоверяване на Windows
- -U: Указва SQL Server потребителско име за вход
- -П: Указва парола за SQL Server Влизане
7.1.2 Създаване на резервно копие с SQLCMD
За архивиране SQL Server използвайки SQLCMD, следвайте тези стъпки:
- Отворете Command Prompt or PowerShell
- Придвижете се до SQL Server директория с инструменти (обикновено се добавя към PATH по време на инсталацията)
- Изпълнете командата за архивиране на базата данни SQLCMD с подходящи параметри
- Проверете дали резервният файл е създаден успешно
Примерна команда за пълно архивиране с помощта на удостоверяване на Windows:
sqlcmd -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH COMPRESSION, INIT"Пример с използване SQL Server Authentication:
sqlcmd -S localhost -U sa -P YourPassword -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH COMPRESSION, INIT"Създаване на диференциално архивиране с SQLCMD
sqlcmd -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL, COMPRESSION, INIT"Създаване на резервно копие на регистрационния файл на транзакциите с SQLCMD
sqlcmd -S localhost -E -Q "BACKUP LOG AdventureWorks TO DISK='C:\Backups\AdventureWorks_Log.trn' WITH COMPRESSION, INIT"7.1.3 Архивиране на базата данни на издателя в SQL Server копиране
При архивиране на бази данни на издатели в SQL Server Репликация, използвайте опцията WITH REPLICATION, за да запазите метаданните за репликация и да осигурите съгласуваност на транзакциите.
-- Backup publisher database with replication support
BACKUP DATABASE PublisherDB
TO DISK = 'C:\Backup\PublisherDB_Full.bak'
WITH REPLICATION,
COMPRESSION,
CHECKSUM,
INIT,
STATS = 10;
GOЗа повече подробности относно SQL Server репликация, вижте нашата изчерпателно ръководство.
7.2 Използване на OSQL резервна база данни
OSQL е остаряла помощна програма за команден ред за SQL ServerВъпреки че Microsoft препоръчва използването на SQLCMD, OSQL остава достъпен за обратна съвместимост с по-стари скриптове и системи.
7.2.1 Основен OSQL синтаксис
Синтаксисът на OSQL е подобен на SQLCMD:
osql -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"- -С: SQL Server име на екземпляр
- -д: Име на базата данни
- -В: Изпълнява заявка и излиза
- -И: Използва надеждна връзка (удостоверяване на Windows)
- -U: Потребителско име за вход
- -П: Парола за вход
7.2.2 Създаване на резервно копие с OSQL
За да извършите операции за архивиране на база данни OSQL:
- Отворете Command Prompt
- Проверете дали OSQL е наличен във вашия SQL Server инсталация
- Изпълнете командата за архивиране на OSQL
Пример за пълно архивиране:
osql -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH INIT"Примерно диференциално архивиране:
osql -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL, INIT"8. Трета страна SQL Server Инструменти за архивиране
Докато SQL Server Включва вградени възможности за архивиране, инструментите на трети страни предлагат подобрени функции, автоматизация и управление от корпоративен клас за организации със сложни изисквания. Тези решения осигуряват разширено компресиране, централизирано управление и рационализирани работни процеси за архивиране. SQL Server бази данни в множество среди.
8.1 Veeam Backup за SQL Server
Veeam предлага цялостни решения за защита на данните, специално разработени за архивиране SQL Server бази данни с минимално въздействие върху производствените системи.
Основни функции:
- Обработка, съобразена с приложенията, за SQL Server консистентност на резервните копия
- Архивиране и управление на дневника на транзакциите
- Възстановяване в определен момент с опции за детайлно възстановяване
- Интеграция с Veeam Backup & Replication за унифицирана защита на данните
- Автоматизирана проверка и валидиране на резервни копия
- Поддръжка за групи за винаги включена наличност
- Ниво на виртуална машина и ниво на приложение SQL Server резервни опции
8.2 Резервно копие на Barracuda за SQL Server
Barracuda предоставя интегрирани в облака решения за архивиране с опростено управление за Microsoft SQL Server операции с резервно копие на базата данни.
Основни функции:
- Автоматизирана SQL Server планиране на резервно копие
- Вградена облачна репликация към Barracuda Cloud Storage
- Глобална дедупликация и компресия
- Възможности за незабавно локално възстановяване
- Уеб-базирана конзола за управление
- Поддръжка за пълни, диференциални и резервни копия на регистрационни файлове на транзакции
- Защита от рансъмуер с непроменяеми резервни копия
8.3 Veritas NetBackup за SQL Server
Veritas NetBackup е платформа за архивиране от корпоративен клас, осигуряваща цялостна защита за SQL Server бази данни в сложни ИТ среди.
Основни функции:
- Управление на архивирането в корпоративен мащаб за хиляди SQL Server случаи
- Усъвършенствани алгоритми за дедупликация и компресия
- Гъвкави политики за архивиране и планиране
- Поддръжка за всички SQL Server модели за възстановяване
- Интеграция с библиотеката за лентиrarи облачно съхранение
- Детайлно възстановяване на бази данни, таблици и обекти
- Поддръжка на множество платформи (Windows, Linux) SQL Server)
- Автоматизирано управление на жизнения цикъл на архивирането
8.4 Commvault Complete Backup & Recovery за SQL Server
Commvault предоставя интелигентно управление на данни с цялостно архивиране SQL Server възможности и разширени функции за автоматизация.
Основни функции:
- Оптимизация на архивирането и откриване на аномалии, управлявани от изкуствен интелект
- Унифицирана платформа за архивиране, възстановяване и архивиране
- Подробно SQL Server компресия на резервни копия (до 90% намаление)
- Автоматизирана оркестрация на възстановяване след бедствия
- Синхронизиране на живо за почти нулева RPO защита
- Подкрепа за SQL Server локални, облачни и хибридни внедрявания
- IntelliSnap за архивиране, базирано на моментни снимки
- Пълни възможности за съответствие и електронно откриване
8.5 Съгласуваност DataProtect за SQL Server
Cohesity осигурява управление на данни от следващо поколение с хиперконвергентна инфраструктура за модерни... SQL Server операции за архивиране.
Основни функции:
- Уеб-мащабна архитектура за опростено управление
- Възможности за незабавно масово възстановяване за SQL Server бази данни
- Съвместими с приложението моментни снимки
- Глобална дедупликация във всички резервни копия
- Нативна облачна интеграция (AWS, Azure, Google Cloud)
- Вградени табла за анализ и мониторинг
- Възможности за клониране и тестване на база данни
- Защита от рансъмуер с непроменяеми моментни снимки
8.6 Red Gate SQL Backup Pro
Red Gate SQL Backup Pro е специализиран инструмент, фокусиран изключително върху оптимизиране SQL Server операции по архивиране и възстановяване с превъзходна компресия и производителност.
Основни функции:
- Водещи в индустрията коефициенти на компресия (до 95%)
- Устойчивост на мрежата за архивиране SQL Server през ненадеждни връзки
- Криптиране на резервни копия с 256-битово AES
- Проверка на резервното копие и проверка на целостта
- Подробна история на архивирането и отчети
- Интеграция с SQL Server Студио за управление
- Поддръжка за архивиране в мрежови местоположения и облачно съхранение
- Паралелно архивиране и възстановяване за по-бързи операции
9. Как да възстановите SQL Server База данни
9.1 Разбиране на процеса на възстановяване
Възстановяване на a SQL Server Базата данни пресъздава базата данни от резервни файлове. Процесът на възстановяване прочита резервния файл и реконструира базата данни до нейното архивирано състояние.
Важни съображения:
- Възстановяването презаписва съществуващата база данни
- Потребителите са изключени по време на възстановяването
- Възстановяването трябва да следва последователността на архивиране (пълно, след това диференциално, накрая лог файлове)
- Базата данни не е налична по време на операцията по възстановяване
9.2 Възстановяване на пълно архивиране с помощта на SSMS
Следвайте тези стъпки, за да възстановите пълно архивиране на базата данни.
9.2.1 Процес на възстановяване стъпка по стъпка
- Отворете SQL Server Студио за управление и се свържете със сървъра си
- In Изследовател на обекти, Кликнете с десния бутон Данни
- Изберете Възстановяване на базата данни
- В източник раздел, изберете Приспособление
- Кликнете върху менюто ... бутон за преглед на резервни файлове
- Кликнете върху ДОБАВЯНЕ, и отидете до вашия .bak файл
- Изберете файла с резервно копие и щракнете OK
- В Дестинация раздел, въведете името на базата данни
- Прегледайте резервните копия за възстановяване
- Кликнете OK до starвъзстановяването
9.2.2 Опции и настройки за възстановяване
Кликнете Опции в левия панел, за да конфигурирате:
- Презаписване на съществуващата база данни (СЪС ЗАМЕСТВАНЕ) – Позволява възстановяване върху съществуваща база данни
- Запазване на състоянията на репликация (СЪС KEEP_REPLICATION) - Запази SQL Server копиране са
- Ограничаване на достъпа до възстановената база данни (С RESTRICTED_USER) – Граници post-възстановяване на достъпа
- Състояние на възстановяване – Изберете между ВЪЗСТАНОВЯВАНЕ С ВЪЗСТАНОВЯВАНЕ или БЕЗ ВЪЗСТАНОВЯВАНЕ
9.3 Възстановяване на диференциално архивиране
Диференциалното възстановяване изисква както пълни, така и диференциални резервни копия:
- Първо, възстановете пълното архивиране с НОРЕКОВЪРСТВО опция
- След това възстановете диференциалното архивиране с ВЪЗСТАНОВЯВАНЕ опция
Пример за T-SQL:
-- Restore full backup (NORECOVERY to allow differential)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;
-- Restore differential backup (RECOVERY to complete)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH RECOVERY;
GO9.4 Възстановяване с резервни копия на регистрационните файлове на транзакциите
За възстановяване към определен момент, възстановете последователно:
- Възстановете пълното архивиране с NORECOVERY
- Възстановяване на диференциално архивиране с NORECOVERY (ако е налично)
- Възстановете резервните копия на регистрационните файлове на транзакциите по ред с NORECOVERY
- Възстановете окончателното архивиране на лог файлове с RECOVERY
-- Restore full backup
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;
-- Restore first log backup
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH NORECOVERY;
-- Restore second log backup with recovery
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log2.trn'
WITH RECOVERY;
GO9.5 Възстановяване към определен момент
Възстановете базата данни до определен момент във времето, използвайки опцията STOPAT:
-- Restore to specific time: January 15, 2025 at 2:30 PM
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY, STOPAT = '2025-01-15 14:30:00';
GO9.6 Възстановяване на таблица
SQL Server не поддържа директно възстановяване на ниво таблица от резервни копия. Въпреки това, все още има някои решения.
9.6.1 Метод 1: Снимки на базата данни (най-подходящи за превенция)
Моментните снимки на базата данни предоставят най-бързия начин за възстановяване на таблични данни, ако са създадени преди възникването на проблема. Моментната снимка е статичен изглед на базата данни, достъпен само за четене, в определен момент от времето.
Създаване на моментна снимка на базата данни:
-- Create snapshot before making changes
CREATE DATABASE ProductionDB_Snapshot_20250107
ON
( NAME = ProductionDB_Data,
FILENAME = 'C:\Snapshots\ProductionDB_Snapshot.ss' )
AS SNAPSHOT OF ProductionDB;
GOВъзстановяване на таблични данни от моментна снимка:
USE ProductionDB;
GO
-- Replace entire table content
BEGIN TRANSACTION;
-- Disable constraints temporarily
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT ALL;
-- Clear current data
TRUNCATE TABLE dbo.Orders;
-- Restore from snapshot
INSERT INTO dbo.Orders
SELECT * FROM ProductionDB_Snapshot_20250107.dbo.Orders;
-- Re-enable constraints
ALTER TABLE dbo.Orders CHECK CONSTRAINT ALL;
COMMIT TRANSACTION;
GOИзисквания за версията: Моментните снимки на базата данни са налични в SQL Server Enterprise Edition (всички версии) и Standard Editiontarтинг с SQL Server 2016 SP1.
9.6.2 Метод 2: Възстановяване на темпотоrarБаза данни (Most Често срещани)
Този метод работи, когато е необходимо да възстановите данни от таблицата след възникване на проблем и няма съществуваща моментна снимка:
- Възстановяване на резервно копие до темпоrarбаза данни y
- Копиране на данни от таблицата от темпотоrarбаза данни y към текущата база данни
9.7 Възстановяване на страница
Възстановяването на страници възстановява отделни повредени страници, без да възстановява цялата база данни, като по този начин минимизира времето за престой. tarполучаване само на повредени страници. Тази функция е налична само при модели за пълно или групово възстановяване и изисква непрекъсната верига от резервни копия на регистрационните файлове на транзакциите от резервното копие на страницата до текущия регистрационен файл.
За да извършите възстановяване на страница, първо идентифицирайте повредените страници, направете резервно копие на последното копие на лог файловете, възстановете конкретните страници и след това приложете всички лог файлове на транзакциите:
-- Identify damaged pages
SELECT * FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID('AdventureWorks');
-- Take tail-log backup
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_TailLog.trn'
WITH NORECOVERY;
-- Restore damaged pages
RESTORE DATABASE AdventureWorks
PAGE = '1:123, 1:456'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;
-- Apply transaction logs
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH NORECOVERY;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_TailLog.trn'
WITH RECOVERY;
GOЗабележка: Възстановяването на страници не е налично в модела за опростено възстановяване. Не можете да възстановявате страници от системни таблици или метаданни на първична файлова група.
9.8 Възстановяване на части
Частичното възстановяване (Piecemeal restore) възстановява базата данни на етапи на ниво файлова група, т.е.tarс основната файлова група. Това незабавно пуска критични данни онлайн, докато по-малко критичните данни се възстановяват във фонов режим. При модела на просто възстановяване, всички файлови групи за четене и запис трябва да бъдат възстановени заедно с основната файлова група; само файлови групи само за четене могат да бъдат възстановени отделно. При моделите за пълно или групово възстановяване, всяка файлова група може да бъде пусната онлайн независимо след прилагане на регистрационни файлове на транзакции.
| Модел на възстановяване | Поведение при възстановяване на части |
|---|---|
| Прост | Основната и всички файлови групи за четене и запис са възстановени заедно. Файловите групи само за четене са възстановени поотделно. |
| Пълно/групово регистриране | Всяка файлова група се възстановява независимо на ниво файлова група. |
Пример за модел на пълно възстановяване – първо възстановете първичната файлова група, за да включите базата данни онлайн, след това възстановете вторичните файлови групи, докато базата данни остава оперативна:
-- Stage 1: Restore primary filegroup (database comes online)
RESTORE DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH PARTIAL, NORECOVERY;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH RECOVERY;
GO
-- Stage 2: Restore secondary filegroup (database stays online)
RESTORE DATABASE AdventureWorks
FILEGROUP = 'HistoricalData'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH RECOVERY;
GOПример за опростен модел на възстановяване:
-- Restore primary with all read-write filegroups
RESTORE DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH PARTIAL, RECOVERY;
-- Restore read-only filegroup separately
RESTORE DATABASE AdventureWorks
FILEGROUP = 'ReadOnlyArchive'
FROM DISK = 'C:\Backups\AdventureWorks_ReadOnly.bak'
WITH RECOVERY;
GO9.9 Възстановяване с помощта на T-SQL команди
Пълен скрипт за възстановяване с преместване на файлове:
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'D:\Data\AdventureWorks.mdf',
MOVE 'AdventureWorks_Log' TO 'E:\Logs\AdventureWorks.ldf',
REPLACE,
STATS = 10;
GO9.10 Проверка на целостта на резервното копие преди възстановяване
Проверете валидността на резервното копие без възстановяване:
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak';
GOТази команда проверява дали резервният набор е пълен и четим, без реално да възстановява базата данни.
10. SQL Server Най-добри практики за архивиране
10.1 Разработване на стратегия за архивиране
10.1.1 Оценка на бизнес изискванията
Преди да внедрите резервни копия, оценете:
- Критичност на данните: Колко важни са тези данни за операциите?
- Промяна на честотата: Колко често се променят данните?
- Размер на базата данни: Колко голяма е базата данни?
- Налични ресурси: Какво място за съхранение и трафик са налични?
- Изисквания за съответствие: Какви разпоредби трябва да спазвате?
10.1.2 Дефиниране на RTO и RPO
Целево време за възстановяване (RTO): Максимално приемливо време на престой. Определя колко бързо е необходимо да възстановите операциите.
Цел на точката на възстановяване (RPO): Максимално допустима загуба на данни. Определя честотата на архивиране.
| Изискване за RTO/RPO | Препоръчителна стратегия за архивиране |
|---|---|
| RPO: Часове, RTO: Часове | Пълен дневен + дневник на транзакциите на всеки 1-2 часа |
| RPO: Минути, RTO: Часове | Пълни и лог архиви на всеки 15-30 минути |
| RPO: Близо до нула, RTO: Минути | Винаги включени групи за наличност + чести архивирания на лог файлове |
| RPO: Дни, RTO: Дни | Седмична пълна сума + дневна разлика |
10.2 Създаване на график за архивиране
10.2.1 Препоръки за честота
Типичен график за архивиране на производствени бази данни:
- Пълни резервни копия: Седмично (неделя вечер по време на ниска активност)
- Диференциални резервни копия: Ежедневно (всяка вечер)
- Архивни копия на регистрационните файлове на транзакциите: На всеки 15-30 минути по време на работно време
- Резервни копия само за копиране: При необходимост за тестване или разработка
10.2.2 Балансиране между производителност и защита
Вземете предвид тези фактори при планирането:
- Ненатоварени часове: Изпълнявайте пълни резервни копия по време на периоди с ниска активност
- Въздействие на ресурсите: Компресията намалява I/O, но увеличава използването на процесора
- Пропускателна способност на мрежата: Планирайте мрежови архиви, когато трафикът е нисък
- Прозорци за архивиране: Уверете се, че резервните копия са завършени преди работно време
10.3 Най-добри практики за съхранение на резервни копия
10.3.1 Съхранение на място спрямо съхранение извън място
Резервни копия на място:
- По-бързо време за архивиране и възстановяване
- Долен cost за високочестотен достъп
- Уязвим към локални бедствия
- Най-подходящ за сценарии за бързо възстановяване
Резервни копия извън сайта:
- Защита от бедствия, специфични за обекта
- Спазване на изискванията за географско резервиране
- По-бавно време за възстановяване
- От съществено значение за възстановяване след бедствия
10.3.2 Опции за архивиране в облака
Предимства на облачното съхранение:
- Съхранение на Azure Blob: Роден SQL Server интеграция, cost-ефективен при рядък достъп
- Amazon S3: Високо издръжливи, гъвкави нива за съхранение
- Google Cloud Storage: Конкурентни цени, глобална наличност
10.3.3 Политики за съхранение на резервни копия
Политика за запазване на проби:
- Съхранявайте ежедневни резервни копия в продължение на 7 дни
- Съхранявайте седмичните резервни копия в продължение на 4 седмици
- Съхранявайте месечните резервни копия в продължение на 12 месеца
- Съхранявайте годишни резервни копия в продължение на 7 години (съответствие)
10.4 Компресиране и криптиране на резервни копия
Предимства на компресията:
- Намалява размера на резервните файлове с 50-70%
- Намалява времето за архивиране
- Намалява пространството за съхранение costs
- Намалява мрежовата честотна лента за отдалечени архиви
Най-добри практики за криптиране:
- Винаги криптирайте резервни копия, съдържащи чувствителни данни
- Използвайте 256-битово AES криптиране
- Сигурно управление на сертификати или ключове
- Документирайте ключовете за криптиране и ги съхранявайте отделно
10.5 Тестване и проверка на резервни копия
10.5.1 Редовно тестване за възстановяване
Процедури за тестово възстановяване на тримесечие или месечно:
- Възстановяване на резервни копия в тестова среда
- Проверете целостта и пълнотата на данните
- Проверете функционалността на приложението
- Време за възстановяване на документа (валидиране на RTO)
- Идентифицирайте и решете всички проблеми
10.5.2 Използване на RESTORE VERIFYONLY
Автоматизиране на валидирането на резервни копия:
-- Verify backup integrity
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM;
GOИзпълнете проверката веднага след завършване на архивирането или като част от планирана поддръжка.
10.6 Автоматизация и наблюдение на архивирането
10.6.1 SQL Server Работа на агенти
Създаване на автоматизирани задачи за архивиране:
- Разширете SQL Server Агент
в SSMS - Щракнете с десния бутон Работа и изберете Нова работа
- Наименувайте задачата (напр. „Ежедневно пълно архивиране“)
- Добавяне на Стъпка с T-SQL команди за архивиране
- Създаване на Планирам за времето за изпълнение
- Определен Известия за успех/неуспех
10.6.2 Планове за поддръжка
SQL Server Плановете за поддръжка предоставят визуален интерфейс за автоматизация на архивирането:
- Навигирайте до управление -> Планове за поддръжка
- Щракнете с десния бутон и изберете Съветник за план за поддръжка
- Изберете задачи за архивиране, които да автоматизирате
- Конфигуриране на график и опции за архивиране
- Настройване на отчитане и регистриране
10.6.3 Предупреждения и известия за архивиране
Конфигуриране на имейл известия:
- Настройване на пощата на базата данни в SQL Server
- Създаване на предупреждения за неуспехи при архивиране
- Следене на историята на заданията за архивиране
- Изпращане на обобщени отчети до администраторите
10.7 Документация и планиране на възстановяване след бедствия
Поддържайте изчерпателна документация:
- График за архивиране: Кога и какво се архивира
- Политики за съхранение: Колко дълго се съхраняват резервните копия
- Места за съхранение: Къде се съхраняват резервните копия
- Процедури за възстановяване: Инструкции за възстановяване стъпка по стъпка
- Информация за връзка: Ключов персонал и доставчици
- Резултати от теста за възстановяване: Документирани резултати от тестовете
11. Разширени SQL Server Сценарии за архивиране
11.1 Архивиране на много големи бази данни (VLDB)
11.1.1 Стратегии за файлове и файлови групи
За бази данни, надвишаващи няколкостотин гигабайта:
- Разделяне на данни само за четене и данни за четене и запис в различни файлови групи
- Архивирайте файлови групи само за четене рядко
- Фокусирайте честите архиви върху активните файлови групи
- Използвайте резервни копия на ниво файл за подробен контрол
Примерно архивиране на файлове:
-- Back up specific file
BACKUP DATABASE LargeDB
FILE = 'LargeDB_Data1'
TO DISK = 'C:\Backups\LargeDB_File1.bak'
WITH COMPRESSION;
GO11.1.2 Оптимизация на производителността на архивирането
Подобряване на производителността на архивирането на VLDB:
- Резервни копия с райета: Записване в няколко файла едновременно
- Compression: Намалете изискванията за входно/изходни операции и съхранение
- Няколко резервни устройства: Паралелизиране на операциите по архивиране
- Бързо съхранение: Използвайте SSD дискове за архивиране
- Брой буфери: Увеличете опцията BUFFERCOUNT
- Максимален размер на трансфера: Оптимизиране на настройката MAXTRANSFERSIZE
-- Optimized VLDB backup
BACKUP DATABASE LargeDB
TO DISK = 'C:\Backups\LargeDB_1.bak',
DISK = 'D:\Backups\LargeDB_2.bak',
DISK = 'E:\Backups\LargeDB_3.bak'
WITH COMPRESSION,
BUFFERCOUNT = 100,
MAXTRANSFERSIZE = 4194304;
GO11.2 Архивиране в групи за достъпност „Винаги включено“
Групите за винаги включена наличност разпределят натоварването с резервни копия между реплики:
- Конфигурирайте предпочитанията за архивиране (първично, вторично или произволно копие)
- Прехвърляне на резервни копия към вторични реплики, за да се намали основното натоварване
- Използвайте COPY_ONLY резервни копия на вторични реплики
- Наблюдение на настройките за приоритет на архивиране
-- Check backup preferences
SELECT
ag.name AS AvailabilityGroup,
ar.replica_server_name,
ar.backup_priority
FROM sys.availability_replicas ar
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;
GO11.3 Архивиране за огледално копиране на база данни
В сценарии за огледално копиране на база данни:
- Редовно архивирайте основната база данни
- Архивирането на регистрационните файлове на транзакциите е от съществено значение за огледалното създаване
- Огледалната база данни е в състояние ВЪЗСТАНОВЯВАНЕ (не може да се архивира директно)
- Помислете за създаване на резервно копие на огледало след превключване на системата с повреда
11.4 Архивиране в хранилище за BLOB обекти в Azure
SQL Server може да архивира директно в Azure Blob Storage:
- Създаване на акаунт за съхранение в Azure
- Създаване на SQL Server идентификационни данни за удостоверяване в Azure
- Използвайте URL синтаксис за местоназначение за резервно копие
-- Create credential for Azure
CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your_SAS_token';
GO
-- Backup to Azure
BACKUP DATABASE AdventureWorks
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/AdventureWorks.bak'
WITH COMPRESSION,
STATS = 10;
GO11.5 Архивиране към URL адрес
Предимства на архивирането на URL адрес:
- Неограничен капацитет за съхранение в облака
- Географското излишък се обработва автоматично
- Модел на ценообразуване с разплащане
- Не е необходимо локално дисково пространство
- Поддържа до 64 URL адреса на резервно копие (striping)
11.6 Резервни копия с разделяне за по-висока производителност
Резервните копия с разделяне на данните разделят на множество файлове за по-бърз входно/изходен режим:
-- Striped backup to 4 files
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AW_Stripe1.bak',
DISK = 'D:\Backups\AW_Stripe2.bak',
DISK = 'E:\Backups\AW_Stripe3.bak',
DISK = 'F:\Backups\AW_Stripe4.bak'
WITH COMPRESSION, FORMAT;
GOЗабележка: Всички stripe файлове са необходими за възстановяване. Липсата на който и да е файл прави резервното копие неизползваемо.
12. Отстраняване на проблеми SQL Server Проблеми с архивирането
12.1 Често срещани грешки при архивиране и решения
Грешка: „Грешка на операционната система 5: Достъпът е отказан“
- Причина: SQL Server Сервизният акаунт няма разрешения
- Решение: Предоставяне на разрешения за запис на SQL Server сервизен акаунт в папката за архивиране
Грешка: „Не може да се отвори резервно устройство… Грешка в устройството или устройството е офлайн“
- Причина: Невалиден път или недостъпен мрежов дял
- Решение: Проверете съществуването на пътя, проверете мрежовата свързаност, осигурете достатъчно дисково пространство
Грешка: „Няма достатъчно място на диска“
- Причина: Недостатъчно дисково пространство за архивиране
- Решение: Освободете дисково пространство, използвайте компресия, архивирайте на различно място
Грешка: „Базата данни се използва. Базата данни се използва от други потребители“
- Причина: Активни връзки по време на възстановяване
- Решение: Използвайте опцията WITH REPLACE или първо изключете потребителите
12.2 Проблеми с производителността на архивирането
Диагностициране на бавни резервни копия:
- Проверете производителността на дисковия вход/изход, като използвате Performance Monitor
- Следете напредъка на архивирането с опцията STATS
- Оценки на посетители SQL Server дневник на грешките за пречки
- Помислете за компресия, за да намалите входно-изходните операции (I/O).
- Използвайте разделени резервни копия на множество дискове
Заявка за наблюдение на напредъка на архивирането:
SELECT
session_id,
command,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%';
GO12.3 Проблеми с пространството и съхранението
Предотвратяване на проблеми със съхранението:
- Приложете политики за запазване: Автоматично изтриване на стари резервни копия
- Използвайте компресия: Намалете размера на резервния файл с 50-70%
- Архивиране за по-евтино съхранение: Преместете старите резервни копия в архивно хранилище
- Монитор на дисково пространство: Настройване на известия за малко дисково пространство
- Приблизителен размер на резервното копие: Изчислете очаквания размер преди архивиране
Приблизителен размер на резервното копие:
-- Estimate full backup size
EXEC sp_spaceused;
GO12.4 Проблеми с разрешенията и достъпа
Необходими разрешения за архивиране:
- РЕЗЕРВНО КОПИЕ НА БАЗА ДАННИ разрешение
- db_backupoperator членство в роли
- администратор роля на сървър (за всички операции по архивиране)
Предоставяне на разрешения за архивиране:
-- Grant backup permission to user
GRANT BACKUP DATABASE TO [BackupUser];
GRANT BACKUP LOG TO [BackupUser];
GO
-- Add user to backup operator role
ALTER ROLE db_backupoperator ADD MEMBER [BackupUser];
GO12.5 Повредени архивни файлове
Откриване и обработка на повредени резервни копия:
Проверете целостта на резервното копие:
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM;
GOАктивирайте CHECKSUM за бъдещи архиви:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM, INIT;
GOСтратегии за превенция:
- Винаги използвайте опцията CHECKSUM по време на архивиране
- Проверявайте резервните копия веднага след създаването им
- Тествайте възстановяванията редовно
- Съхранявайте резервни копия на надеждно място за съхранение
- Поддържайте множество резервни копия
12.6 Възстановяване на данни от повредени архивни файлове
Ако вашите резервни файлове са повредени и все още искате да възстановите данни от тях, можете да използвате инструменти на трети страни, като например DataNumen SQL Recovery, както по-долу:
- Start DataNumen SQL Recovery.
- Изберете повредения архивен файл като изходен файл, като промените филтъра на „Всички файлове (*.*)“:
- Задайте изходния .MDF файл, ако е необходимо.
- Щракнете върху „Star„Възстановяване“ и следвайте инструкциите за възстановяване на базата данни.
- След процеса на възстановяване ще се появи нова база данни за възстановяване. SQL Server който съдържа всички възстановени данни.
13. SQL Server Защита на резервните копия
13.1 Защита на архивните файлове
Защитете архивните файлове от неоторизиран достъп:
- Разрешения за файловата система: Ограничете достъпа само до оторизирани администратори
- Мрежова сигурност: Използвайте защитени протоколи за мрежови архиви
- Физическа охрана: Съхранявайте резервните носители на сигурни места
- Регистриране на достъпа: Одит на достъпа до архивни файлове
13.2 Опции за криптиране
SQL Server поддържа прозрачно криптиране на резервни копия:
Създаване на сертификат за криптиране:
-- Create master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd!';
GO
-- Create certificate
CREATE CERTIFICATE BackupCertificate
WITH SUBJECT = 'Database Backup Certificate',
EXPIRY_DATE = '2026-12-31';
GOКриптирано резервно копие:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Encrypted.bak'
WITH COMPRESSION,
ENCRYPTION (
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCertificate
);
GOВажно: Архивирайте сертификата и частния ключ поотделно. Без тях криптираните резервни копия не могат да бъдат възстановени.
-- Backup certificate
BACKUP CERTIFICATE BackupCertificate
TO FILE = 'C:\Certificates\BackupCertificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\Certificates\BackupCertificate.key',
ENCRYPTION BY PASSWORD = 'C3rt!f!c@t3P@ss'
);
GO13.3 Контрол на достъпа и разрешения
Приложете принципа на най-малките привилегии:
- Предоставяне на разрешения за архивиране само на необходимите акаунти
- Използвайте отделни акаунти за операции по архивиране и възстановяване
- Избягвайте използването на SA акаунт за архивиране
- Редовно проверявайте разрешенията за архивиране
- Премахнете разрешенията, когато вече не са необходими
13.4 Съображения за съответствие
Спазване на регулаторните изисквания:
- GDPR: Криптирайте резервни копия, съдържащи лични данни, прилагайте политики за съхранение
- HIPAA: Криптиране на защитена здравна информация (PHI) в резервни копия, контрол на достъпа, поддържане на одитни следи
- PCI DSS: Криптиране на резервни копия на данните на картодържателите, сигурно съхранение на резервни копия
- SOX: Поддържане на целостта на резервните копия, политики за съхранение на документи
14. Мониторинг и поддържане на операции по архивиране
14.1 Проследяване на историята на архивирането
SQL Server съхранява историята на архивирането в базата данни msdb:
-- View recent backup history
SELECT
bks.database_name,
bks.backup_start_date,
bks.backup_finish_date,
CASE bks.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
ELSE 'Other'
END AS backup_type,
bks.backup_size / 1024 / 1024 AS backup_size_mb,
bkmf.physical_device_name
FROM msdb.dbo.backupset bks
INNER JOIN msdb.dbo.backupmediafamily bkmf ON bks.media_set_id = bkmf.media_set_id
WHERE bks.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY bks.backup_start_date DESC;
GOНамиране на бази данни без скорошни резервни копия:
SELECT
d.name AS database_name,
MAX(bs.backup_finish_date) AS last_backup_date,
DATEDIFF(DAY, MAX(bs.backup_finish_date), GETDATE()) AS days_since_last_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4 -- Exclude system databases
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -7, GETDATE())
OR MAX(bs.backup_finish_date) IS NULL
ORDER BY last_backup_date;
GO14.2 Използване SQL Server Доклади
SQL Server Management Studio включва вградени отчети за архивиране:
- Щракнете с десния бутон върху базата данни в Object Explorer
- Изберете Доклади -> Стандартни отчети
- Изберете от наличните отчети:
- Събития за архивиране и възстановяване
- Всички резервни копия
- Състояние на доставката в регистрационния файл на транзакциите
14.3 Инструменти за мониторинг от трети страни
Решения за търговски мониторинг:
- SQL Sentry: Цялостно наблюдение и предупреждение
- Редгейт SQL монитор: Мониторинг и диагностика в реално времеostICS
- Анализатор на производителността на базата данни SolarWinds: Мониторинг на производителността и архивирането
- Idera SQL DiagnostМениджър на IC: Валидиране и предупреждение на резервни копия
14.4 Проверки на състоянието на резервните копия
Създайте процедура за проверка на състоянието:
-- Backup health check procedure
CREATE PROCEDURE sp_BackupHealthCheck
AS
BEGIN
-- Check for databases without recent full backup
SELECT
'Missing Recent Full Backup' AS issue,
d.name AS database_name,
ISNULL(CAST(MAX(bs.backup_finish_date) AS VARCHAR), 'Never') AS last_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
ON d.name = bs.database_name AND bs.type = 'D'
WHERE d.database_id > 4
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -7, GETDATE()) OR MAX(bs.backup_finish_date) IS NULL; -- Check for failed backup jobs SELECT 'Failed Backup Job' AS issue, j.name AS job_name, jh.run_date, jh.run_time, jh.message FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id WHERE jh.run_status = 0 -- Failed AND jh.step_id = 0 AND jh.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-7, 112))
AND j.name LIKE '%backup%';
END
GO15. SQL Server Често задавани въпроси за архивиране
15.1 Колко често трябва да правя резервно копие SQL Server?
Честотата на архивиране зависи от вашата цел за точка на възстановяване (RPO):
- Критични производствени бази данни: Пълна седмична, диференциална дневна, логване на всеки 15-30 минути
- Стандартни производствени бази данни: Пълна седмична, диференциална дневна, логване на всеки 1-2 часа
- Бази данни за разработка: Пълен дневен или седмичен
- Бази данни само за четене: Пълно след всяка промяна на данните
15.2 Каква е разликата между пълно и диференциално архивиране?
Пълното архивиране копира цялата база данни, докато диференциалното архивиране улавя само промените след последното пълно архивиране. Диференциалните архивирания са по-малки и по-бързи, но изискват основното пълно архивиране за възстановяване.
15.3 Мога ли да направя резервно копие SQL Server Докато работи?
Да, SQL Server поддържа онлайн архивиране. Потребителите могат да продължат да работят по време на операциите по архивиране. SQL Server използва своя дневник на транзакциите, за да поддържа последователност, като гарантира, че резервните копия са валидни дори при едновременни модификации.
15.4 Колко време трае SQL Server Резервно копие?
Продължителността на архивирането варира в зависимост от:
- Размер на базата данни: По-големите бази данни отнемат повече време
- Тип резервно копие: Пълните резервни копия отнемат най-много време
- Compression: Може да увеличи времето на процесора, но да намали общата продължителност
- Скорост на съхранение: SSD дисковете са значително по-бързи от HDD дисковете
- Натоварване на сървъра: По-високата активност забавя архивирането
Типични диапазони: Пълното архивиране на база данни от 10GB може да отнеме 5-15 минути с компресия на съвременен хардуер.
15.5 Къде трябва да съхранявам SQL Server Резервни копия?
Най-добра практика: Следвайте правилото 3-2-1:
- 3 копия на вашите данни
- 2 различни видове съхранение (напр. диск и лента/облак)
- 1 копие извън сайта
Препоръчителни места:
- Локален диск за бързо възстановяване
- Мрежово съхранение за централизирано управление
- Облачно съхранение (Azure, AWS) за възстановяване след бедствия
15.6 Какво е файловото разширение .bak?
Разширението .bak е файловото разширение по подразбиране за SQL Server архивни файлове. Това е конвенция, а не изискване – SQL Server Резервните копия работят с всяко файлово разширение. Използването на .bak обаче прави резервните файлове лесно разпознаваеми и е стандартна практика в индустрията.
15.7 Как да направите резервно копие SQL Server към мрежовото устройство?
За да направите резервно копие на мрежово устройство:
- Уверете се, SQL Server акаунтът на услугата има разрешения за запис в споделения мрежов ресурс
- Използвайте UNC път в командата за архивиране:
\\ServerName\ShareName\BackupFile.bak - Тествайте свързаността, преди да планирате автоматизирани архиви
BACKUP DATABASE AdventureWorks
TO DISK = '\\BackupServer\SQLBackups\AdventureWorks.bak'
WITH COMPRESSION, INIT;
GO15.8 Мога ли да компресирам SQL Server Резервни копия?
Да, SQL Server поддържа вградена компресия на резервни копия (Enterprise Edition или Standard Edition)tarзвън SQL Server 2016 SP1). Компресията обикновено намалява размера на резервното копие с 50-70% и често намалява времето за архивиране чрез намаляване на входно-изходните операции, въпреки че увеличава използването на процесора.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH COMPRESSION;
GO16. заключение
16.1 Основни изводи
Ефективен SQL Server Стратегиите за архивиране защитават вашите данни и осигуряват непрекъснатост на бизнеса. Запомнете тези основни точки:
- Разберете типовете резервни копия: Изберете подходящи типове архивиране (пълно, диференциално, лог на транзакции) въз основа на вашите изисквания за възстановяване
- Изберете подходящ модел за възстановяване: Пълно възстановяване на критични данни, лесно за бази данни за разработка
- Внедряване на график за архивиране: Редовните пълни резервни копия, комбинирани с диференциални и лог архиви, минимизират загубата на данни.
- Процедури за възстановяване на тестове: Резервните копия са ценни само ако могат да бъдат успешно възстановени
- Автоматизирайте и наблюдавайте: Използвайте SQL Server Агент, планове за поддръжка и инструменти за наблюдение
- Сигурни резервни копия: Криптирайте чувствителни данни и контролирайте достъпа до резервни файлове
- Съхранявайте копия извън сайта: Защитете се от бедствия в целия сайт с облачно или отдалечено съхранение
- Документирайте всичко: Поддържайте ясна документация за процедурите за архивиране и възстановяване
16.2 Следващи стъпки и ресурси
За да подобрите своя SQL Server реализация на резервно копие:
- Оценете текущата си стратегия за архивиране спрямо най-добрите практики
- Изчислете вашите изисквания за RTO и RPO
- Тествайте процедурите за възстановяване на непроизводствени системи
- Преглеждайте и актуализирайте редовно графиците за архивиране
- Внедряване на автоматизирано наблюдение и предупреждение
- Обучете членовете на екипа за процедурите за възстановяване
Допълнителни ресурси:
- Microsoft SQL Server Документация: Официални насоки за архивиране и възстановяване
- SQL Server форуми на общността за архивиране: Споделяне на опит и решения
- Професионални сертификати: Сертифициран от Microsoft: Администратор на бази данни Azure
16.3 Препоръчителни инструменти и решения
Въз основа на различни сценарии:
Малки предприятия:
- Роден SQL Server архивиране с планирано SQL Server Работа на агенти
- SQLBackupAndFTP за интеграция в облака
- Azure Backup за SQL Server
Средни предприятия:
- SQL Server Планове за поддръжка
- Инструменти на трети страни като Redgate SQL Backup Pro
- Veeam Backup за SQL Server
Големи предприятия:
- Quest LiteSpeed за максимална компресия
- Commvault или Veritas NetBackup за управление на архивиране в предприятието
- Групи за винаги налична достъпност за High Availability
SQL Server Архивирането е от основно значение за администрирането на бази данни. С правилно планиране, внедряване и тестване можете да гарантирате, че данните ви ще останат защитени и възстановими, когато е необходимо.tarприлагането на тези най-добри практики днес, за да осигурите вашата SQL Server бази данни.
За автора
Юан Шенг е старши администратор на бази данни (DBA) с над 10 години опит в SQL Server среди и управление на корпоративни бази данни. Той е разрешил успешно стотици сценарии за възстановяване на бази данни във финансови услуги, здравеопазване и производствени организации.
Юан е специализиран в SQL Server възстановяване на бази данни, решения за висока достъпност и оптимизация на производителността. Неговият богат практически опит включва управление на многотерабайтови бази данни, внедряване на групи за достъпност Always On и разработване на автоматизирани стратегии за архивиране и възстановяване за критично важни бизнес системи.
Чрез техническата си експертиза и практичен подход, Юан се фокусира върху създаването на изчерпателни ръководства, които помагат на администраторите на бази данни и ИТ специалистите да решават сложни задачи. SQL Server предизвикателствата ефикасно. Той е в крак с най-новото SQL Server издания и развиващите се технологии за бази данни на Microsoft, като редовно тества сценарии за възстановяване, за да гарантира, че препоръките му отразяват най-добрите практики в реалния свят.
Имате въпроси относно SQL Server възстановяване или имате нужда от допълнителни насоки за отстраняване на проблеми с базата данни? Юан приветства обратна връзка и предложения за подобряване на тези технически ресурси.