W tej części artykułu opisano w jaki sposób przygotować procedurę tworzenia kopii zapasowej baz danych oraz jak automatycznie ją wywoływać przy pomocy Harmonogramu Zadań Windows. Instrukcję przygotowano dla automatyzacji wykonywania kopii zapasowych baz danych w SQL Server Express, który nie oferuje automatyzacji bezpośrednio poprzez narzędzie SQL Management Studio. Procedura automatyzacji przy użyciu SQL Management Studio została opisana w artykule: SOLIDWORKS PDM – kopia zapasowa bazy danych SQL Server cz.I
- W pierwszej kolejności należy uruchomić SQL Server Management Studio z poziomu Menu Start
- Zalogować się korzystając z danych podanych podczas instalacji SQL Server Express
- Drzewko po lewej stronie rozwinąć i przejść do Databases/System Databses/master/Programmability następnie kliknąć prawym klawiszem myszy na Stored Procedures i wybrać Stored Procedure…
- Kolejnym krokiem jest przekopiowanie poniższego skryptu i podmienienie go z procedurą w nowo otwartym oknie
// 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 [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
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=@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 [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
- Sprawdzić poprawność skryptu
Jeśli jest wszystko OK pojawi się poniższy komunikat
- Wykonać procedurę
Po poprawnym wykonaniu procedury ponownie pojawi się komunikat jak w pkt. 5 oraz zostanie dodana nowa procedura w katalogu „Stored Procedures” (jeśli procedura się nie pojawiła, należy kliknąć bazę danych prawym klawiszem myszy i wybrać Refresh)
- Zamknąć SQL Server Management Studio bez konieczności zapisywana zapytania SQL (pliku *.sql)
- Otworzyć notatnik i wkleić do niego poniższą komendę w jednej linijce
sqlcmd -S <Nazwa_serwera> –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"
Proszę podmienić pole <Nazwa_serwera> na nazwę serwera SQL oraz podmienić D:\SQLBackups\ na ścieżkę zapisu kopii zapasowej. Proszę się upewnić, że dane zostały wprowadzone prawidłowo oraz wskazany katalog rzeczywiście istnieje.
Zalecane jest wskazanie w powyższym skrypcie tego samego katalogu dla kopii zapasowych SQL, który został wskazany podczas instalacji Bazy SQL.
Następnie proszę zapisać plik notatnika przez „Zapisz jako” wprowadzając nazwę Sqlbackup.bat – proszę się upewnić, że plik został zapisany z rozszerzeniem .bat
Powyższy skrypt wywołuje procedurę tworzenia kopii zapasowej we wskazanym folderze. Proszę go zapisać w lokalizacji, w której omyłkowo nie mógłby zostać usunięty.
Ostatnim krokiem jest utworzenie cyklicznego zadania wywołującego uruchomienie pliku Sqlbackup.bat
- Otworzyć Menu Start, wpisać wyszukiwarce Harmonogram Zadań (Task Scheduler) i otworzyć program.
- W menu „Akcje” po prawej stronie wybrać „Utwórz zadanie podstawowe…”
- Wpisać nazwę dla zadania, np. „Kopia zapasowa SQL Express” oraz opcjonalnie uzupełnić pole Opis
- Zdecydować z jaką częstotliwością zadanie ma być wywoływane
Zalecane jest, aby kopia zapasowa była tworzona, kiedy nikt w firmie
nie pracuje, np. w piątek popołudniu lub w weekend.
- Jako wykonywaną akcję proszę wybrać „Uruchom program”
- Wskazać ścieżkę do pliku bat oraz zakończyć tworzenie zadania
Przed utworzeniem zadania można wykonać test czy uruchomienie pliku Sqlbackup.bat prawidłowo wywołuje procedurę tworzenia kopii zapasowej.
Test polega na ręcznym uruchomienie pliku wywołującego procedurę tworzenia kopii zapasowej. Należy go wykonać, gdy nikt w firmie nie pracuje w systemie PDM.
Aby to zrobić należy otworzyć Wiersz Poleceń z Menu Start oraz uruchomić plik Sqlbackup.bat. Jeśli wszystko prawidłowo wykonaliśmy, powinniśmy dostać informacje o utworzeniu kopii zapasowych.
Jeśli podczas wywoływania pliku Sqlbackup.bat pojawią się błędy, należy sprawdzić czy skrypt z punktu 8 został prawidłowo wprowadzony, nazwa serwera nie zawiera błędów, a wskazany folder rzeczywiście istnieje.
W przypadku pytań prosimy o kontakt.
