Простой скрипт для резервного копирования всех баз данных SQL Server

0
1338
Простой скрипт для резервного копирования всех баз данных SQL Server
Простой скрипт для резервного копирования всех баз данных SQL Server

Иногда то, что кажется нам сложным, оказывается гораздо проще, чем мы думали, и в этом заключается сила использования T-SQL для решения повторяющихся задач. Одной из таких задач может быть необходимость создания резервных копий всех баз данных на вашем сервере. Если баз не много, это не проблема, но я видел несколько серверов, где на одном экземпляре SQL Server было 100+ баз данных. Вы можете использовать SQL Server Management Studio для резервного копирования баз данных или даже использовать Maintenance Plans, но использование T-SQL — гораздо более простой и быстрый подход.

Решение
С помощью языка T-SQL вы можете создавать скрипты резервного копирования, а с помощью мышки вы можете просматривать все базы данных для их резервного копирования по очереди. Можно также использовать цикл while, если вы предпочитаете не использовать мышь. Это очень простой процесс, и для его выполнения вам понадобится всего несколько команд.

Как создать резервные копии всех баз данных SQL Server

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

Формат имени файла бэкапа базы данных DBname_YYYYDDMM.BAK

Вот скрипт, который позволит вам создать резервную копию каждой базы данных в вашем экземпляре SQL Server. Вам нужно будет изменить @path на соответствующий директорию резервного копирования.

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\test\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Формат имени файла бэкапа базы данных DBname_YYYYDDMM_HHMMSS.BAK

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

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)

этой строкой:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')

Добавление дополнительных элементов в команду резервного копирования

Чтобы добавить дополнительные параметры, такие как прогресс резервного копирования (статистика) и сжатие, вы можете изменить эту строку в скрипте:

BACKUP DATABASE @name TO DISK = @fileName

этой строкой:

BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION

Примечания

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

Кроме того, если вы хотите обойти некоторые пользовательские базы данных, вы можете включить их в раздел NOT IN.

Следующие шаги
  • Добавьте этот сценарий в свой набор инструментов (toolbox)
  • Измените этот сценарий и сделайте его хранимой процедурой, чтобы включить один или несколько параметров
  • Усовершенствуйте сценарий для использования дополнительных опций BACKUP