Метка: sql

Microsoft SQL Server Express Edition — бесплатная версия Microsoft SQL Server, в которой многий функционал порезан.

Например отсутствует SQL Server Agent или Maintenance Plans, которые облегчают создание автоматических бэкапов баз данных.

В связи с этим, приходится выбирать другие пути. Один из них описан ниже.

Исходные данные для наших настроек:

• имя базы данных: db_name
• путь для хранения скриптов и бэкапов базы данных db_name: «d:\projects\db_backups»
• периодичность создания бэкапов: раз в 1 день
• срок хранения бекапов: 3 дня

Разделим скрипт бекапов на 2 шага:

1. создание и сохранение бекапа
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) первого шага.

На этом все…