gifts2017

Автоматический backup и регламентные процедуры в SQL Server 2005 Express Edition.

Опубликовал Константин (kstukov) в раздел Администрирование - Архивирование (backup)

Как известно, SQL Server 2005 Express Edition хорош тем что абсолютно бесплатен, а если учесть, что теперь нужно в стандартной поставке докупать лицензию на sql для каждого пользователя – вдвойне хорош. Правда у бесплатной поставки есть ограничения по объему баз данных (4 Gb), оперативной памяти (1 Gb) и использовании только одного процессора. Для баз среднего размера и небольшого количества пользователей как раз то что нужно. Еще одним из недостатков бесплатной версии является то, что в ней отсутствует встроенная возможность автоматического создания бэкапов и запуска регламентных заданий.

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

  • Создание полных архивных копий
  • Создание разностных копий
  • Создание копий лога
  • Проверка целостности баз
  • Перестройка индексов
  • Реорганизация индексов
  • Формирование отчетов о выполнении операций

Как установить:

Извлечь из архива и поместить файл, например, в c:\expressmaint.sql , затем в Пуск-Выполнить набрать «sqlcmd -S .\SQLExpress -i c:\expressmaint.sql»

Как использовать:

Рассмотрим пример: пусть на нашем sql сервере у нас находятся 2 базы: «trade» и «buh». Необходимо настроить, чтобы каждый день в 7 утра делался полный backup баз и после этого делалась перестройка всех индексов. Также нужно, чтобы раз в час обновлялась статистика, и чистился процедурный кэш.

  1. Создаем три папки «C:\Backup\backups_sql», «C:\Backup\scripts_sql» и «C:\Backup\reports_sql». В первой будут храниться утренние бэкапы, во вторую поместим файлы скриптов, а в третью будут сохраняться отчеты о выполнении операций.
  2. Создаем следующие файлы и помещаем их в папку «C:\Backup\scripts_sql»:

Название файла Содержимое
FullDatabaseBackup.sql

exec expressmaint

   @database      = 'ALL_USER',

   @optype        = 'DB',

   @backupfldr    = 'C:\Backup\backups_sql',

   @reportfldr    = 'C:\Backup\reports_sql',

   @verify        = 1,

   @dbretainunit  = 'copies',

   @dbretainval   = 1,

   @rptretainunit = 'copies',

   @rptretainval  = 1,

   @report        = 1

Rebuild_all_indexes.sql

exec expressmaint

   @database      = 'ALL_USER',

   @optype        = 'REINDEX',

   @reportfldr    = 'C:\Backup\reports_sql',

   @rptretainunit = 'days',

   @rptretainval  = 1,

   @report        = 1

Update_statistics.sql

USE [buh]

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

go

 

USE [trade]

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

go

Free_proc_cashe.sql

USE [buh]

DBCC FREEPROCCACHE

go

 

USE [trade]

DBCC FREEPROCCACHE

go

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

Для добавления задачи нужно (простите за подробности):

·   Дважды щелкнуть на «Добавить задание» для запуска мастера назначенных задач, а затем на  кнопку «Далее» в первом диалоговом окне.

·   Нажать кнопку «Обзор», найти SQLCMD.exe (по умолчанию, он в папке C:\Program Files\Microsoft SQL Server\90\Tools\binn), а затем нажать кнопку «Открыть».

·   Ввести имя задачи, например «FullDatabaseBackup» и в параметрах выбрать ежедневно.

·   Нажать кнопку «Далее», указать время 07:00, а затем нажать кнопку «Далее»

·   Введите имя и пароль учетной записи, которые будет выполнять эту задачу.

·   Нажмите кнопку «Далее», установите флажок, чтобы открыть дополнительные свойства для этого задачи и нажмите кнопку Готово

·   В текстовом поле «Выполнить» добавить следующее содержание:

 -S . \SQLExpress -i"C:\Backup\scripts_sql\FullDatabaseBackup.sql"

·   Нажмите кнопку ОК.

Аналогично настраиваем для остальных. Перестройку индексов можно назначить на 8 утра, а обновление статистики и чистку кэша раз в час с 09:00 до 21:00.

4. Теперь каждое утро папка «C:\Backup\backups_sql» будет очищаться и туда будут помещаться новые бэкапы. Поэтому осталось настроить какую-нибудь программу для архивирования файлов для переноса бэкапов на резервный диск, комп, ..

Подробное описание и примеры здесь.

Где взять и как установить SQL Server 2005 Express Edition.

Процедура имеет следующие параметры:

Параметр

обяз.

по умолч.

Описание

@database

да

NONE

Служит для указания баз данных для которых будет выполняться операция. Может принимать значения

  • имя конкретной базы данных
  • ALL_USER когда нужно выполнить для всех пользовательских баз данных
  • ALL_SYSTEM для всех системных баз данных

@optype

да

NONE

