Резервное копирование ms sql express. Как настроить ежедневное резервное копирование с помощью SQL Server Express? Удаление задач из БД

Script для динамического резервного копирования всех баз данных на сервере. Затем создайте пакетный файл в соответствии со статьей. Полезно создавать два командных файла: один для полного резервного копирования и один для резервного копирования. Затем создайте две задачи в планировщике заданий, один для полного и один для diff.

-- // Copyright © Microsoft Corporation. All Rights Reserved. -- // This code released under the terms of the -- // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) USE GO /****** Object: StoredProcedure . ******/ 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 . @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=@DatabaseName OR @DatabaseName IS NULL 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 varchar(100) DECLARE @BackupFile varchar(100) DECLARE @DBNAME varchar(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 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

И пакетный файл может выглядеть так:

Sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation="c:\Dropbox\backup\DB\", @backupType="F"" >> c:\Dropbox\backup\DB\full.log 2>&1

Sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation="c:\Dropbox\backup\DB\", @backupType="D"" >> c:\Dropbox\backup\DB\diff.log 2>&1

Преимущество этого метода заключается в том, что вам не нужно ничего менять, если вы добавляете новую базу данных или удаляете базу данных, вам даже не нужно перечислять базы данных в script. Ответ от JohnB лучше/проще для сервера с одной базой данных, этот подход более подходит для серверов с несколькими базами данных.

MS SQL Express лишен агента, при помощи которого можно выполнять задачи по расписанию, но можно воспользоваться и стандартными средствами Windows.

Очень часто для небольших проектов хватает и Express версии SQL сервера. Одна из проблем -это то, что у Express версии нет SQL Agent службы, при помощи которой можно делать некоторые задачи по расписанию. Взамен можно использовать SQLCMD и стандартный Scheduled Tasks от Windows. Первое, что нужно сделать, это написать скрипт, который создавал бы для нас необходимые бекапы. Для его генерации можно воспользоваться MS Management Studio (ее тоже можно скачать для Express версии) и на окне создания бекапа нажать не OK, а “Script Actions to … ”.

Я же использую обычно в таких задачах следующий скрипт:

DECLARE @pathName NVARCHAR(512) SET @pathName = "D:\Backup\db_backup_" + Convert(varchar(8), GETDATE(), 112) + ".bak" BACKUP DATABASE TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N"db_backup", SKIP, NOREWIND, NOUNLOAD, STATS = 10

Этот скрипт создает бекап с именем файла db_backup_YYYYDDMM.bak где YYYYDDMM - это текущая дата. Дата в имени файла позволит нам создавать каждый день бекап в новом файле. Запустите и проверьте что бекап действительно создается такой какой вам и нужен. Этот скрипт сохраняем в какой-нибудь папке под именем schedule.sql , предположим c:\sheduled tasks\. В этой же папке создадим исполняемый файл backup.bat , следующего содержания:

Sqlcmd -S SEVERNAME -U UserName -P Password -i schedule.sql 7z a -tzip D:\Backup \db_backup_%date%.zip -i! D:\Backup\db_backup_*.bak del d:\Backup\db_backup_*.bak

Где меняем SERVERNAME - имя сервера, UserName - имя пользователя, Password - пароль пользователя, schedule.sql - имя сохраненного скрипта. Вторая и третья строка батника архивирует бекап в zip файл и удаляет сам файл бекапа. Для того чтобы работала архивация необходимо установить архиватор 7z и прописать полные пути до исполняемого файла 7z.exe либо положить 7z.exe и 7z.dll в ту же папку, где располагаются скрипты. Теперь можем запустить исполняемый файл backup.bat и проверить проработает ли он так как нужно. Последний шаг это записать schedule в задачи windows. Запускаем Task Scheduler из меню Пуск, либо набираем в командной строке taskschd.msc . В разных версиях Windows это выглядит по разному, да и информацию о том как сделать задачу можно прочитать в помощи Windows. Основное - это запускать задачу от имени пользователя с достаточными правами на используемые папки. При помощи таких действий можно так же запрограммировать и любые другие задачи. В скрипте schedule.sql можно перед бекапом вызвать какие-либо необходимые процедуры, может переиндексирование или сжатие базы данных.

