Microsoft SQL Server Express Edition — бесплатная версия Microsoft SQL Server, в которой многий функционал порезан.
Например отсутствует SQL Server Agent или Maintenance Plans, которые облегчают создание автоматических бэкапов баз данных.
В связи с этим, приходится выбирать другие пути. Один из них описан ниже.
Исходные данные для наших настроек:
- имя базы данных: db_name
- путь для хранения скриптов и бэкапов базы данных db_name: «d:\projects\db_backups»
- периодичность создания бэкапов: раз в 1 день
- срок хранения бекапов: 3 дня
Разделим скрипт бекапов на 2 шага:
- создание и сохранение бекапа
- очистка папки от старых бэкапов
Для первого шага:
a) Запускаем нижеследующий скрипт, например в Management Studio, чтобы записалась хранимая процедура на master базе данных
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] Script Date: 8/12/2021 6:26:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS
SET NOCOUNT ON;
DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name= ISNULL(@databaseName ,name)
ORDER BY Name
-- Filter out databases which do not need to backed up
IF @backupType='F'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
END
ELSE IF @backupType='D'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE IF @backupType='L'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE
BEGIN
RETURN
END
-- Declare variables
DECLARE @BackupName nvarchar(100)
DECLARE @BackupFile nvarchar(300)
DECLARE @DBNAME nvarchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int
-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs
WHILE @Loop IS NOT NULL
BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
-- Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = 'F'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'D'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'L'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
-- Provide the backup a name for storing in the media
IF @backupType = 'F'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
IF @backupType = 'D'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
IF @backupType = 'L'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
-- Generate the dynamic SQL command to be executed
IF @backupType = 'F'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'D'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'L'
BEGIN
SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
-- Execute the generated SQL command
EXEC(@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END
b) В папке бэкапов создаем исполняемый файл с расширение bat (например, sql_db_backup.bat) и содержимым
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='d:\projects\db_backups', @databaseName='db_name', @backupType='F'"
где ‘d:\projects\db_backups‘ — здесь будем хранить бэкапы, db_name — имя базы данных (из условий выше)
с) создаем в Расписании Виндовс (Task Scheduler) задачу для выполнения скрипта c параметрами
- ‘Create Basic Task…’
- ‘Run whether user is logged on or not’ выбранный, затем выбираем ‘Do not store password…’
- Trigger: Daily, и время, когда будет выполняться скрипт
- Actions: Start a program — и указываем путь к bat файлу, созданному ранее — sql_db_backup.bat
- Остальные параметры второстепенны
Уже на этом шаге у нас будут создаваться ежедневные бэкапы, но мы пойдем дальше и будем очищать старые бекап, чтобы освобождать место на жестов диске.
Для второго шага:
a) создадим скрипт с расширением bat (например, sql_clean_backups.bat) и содержимым
forfiles -p d:\projects\db_backups\ -m *.bak* /D -3 /C "cmd /c del /q @path"
который будет удалять файлы с расширением .bak старше 3х дней
b) создаем в Расписании Виндовс (Task Scheduler) задачу для выполнения скрипта c параметрами аналогичными пункту (c) первого шага.
На этом все…