Указывает на операцию, которая должны выполняться. Может принимать значения:

  • DB – создание полной резервной копии
  • DIFF – создание разностной резервной копии
  • LOG – создание копии лога
  • CHECKDB – проверка целостности
  • REINDEX – перестроить все индексы
  • REORG – реорганизовать все индексы

@backupwith

нет

NULL

Служит для указания дополнительных параметров архивации ( как в BOL для команды BACKUP WITH )

@backupfldr

нет

NULL

Основная папка для записи архивов. Для каждой базы данных будет создана подпапка

@verify

нет

1

Указывает, следует ли проверять файл резервной копии.
Допустимые значения: 1 и 0 с 1 = TRUE и 0 = FALSE

@verifywith

нет

NULL

Дополнительные параметры проверки ( как в BOL для команды VERIFY WITH)

@dbretainval

нет

1

Указывается период времени или количество копий старых архивных копий.

@dbretainunit

нет

NULL

Единица измерения для параметра @dbretainval. Допустимыми значениями являются minutes, hours, days, weeks, months and copies. Сочетание этих двух параметров определяет, как долго или каким образом будут храниться старые архивные копии.

@report

нет

1

Указывает, следует ли формировать отчет о выполнении операции.
Допустимые значения: 1 и 0 с 1 = TRUE и 0 = FALSE

 

@reportfldr

нет

NULL

Указывается папка для отчет, если @report = 1

@rptretainval

нет

1

Указывается период времени или количество копий старых отчетов.

@rptretainunit

нет

NULL

Единица измерения для параметра @rptretainval. Может принимать значения: minutes, hours, days, weeks, months and copies. Сочетание этих двух параметров определяет, как долго или каким образом будут храниться копии старых отчетов.

@delfirst

нет

0

Указывает, следует ли удалить устаревшие резервные копии до выполнения резервного копирования. Допустимые значения: 1 и 0 с 1 = TRUE и 0 = FALSE

 

 

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Василий Демидов (Душелов) 12.03.09 12:36
2. anbxp (anbxp) 12.03.09 15:55
А я для этого взял на http://www.codeplex.com/ExpressMaint этот самый ExpressMaint, потом батник в 5 строчек с его вызовом - и все
3. Евгений (ujs) 12.03.09 23:35
Бэкапчик делается проще. Батник в планировщике.
sqlmaint -S имясервера\SQLEXPRESS -U sa -P пароль -D имябазы -BkUpDB D:\Arhivs\SQL -BkUpMedia DISK -DelBkUps 4weeks
последний параметр указывает как долго хранить бэкапы
RodinMax; h00k; -ioan-; Alienvlg; waol; Rebelx; Gilev.Vyacheslav; Душелов; +8 Ответить 1
4. anbxp (anbxp) 13.03.09 10:17
5. Евгений (ujs) 13.03.09 12:44
(4) екзешник :) лежит в папке с SQLEXPRESS
выполняет операции обслуживания баз (бэкапы, обновление статистики, перестройка индексов)
подробнее http://msdn.microsoft.com/ru-ru/library/ms162827(SQL.90).aspx
6. Npoen Dzogchen (Dansur) 11.08.10 22:00
Не взлетело :cry: планировщик ничего не делает, скопировал в командную строку - вот что выдал:

C:\>"c:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLCMD.EXE" -S .\SQLExp
ress -i "f:\!BackUp!\SQL\scripts_sql\FullDatabaseBackup.sql"
Msg 50000, Level 16, State 1, Server ASOFT-SERVER\SQLEXPRESS, Procedure expressm
aint, Line 176
The folder f:\!BackUp!\SQL\backups_sql\ does not exist on this server
при том что путь точно есть, даже из этого же скрипта беру путь и "cd f:\!BackUp!\SQL\backups_sql\" - успешно,
также если текст этого скрипта запускать самим SQL - то тоже успешно, и копию делает и отчеты хорошие. а вот командной строкой почему то не видит пути. не подскажешь в чем может быть дело?
7. Владимир Денисов (den_vladimir) 19.09.11 07:40
Автору спасибо за труд!
ujs пишет:
sqlmaint -S имясервера\SQLEXPRESS -U sa -P пароль -D имябазы -BkUpDB D:\Arhivs\SQL -BkUpMedia DISK -DelBkUps 4weeks
последний параметр указывает как долго хранить бэкапы

За этот пример вообще поклон!
8. Alex Shipka (Akpish) 06.12.11 13:54
Кто бы что ни говорил, а организация backup средствами СУБД наиболее эффективная....никаких тебе зависаний планировщика, никакой головной боли с отключением пользователей, все ровненько и просто. Спасибо за публикацию.
9. Алексей Роза (DoctorRoza) 07.01.15 21:43
10. Сергей Сторожев (ssa) 05.06.15 13:15
Спасибо автору за тему, а
ujs за это:
ujs пишет:
sqlmaint -S имясервера\SQLEXPRESS -U sa -P пароль -D имябазы -BkUpDB D:\Arhivs\SQL -BkUpMedia DISK -DelBkUps 4weeks
последний параметр указывает как долго хранить бэкапы

Пригодилось!