Итак Вы внедрили электронный документооборот, в качестве СУБД используется бесплатная версия SQL сервера от Microsoft, благо данная бесплатная редакция позволяет использовать размер базы данных до 10 гигабайт. Первая проблема, которая выходит на повестку дня это резервное копирование баз данных. Рассмотрим данную операцию на примере резервирования системной базы данных Master.
Вообще копирование базы происходит по образу и подобию Oracle, нам также придется писать SQL запрос и использовать командную строку. Итак, для резервного копирования мы будем использовать утилиту osql.exe, вызывая ее из командной строки cmd.exe.
При подключении к серверу нам придется указать его имя, экземпляр СУБД, имя пользователя и его пароль. Вот как это будет выглядеть:
c:\> osql.exe –S Workstation\sqlexpress –U sa –P password_of_sa
В данном случае
Workstation это имя сервера баз данных,

sqlexpress имя экземпляра MSSQL, данное значение задается при установке СУБД,

sa – учетная запись системного администратора баз данных, для ее использования необходимо, чтобы СУБД была установлена со смешанным режимом авторизации, то есть должны присутствовать авторизация Windows и авторизация Microsoft SQL Server.
password_of_sa пароль администратора sa
Если все указано правильно, то курсор изменит свой вид на:
1 > _

Все, теперь можно вводить команды, написанные на языке Transact-SQL. Хотя в целях автоматизации процесса эти директивы можно записать в текстовой файл с расширением *.sql и подать на ввод утилиты osql. К примеру резервное копирование нашего серера будет выглядеть так:
c:\> osql.exe –S Workstation\sqlexpress –U sa –P password_of_sa –I c:\scripts\backup.sql
Теперь задание можно добавить в планировщик Windows и оно будет ежесуточно выполняться, создавая нам резервные копии базы данных. Остается только написать этот самый скрипт для копирования, в этом нам поможет, данное приложение предназначено для управления базами данных.

В левом окне со списком баз данных выберем необходимую, щелкнем правой кнопкой мыши, выбираем пункт меню «резервная копия», задаем необходимые параметры копирования, а затем в верхнем меню выбираем пункт «сценарий > записать действие в сценарий в новом окне запроса»

К примеру у меня получился такой скрипт:
BACKUP DATABASE TO DISK = N"c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\master.bak" WITH NOFORMAT, INIT, NAME = N"master-Полная База данных Резервное копирование", SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N"master" and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N"master")
if @backupSetId is null begin raiserror(N"Ошибка верификации. Сведения о резервном копировании для базы данных "master" не найдены.", 16, 1) end
RESTORE VERIFYONLY FROM DISK = N"c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\master.bak" WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

после этого данный сценарий следует поместить в файл c:\scripts\backup.sql , и система автоматически каждую ночь будет создавать копию базы данных в каталоге c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup , в дальнейшем данную папку можно для надежности копировать по сети на сервер с FreeBSD . То есть мы ежедневно будем иметь полную резервную копию базы данных за предыдущее число.
По образу и подобию настраиваются бекапы всех имеющихся у Вас баз данных. В заключение скажу, что базу данных Master нужно копировать, если вы создавали новых пользователей, меняли их пароли или вносили изменения в права доступа к базам данных. Если у Вас этого не происходило, то в ежедневном копировании БД Master не необходимости. А вот пользовательские базы данных нужно резервировать регулярно. Вообще это зависит от плана резервного копирования, но об этом в следующий раз;) Да, и напоследок напомню, что восстанавливать базу данных Master из резервной копии необходимо, запустив сервер MSSQL в однопользовательском режиме.

А также: бэкап SQL, бэкап 1С.

Серверная 1С содержит данные в базе данных, которая находится на SQL сервере. Сегодня мы рассматриваем вариант MS SQL 2005/2008.

Чтобы данные не были потеряны в случае сгоревшего диска сервера или других форс-мажорных ситуаций – необходимо с самого начала делать бэкапы (backup).

Делать ручками каждый день Backup SQL базы 1С конечно никто не хочет. Для этого есть автоматические средства. Познакомимся с ними.

Настройка Backup SQL

Настройка Backup SQL для базы 1С ничем не отличается от настройки бэкапа для любой другой базы данных.

Для настройки запустите MS SQL Management Studio. Эта программа находится в группе программ MS SQL.

Добавление задания бэкапа SQL базы 1С

Задания автоматического бэкапа баз SQL находятся в ветке Management / Maintenance plans.

Чтобы добавить новое задание бэкапа щелкните на группу Maintenance plans правой кнопкой мыши и выберите New Maintenance Plan.

Введите название задания. Название имеет значение только для Вас. На всякий случай лучше использовать английские символы.

Настройка задания бэкапа SQL базы 1С

Откроется редактор заданий. Обратите внимание – задания могут делать различные операции с базой данных, а не только бэкапы.

Список вариантов операций выведен слева внизу. Выберите Back Up Database Task двойной кнопкой мыши или просто перетащите вправо.

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

В окне настройки выберите нужные базы SQL 1С (можно сразу несколько или по одной).

Выберите место сохранения бэкапа базы SQL 1С. Необходимо выбрать физически другой винчестер. Организационно можно поставить галочку «Создать подпапки».

Теперь настроим расписание backup. Расписание backup по-умолчанию добавилось само. Но Вы можете добавить несколько расписаний (например, одно – ежедневное, одно – еженедельное и т.п.). Нажмите кнопку настройки расписания backup.

На скриншоте пример ежедневного Backup SQL базы 1С в 3 ночи.

Чтобы расписание backup в списке было красиво-понятным, его можно изменить.

Сохранение задания бэкапа SQL базы 1С

Нажмите записать. Задание появится слева в списке.

Это важно! Проверьте правильность создания задания Backup SQL базы. Для этого нажмите на задании правой кнопкой и выберите Execute.

В результате должен появится файл бэкапа по указанному пути. Если что-то не так – удалите задание (Del) и начните с начала.

sqlcmd -S DECLSERVER\SQLGTD -E -Q «declare @s varchar(255) set @s=’E:\backup\GTD_’ + convert(varchar(1), datepart(dw, getdate())) + ‘.bak’ backup database GTD to disk = @s with init, noformat, skip, nounload»

sqlcmd позволяет вводить инструкции Transact-SQL, системные процедуры и файлы скриптов из командной строки в редактор запросов в режиме SQLCMD,

• -S - задает имя сервера, server[\instance_name] ;
• DECLSERVER\SQLGTD - имя сервера/имя экземпляра, на котором крутится база;
• -E - использует для соединения с SQL server вместо имени пользователя и пароля доверительное соединение;
• -Q «cmdlinequery « - при запуске программы sqlcmd выполняет запрос, но выход из программы по завершении его выполнения не производится. Может быть выполнено несколько запросов, разделенных точкой с запятой. Заключайте запрос в кавычки, как показано выше;
• declare - объявляем переменную s ,имя переменной всегда начинается с @, поэтому @s . В нашем случае @s - это папка (диск) хранения бэкапов;
• varchar(n) - задает тип переменной @s как строковый с длинной строки n, в примере 255 символов;
• set - задает значение переменной @s ,в примере это папка backup на диске E (E:\backup\ ), далее задается имя бэкап файла, где набор функций convert(varchar(1), datepart(dw, getdate())) возвращает в текстовом формате с длиной в 1 символ текущий день недели (понедельник – 1 , вторник – 2 , и т.д.) и добавляется расширение bak . На выходе получим файл с именем GTD_НомерДняНедели.bak ;
• backup - создает бэкап;
• database - указывает на создание бэкапа всей базы;
• GTD - в нашем примере имя базы на SQL-сервере;
• to disk - указывает на тип устройства резервного хранения, файл жесткого диска, и указана переменная @s , которой присвоено путь и имя создаваемого файла;
• with init, noformat, skip, nounload - указывает на то, что необходимо произвести перезапись данных по кругу с переопределением заголовков, что позволит нам иметь 7 файлов бэкапа на каждый день недели, перезаписываемые по кругу.

При необходимости можно использовать и другие функции, например сжатие, см. справку по запросам и функциям Transact-SQL.

Шаг 2. Меняем расширение текстового файла на.cmd

В итоге получаем файл backupGTD.cmd . Запускать созданный командный файл необходимо с той машины, где установлена БД MS SQL.

Шаг 3. Автоматизируем данный процесс

Рассмотрим данный шаг на примере MS Windows Server 2008: Диспетчер сервера -> Конфигурация -> Планировщик заданий -> Библиотека планировщика заданий.