Backup expres SQL Server. Crearea unui backup automat al bazei de date SQL pe serverul SQL Express Edition. Restaurarea unei baze de date dintr-o copie de rezervă

După ce am studiat o mulțime de informații din diferite surse, am decis să descriu procesul de configurare a unei copii de rezervă a bazei de date MS SQL Server pentru deplin model de recuperare, ce model să utilizați depinde de dvs., dar aș adăuga că, dacă există un flux mare de informații în baza dvs. de date (de exemplu, zeci, sute sau mii de documente sunt create într-o oră), atunci pierderea informațiile din timpul unei zile de lucru vor fi pur și simplu inacceptabile, în acest caz doar modelul complet va asigura siguranța datelor dumneavoastră. Acest articol este destinat administratorilor de sistem începători și conține în opinia mea setul minim de acțiuni pentru copierea de rezervă a unei baze de date 1C. Instalarea\configurarea serverului SQL în sine și implementarea unei baze de date pe acesta nu se încadrează în domeniul de aplicare al acestui articol.

Vom face toate setările folosind SQL Management Studio. Mai întâi trebuie să creați un dispozitiv de backup, nu trebuie să îl creați, dar în opinia mea este mult mai convenabil și mai corect. într-o clipă SQL Management Studio -> Server Objects -> Backup Devices. Trebuie să specificați numele dispozitivului și fișierul în care vor fi stocate backup-urile (de preferință cu extensia BAK), apoi puteți vizualiza conținutul media, toate backup-urile vor fi listate acolo.

Acum puteți începe configurarea Planului de întreținere. Un plan de întreținere poate fi creat pentru toate bazele de date simultan, dar este mai convenabil să vă creați propriul plan de întreținere pentru fiecare bază de date.

Planul nostru de servicii va avea trei subplanuri: 1 - backup bazei de date (complet); 2 - backup bazei de date (Diferente); 3 - Faceți o copie de rezervă a jurnalului de tranzacții. Fiecare subplan are propriul său program de execuție. Fiecare își stabilește programul după propria discreție, dar în cazul meu, o copie completă se face o dată pe săptămână duminica, o copie diferențială în fiecare zi, cu excepția zilei de duminică și un jurnal de tranzacții la fiecare oră. Cu acest model de backup, puteți restaura baza de date dorită la orice dată și oră, iar noi economisim spațiu pe hard disk deoarece O copie de rezervă completă se efectuează de fapt o dată pe săptămână și numai modificări sunt făcute în timpul săptămânii.

Stabilirea unui program zilnic. Săptămânal diferă doar în caseta de selectare „Duminică” și nebifat de la „Luni” la „Sâmbătă”

Program pentru transportul feroviar. Timpul de economisire în timpul zilei este evidențiat cu roșu, are sens, de exemplu, dacă utilizatorii lucrează cu baza de date într-o anumită perioadă, dacă modul de operare este 24x7, atunci îl lăsăm implicit.

Figura de mai jos arată editorul de subplan săptămânal, care constă din sarcini care sunt efectuate într-o anumită secvență. Secvența este setată manual, iar săgețile verzi înseamnă că sarcina următoare va fi finalizată numai dacă sarcina anterioară este finalizată cu succes, iar săgețile albastre înseamnă că sarcina va fi finalizată ori de câte ori sarcina anterioară este finalizată. În editorul subplanului de întreținere, sarcinile pot fi adăugate din „Panoul de elemente”, care se află în colțul din stânga sus când editorul este deschis.

Sarcini. Trebuie să accesați fiecare sarcină și să selectați baza de date pentru care va fi executată și o serie de alte setări (dacă există). Să ne uităm la ce sarcini conține subplanul săptămânal al planului nostru de întreținere.

1. „Verificați sarcina de integritate a bazei de date”. Următoarea sarcină va fi executată numai dacă baza de date nu conține erori. (Ar trebui să facem o copie de rezervă a bazei de date cu erori?)

2. „Reconstruiți sarcina de index”. Este necesar să restaurați (Reconstruiți) indexul în fiecare zi, deoarece... Când lucrați cu indici, aceștia devin foarte fragmentați, iar când fragmentarea depășește 25%, SQL începe să încetinească semnificativ. Această operațiune necesită destul de mult resurse, deci se poate face cel puțin o dată pe săptămână și în timpul zilei subplan pentru a-l înlocui cu sarcina mai puțin consumatoare de resurse „Reorganizarea indexului”.

3. „Sarcina de actualizare statistică”. Pentru optimizare... Apropo, această sarcină poate fi efectuată de mai multe ori în timpul zilei dacă baza de date este încărcată puternic.

4. După actualizarea statisticilor, TREBUIE să ștergeți memoria cache procedurală. Pentru a face acest lucru, trageți sarcina „Execute T-SQL statement” în editor și scrieți o procedură în câmpul „T-SQL statement:” DBCC FREEPROCCACHE. Dar trebuie să țineți cont de faptul că această procedură șterge memoria cache a TOATE bazele de date și am actualizat statisticile pe rând! Cum să ștergeți memoria cache procedurală pentru o anumită bază de date, citiți. Pe scurt: DBCC FLUSHPROCINDB(DB_ID)

5. „Backup DB” (Back Up Database Task). În această sarcină, indicăm ce bază de date facem backup, tipul de backup (Pentru un subplan săptămânal - Complet, pentru un subplan zilnic - Diferenţial, pentru un orar - Jurnal de tranzacţii.) Punem comutatorul în poziţia „Creează un copie de rezervă a bazelor de date într-unul sau mai multe fișiere" și adăugați-o dispozitivul de rezervă creat anterior. În acest caz, TOATE copiile sunt salvate într-un singur fișier, care a fost specificat la creare dispozitiv de rezervă, dacă comutatorul este lăsat în „Creați un fișier de rezervă pentru fiecare bază de date”, atunci pentru fiecare backup va fi creat un fișier separat pentru Full, Differential și VT, ceea ce este foarte incomod la restaurare, dar convenabil la stocare. Nu uitați să indicați că trebuie să comprimați copiile de rezervă!

6. „Clear Log” Şterge înregistrările create la executarea sarcinilor. De asemenea, puteți activa sarcina „Curățare după întreținere” și o puteți configura pentru a șterge jurnalele de text sau copiile de rezervă învechite.

Subplanul pentru backup VT constă dintr-o sarcină „Copia de rezervă a bazei de date”. Pentru mine, este mai convenabil să salvez VT nu pe dispozitivul de backup, ci într-un fișier separat, care trebuie specificat în setările sarcinii.

Funcționalitatea extinsă a Bacula Enterprise Edition, printre altele, vă permite să creați rapid și ușor copii de siguranță ale bazei de date pentru . De exemplu, vorbim despre un instrument cu care poți face backup pentru MS SQL Server. Utilizatorul poate face o copie de rezervă a MS SQL prin crearea de copii de siguranță în volum mare ale bazelor de date MS SQL specifice utilizate de platforma Windows, la costuri mai mici pentru software-ul terților, cu posibilitatea de a restaura datele la un anumit moment în timp (recuperare PITR ) la o rețea și o unitate locală.

Scriptul Bacula Systems pentru crearea de backup-uri MS SQL Server se caracterizează printr-o eficiență extremă, realizată prin implementarea unei arhitecturi moderne, de mare încredere. Mai mult, software-ul vă permite să faceți o copie de rezervă a MS SQL Server și să utilizați o varietate de opțiuni pentru a crea copii de rezervă MS SQL.

Scriptul de backup MS SQL Bacula Systems funcționează independent de VSS. Aceasta înseamnă că instrumentul de backup MS SQL nu utilizează instantanee VSS pentru a crea copii de rezervă. Prin urmare, utilizatorul poate seta următoarea valoare „Enable VSS = no” în Bacula FileSet. Crearea eficientă a backup-urilor MS SQL Server și restaurarea lor folosind această soluție se realizează prin utilizarea API-ului Microsoft pentru SQL Server. Acest lucru permite Bacula Systems să suporte mecanismele de securitate și toate tipurile de autentificare implementate în Microsoft SQL Server.

Backup pentru jurnalul de tranzacții MS SQL și recuperarea momentană a MS SQL: software-ul Bacula Enterprise Edition vă permite să recuperați blocuri de date MS SQL sau setări specifice la un anumit moment în timp. Odată cu implementarea modelelor de recuperare completă și în bloc, puteți recupera MS SQL folosind recuperarea PITR sau puteți utiliza LSN pentru a restaura sistemul la o anumită stare. Puteți restaura o anumită stare a unei baze de date MS SQL în orice moment specific, până la al doilea. În cazul unei copii de rezervă a jurnalului de tranzacții MS SQL, la restaurare, starea bazei de date va fi restabilită din diferite copii de rezervă selectate.

Caracteristici dintr-o privire  backup și recuperare automată a MS SQL cu Bacula Enterprise

Bacula Systems a creat un plugin de backup MS SQL Server pentru utilizare cu Bacula Enterprise Edition. Backup-ul MS SQL Server cu Bacula are următoarele caracteristici:

Suportă backup-uri MS SQL complete și diferențiate
Suport pentru backup incremental MS SQL
Backup MS SQL în rețea și unitatea locală
Backup programat MS SQL
Crearea de copii de rezervă la nivelul bazei de date MS SQL Server
Abilitatea de a include/exclude baze de date din procedura de creare a backupului
Suport pentru crearea de copii de rezervă numai pentru citire
Restaurarea backup-urilor MS SQL pe disc
Trimiterea unui flux de rezervă direct către Storage Daemon
MS SQL punct în timp de recuperare

Revizuirea și configurarea backupului MS SQL 2008, 2008 R2, 2012 și 2014

Acest document oferă soluții pentru Bacula Enterprise Edition 8.4 și ulterioare, care nu sunt acceptate de versiunile anterioare ale software-ului. Backup-ul bazei de date MS SQL a fost testat și este susținut de MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. Backup-ul MS SQL de la Bacula poate funcționa cu SQL Express.

Glosar MS SQL Backup 2008, 2008 R2, 2012 și 2014

MS SQL reprezintă Microsoft SQL Server.
Jurnal de tranzacții. Orice bază de date MS SQL Server are un jurnal de tranzacții, care înregistrează toate tranzacțiile și modificările bazei de date efectuate în timpul acestor tranzacții. Jurnalul de tranzacții este un element important al bazei de date. În cazul unei defecțiuni a sistemului, jurnalul de tranzacții poate fi necesar pentru a restabili baza de date la starea de funcționare. Mai multe informații pot fi găsite la https://msdn.microsoft.com/en-us/library/ms190925.aspx.
Backup diferențial al bazei de date MS SQL Server. Backup-ul diferențial se bazează pe ultimul complet. În timpul unei copii de rezervă diferențiale, sunt capturate numai datele care s-au modificat de la crearea ultimei copii de siguranță completă. Mai multe informații pot fi găsite la https://msdn.microsoft.com/en-us/library/ms175526.aspx.
Backup complet al bazei de date MS SQL Server.În timpul unui backup complet al bazei de date, este creată o copie de rezervă a întregii baze de date. Backup-ul include o parte din jurnalul de tranzacții cu scopul de a restaura baza de date completă din backup. Backup-urile complete ale bazei de date conțin baza de date la momentul finalizării copiei de rezervă. Mai multe informații pot fi găsite la https://msdn.microsoft.com/en-us/library/ms186289.aspx.
Backup „numai copiere” (CopyOnly). Copierele de rezervă numai pentru copiere sunt copii de rezervă MS SQL care sunt independente de fluxul normal al backup-urilor tradiționale SQL Server. Uneori este util să creați copii de rezervă pentru nevoi specifice, fără a afecta procesul general de backup și recuperare a bazei de date. Mai multe informații pot fi găsite la https://msdn.microsoft.com/en-us/library/ms191495.aspx.
VDI(Virtual Device Interface) este o tehnologie Microsoft care vă permite să creați conductă numităîntre programe.
măștile standard specifică seturi de șiruri de caractere cu metacaractere. De exemplu, masca standard de producție* va include liniile producție1 și producție2.
linia
întreg.
LSN Fiecare intrare din jurnalul de tranzacții MS SQL Server este identificată printr-un număr de serie unic al tranzacției (LSN). Informații mai detaliate pot fi găsite la https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx.

Backup MS SQL Server 2008, 2008 R2, 2012 și 2014

Backup complet al bazelor de date MS SQL Server 2008, 2008 R2, 2012 și 2014

În timpul unei copii de rezervă completă a unei baze de date MS SQL, fișierele bazei de date și jurnalul de tranzacții sunt salvate, ceea ce vă permite să protejați complet baza de date MS SQL în caz de defecțiune a media. Dacă unul sau mai multe fișiere sunt deteriorate, restaurarea bazei de date MS SQL dintr-o copie de rezervă vă va permite să restaurați toate tranzacțiile finalizate. Toate tranzacțiile care erau în curs vor fi, de asemenea, anulate. În acest mod, sunt create copii de rezervă ale bazelor de date master și mbdb.

Backup diferențial al bazelor de date MS SQL Server 2008, 2008 R2, 2012 și 2014

Backup-ul diferențial al bazei de date MS SQL Server se bazează pe cel mai recent backup complet al bazei de date MS SQL. Când se creează o copie de rezervă MS SQL diferențială, sunt capturate numai datele care au fost modificate de la crearea ultimului backup complet MS SQL. Pentru funcția de backup diferențial MS SQL, succesiunea backup-urilor este extrem de importantă. Dacă, dintr-un motiv oarecare, copia de rezervă completă la care face referire MS SQL nu este disponibilă, copiile de rezervă diferențiate ale bazei de date MS SQL Server nu pot fi utilizate. MS SQL Backup de la Bacula folosește tehnici specifice pentru a rezolva această problemă. Prin urmare, dacă apar dificultăți, starea unei copii de siguranță diferențiate a bazei de date poate fi actualizată automat la o copie de rezervă completă.

Backup jurnal de tranzacții pentru MS SQL 2008, 2008 R2, 2012 și 2014

Configurarea backupului MS SQL și configurarea bazei de date

Restaurarea unei baze de date MS SQL dintr-o copie de rezervă

Puteți folosi toate metodele standard pentru a începe procedura de restaurare a unei baze de date MS SQL dintr-o copie de rezervă. Cu toate acestea, trebuie să vă asigurați că, în cazul restaurării datelor diferențiale, va fi restaurată și backupul complet anterioar al bazei de date MS SQL. În acest caz, recuperarea are loc automat dacă o rulați în consolă bconsole folosind opțiunile de recuperare 5 sau 12. În structura de fișiere generată, trebuie să marcați recuperarea bazelor de date complete sau a instanțelor DB.

Opțiuni pentru restaurarea unei baze de date MS SQL dintr-o copie de rezervă

Software-ul Bacula Enterprise Edition permite utilizatorilor să utilizeze mai multe opțiuni de recuperare MS SQL și să aplice o varietate de metode de derulare a bazei de date. Cele mai frecvent utilizate opțiuni de recuperare sunt descrise mai jos:

Parametru unde: În cazul Bacula Enterprise Edition, acest parametru permite administratorului să restaureze baza de date într-o anumită locație.
Înlocuire parametru: Folosit pentru a defini cum ar trebui să se comporte Bacula cu baza de date curentă când este restaurată. Backup-ul MS SQL de la Bacula vă permite, de asemenea, să utilizați mai multe opțiuni la restaurare, cum ar fi:
Instanță: Deoarece MS SQL utilizează mai multe instanțe, backupul bazei de date MS SQL de la Bacula vă permite să alegeți ce instanță să restaurați. Acest parametru este opțional, iar dacă nu este specificat, valoarea specificată la crearea copiei de rezervă va fi utilizată la restaurare. În mod implicit, este utilizată o instanță numită „MSSQLSERVER”.
Bază de date. Această opțiune specifică numele bazei de date de restaurat și folosește valoarea specificată la momentul creării bazei de date. Acest parametru este opțional. În mod implicit, backup-urile bazei de date SQL Server utilizează parametrul Unde pentru a determina numele noii baze de date. Dacă ambilor parametrii Unde și Bază de date li se atribuie un nume valid al bazei de date, atunci va fi utilizat parametrul Bază de date.
Utilizator. Numele de utilizator utilizat pentru conectarea la instanța bazei de date MS SQL. Acest parametru este opțional, iar dacă nu este specificat, valoarea specificată la crearea copiei de rezervă va fi utilizată la restaurare.
Parola. Parola folosită pentru a vă conecta la instanța bazei de date MS SQL. Acest parametru este opțional, iar dacă nu este specificat, valoarea specificată la crearea copiei de rezervă va fi utilizată la restaurare.
Domeniu. Domeniul folosit pentru a se conecta la instanța bazei de date MS SQL. Acest parametru este opțional, iar dacă nu este specificat, valoarea specificată la crearea copiei de rezervă va fi utilizată la restaurare.
Recuperare. Parametrul vă permite să determinați dacă baza de date va fi revenită la starea anterioară în timpul recuperării sau nu. În mod implicit, la restaurarea unei baze de date, aceasta va reveni la starea anterioară.
Stop_before_mark. Stare CU STOPBANTEMARK = Folosit pentru a indica faptul că intrarea din jurnalul de tranzacții care precede steag este punctul de restaurare. Punctul de recuperare poate fi o dată și o oră, un LSN sau un flag mark_name.
Stop_at_mark. Stare CU STOPATMARK = Folosit pentru a indica faptul că tranzacția marcată este un punct de recuperare. STOPATMARK trece înainte la steag și redă tranzacția marcată. Punctul de recuperare poate fi o dată și o oră, un LSN sau un flag mark_name.
Stop_at= . Stare CU STOPAT = este folosit pentru a indica faptul că punctul de restaurare este data/ora.
Restrict_user. Clauza WITH RESTRICT_USER este folosită pentru a restricționa accesul la baza de date restaurată. Valoarea implicită este nr.

Restaurarea MS SQL la un moment dat poate fi efectuată direct din pluginul de backup MS SQL. De asemenea, puteți să restaurați fișierele local și să efectuați operațiuni din Consola de management Microsoft SQL Server pentru a obține mai multe funcționalități.

LSN

Numărul LSN al intrării de jurnal la care a avut loc un anumit eveniment de backup și recuperare poate fi vizualizat în unul dintre următoarele moduri:

Când se afișează o descriere a sarcinilor pentru crearea unei copii de rezervă folosind software-ul Bacula
În numele fișierului jurnal
În tabelul msdb.backupset
În tabelul msdb.backupfile

Când efectuați o sarcină pentru a crea o copie de rezervă a unei baze de date MS SQL, următoarele informații despre numerele LSN vor fi afișate la afișarea descrierii sarcinii:

Număr Primul LSN corespunde ultimului număr LSN al ultimului backup al jurnalului de tranzacții. Un astfel de backup poate fi primul backup complet sau ultimul backup (incremental).

Număr Ultimul LSN se potrivește cu ultima tranzacție înregistrată în jurnal.

În cazul unei copii de rezervă a jurnalului de tranzacții (incremental), numele fișierului asociat cu această bază de date în sarcina de creare a unei copii de siguranță incrementală va arăta astfel:

Numărul din nume, în cazul nostru 42000162001, corespunde ultimului număr LSN al sarcinii anterioare (pentru a crea o copie de rezervă completă sau incrementală).

Figura 2: Primul LSN, Ultimul LSN și LSN-urile în numele fișierelor

După cum se arată în exemplul din Figura 2, dacă administratorul trebuie să restaureze baza de date MS SQL într-o stare corespunzătoare cu numărul LSN 14, se pot efectua următorii pași:

În meniul de recuperare a bazei de date, utilizați opțiunea 5
Selectați cel mai recent fișier de backup complet „data.bak” (LSN: 10)
Selectați backup incremental „log-10.trn”

Sau, dacă cea mai recentă copie de rezervă completă a MS SQL Server nu este disponibilă, dar copia de rezervă completă anterioară este disponibilă, atunci:

Utilizați opțiunea de restaurare 3, selectați valorile jobids corespunzătoare
Selectați directorul bazei de date „/@mssql/db29187”
Selectați fișierul de rezervă complet „data.bak” (LSN: 2)
Selectați backup-uri incrementale „log-2.trn”, „log-3.trn”, „log-10.trn”
Setați parametrul stop_at_mark la „lsn:14”
Rulați sarcina pentru a restabili copia de rezervă

Scripturi de recuperare MS SQL

Descriere	Unde	Bază de date	Exemplu
Recuperați fișierele pe disc	cale		unde=c:/tmp
Restaurați baza de date inițială			unde=/
Restaurați cu un nume nou	Nume		unde=newdb
Restaurați cu un nume nou		Nume	baza de date=newdb
Recuperați cu un nume nou și mutați fișierele		Nume

Tabelul 1: Scenarii de recuperare MS SQL

2.3.1 Restaurarea unei baze de date MS SQL cu numele original

Pentru a restaura baza de date cu numele original, opțiunea Unde nu trebuie specificată (valoare goală), sau trebuie specificată valoarea „/” și parametrul A inlocui trebuie să i se atribuie o valoare Mereu, sau mai întâi trebuie să ștergeți baza de date sursă.

Restaurarea unei copii de rezervă MS SQL cu un nume nou

Pentru a restabili o copie de rezervă a bazei de date MS SQL cu un nume nou, poate fi necesar mai întâi să mutați fișierele bazei de date pe disc. Totul depinde dacă baza de date originală mai există.

Dacă baza de date sursă nu mai este disponibilă, atunci parametrul Unde, sau câmpul „Opțiuni plugin” poate conține numele noii baze de date. MS SQL Backup de la Bacula va crea automat baza de date cu un nume nou.

Dacă este încă necesară baza de date inițială, parametrul unde va fi folosit pentru a muta fișierele pe disc și va trebui să denumești noua bază de date folosind meniul Opțiuni plugin. În arborele de recuperare, trebuie să selectați fișierul layout.dat.

Folosind catalogul meu

Rulați sarcina de recuperare MS SQL:

Folosind Catalogul meu, rulați sarcina de recuperare a bazei de date MS SQL:

Recuperați MS SQL pe discul local

Dacă specificați unde=c:/cale/, fișierele vor fi restaurate pe discul local, iar administratorul bazei de date MS SQL va putea folosi extensia procedurală TSQL pentru Microsoft SQL Server Management Console pentru a restaura baza de date. Comenzile SQL necesare pentru restaurarea bazei de date sunt listate în descriere Rezultatul jobului așa cum se arată în imaginea de mai jos.

2. Vizualizați informații despre evenimentele de backup și restaurare pentru o bază de date

Pentru a afla când au fost create copii de rezervă ale unei anumite baze de date, precum și când a fost restaurată baza de date dintr-o copie de rezervă, puteți utiliza raportul standard " » (Evenimente de backup și restaurare). Pentru a genera acest raport, trebuie să faceți clic dreapta pe baza de date corespunzătoare în Object Browser (Server Oblects) și să selectați „ Rapoarte" (Rapoarte) - " Raport standard" (Rapoarte standard) - " Backup și restaurare evenimente» (Evenimente de backup și restaurare).

Raportul generat conține următoarele date:

Timp mediu luat pentru operațiunile de backup
Operațiuni de backup de succes
Erori de operare de backup
Operațiuni de restaurare reușite

Pentru a vizualiza aceste informații, trebuie să extindeți gruparea corespunzătoare din raport.

Te-a ajutat acest articol?

Script pentru backup dinamic al tuturor bazelor de date de pe server. Apoi creați un fișier batch conform articolului. Este util să creați două fișiere batch: unul pentru backup complet și unul pentru backup. Apoi creați două sarcini în planificatorul de joburi, una pentru finalizare și una pentru diferență.

-- // Copyright © Microsoft Corporation. Toate drepturile rezervate. -- // Acest cod eliberat în conformitate cu termenii -- // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) USE GO /****** Obiect: StoredProcedure. ******/ SETĂ ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================= ============ -- Autor: Microsoft -- Data creării: 2010-02-06 -- Descriere: Backup baze de date pentru SQLExpress -- Parametrul1: databaseName -- Parametru2: backupType F=full, D =diferențial, L=log -- Parametrul 3: locația fișierului de rezervă -- =================================== = ========== CREATE PROCEDURA . @databaseName sysname = null, @backupType CHAR(1), @backupLocation nvarchar(200) AS SET NOCOUNT ON; DECLARE @DBs TABLE (ID int IDENTITY PRIMARY KEY, DBNAME nvarchar(500)) -- Alegeți numai bazele de date care sunt online în cazul în care TOATE bazele de date sunt alese pentru a fi copiate -- Dacă o anumită bază de date este aleasă pentru a fi copiată, alegeți doar aceea out from @DBs INSERT INTO @DBs (DBNAME) SELECT Name FROM master.sys.databases where state=0 AND name=@DatabaseName SAU @DatabaseName ESTE NULL ORDER BY Name -- Filtrați bazele de date pentru care nu este nevoie să faceți copii de rezervă IF @ backupType="F" BEGIN DELETE @DBs unde DBNAME IN ("tempdb","Northwind","pubs","AdventureWorks") END ELSE IF @backupType="D" BEGIN DELETE @DBs unde DBNAME IN ("tempdb", „Northwind","pubs","master","AdventureWorks") END ELSE IF @backupType="L" BEGIN DELETE @DBs unde DBNAME IN ("tempdb","Northwind","pubs","master",") AdventureWorks") END ELSE BEGIN RETURN END -- Declare variabile DECLARE @BackupName varchar(100) DECLARE @BackupFile varchar(100) DECLARE @DBNAME varchar(300) DECLARE @sqlCommand NVARCHAR(1000) DECLARE @dateTime NVARCHAR(20) int -- Buclă prin bazele de date una câte una SELECT @Loop = min(ID) FROM @DBs WHILE @Loop IS NOT NULL BEGIN -- Numele bazelor de date trebuie să fie în format deoarece unele au - sau _ în numele lor SET @DBNAME = "["+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+"]" -- Setați data și ora curente n formatul aaaahhmmss SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),"/ ","") + "_" + REPLACE(CONVERT(VARCHAR, GETDATE(),108),":","") -- Creați numele fișierului de rezervă în format calea\filename.extension pentru copii de siguranță complete, diferite și jurnal 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" -- Furnizați copiei de rezervă un nume pentru stocarea pe suport IF @backupType = "F" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" backup complet pentru "+ @dateTime IF @backupType = "D" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" backup diferențial pentru "+ @dateTime IF @backupType = "L" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" backup jurnal pentru "+ @dateTime -- Generați comanda SQL dinamică care urmează să fie executată 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 DIFERENTIAL, 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 -- Executați comanda SQL generată EXEC(@sqlCommand) -- Mergeți la următoarea bază de date SELECT @Loop = min(ID) FROM @DBs unde ID>@Loop END

Și fișierul batch ar putea arăta astfel:

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

Avantajul acestei metode este că nu trebuie să modificați nimic dacă adăugați o nouă bază de date sau eliminați o bază de date, nici măcar nu trebuie să enumerați bazele de date în script. Răspunsul lui JohnB este mai bun/mai ușor pentru un singur server de baze de date, această abordare este mai potrivită pentru mai multe servere de baze de date.

O copie de rezervă diferențială se bazează pe cea mai recentă copie de rezervă completă anterioară a datelor. O copie de rezervă diferențială salvează doar modificările care au fost făcute de la ultima copie de rezervă completă.
Recomandări:

Utilizați copii diferențiale ale bazei de date dacă crearea unei copii complete a bazei de date durează o perioadă lungă de timp
Realizați periodic o copie completă a bazei de date pentru a reduce volumul de copii diferențiale create.
După crearea unei copii complete a bazei de date, toate copiile diferențiale anterioare își pierd relevanța.

Puteți citi mai multe despre recomandări cu privire la frecvența creării backup-urilor diferențiate.

Permiteți-mi să vă dau un mic exemplu practic de ce am început să folosim o copie diferită. De-a lungul timpului, baza de date a clienților noștri a crescut la o astfel de dimensiune încât crearea unei copii de siguranță completă a durat 8 ore, mai multe luni și poate că această operațiune nu ar avea timp să fie finalizată până la începutul zilei de lucru. După trecerea la backup diferențial, am redus timpul de la 8 ore la 2-4 minute (în funcție de ziua săptămânii). O dată pe săptămână am făcut o copie completă a bazei de date.

Exemplu SQL pentru crearea unei copii de rezervă diferențiale a unei baze de date cu verificarea copiei la finalizare (diferită de o copie completă cu steag DIFERENŢIAL ar trebui să-l folosești în schimb NOFORMAT).

Declara @pathBackup ca varchar(55) set @pathBackup = N"C:\Backup\[nume fișier DB]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + " .bak" BACKUP DATABASE [Database Name] TO DISK = @pathBackup WITH DIFERENTIAL, NOFORMAT, INIT, NAME = N"Full Database Backup", SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backupSetId ca int declare @pathBackup ca varchar(55) set @pathBackup = N"C:\Backup\[nume fișier DB]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + " .bak" selectați @backupSetId = poziția din msdb..backupset unde database_name=N"[Database name]" și backup_set_id=(selectați max(backup_set_id) din msdb..backupset unde database_name=N"[Database name]") dacă @backupSetId este nul begin raiserror(N „Eroare de verificare. Informațiile de backup pentru baza de date „[Numele bazei de date]” nu au fost găsite.", 16, 1) end RESTORE VERIFYONLY FROM DISK = @pathBackup WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO

3.Baze de date de sistem

Pe lângă baza de date principală și fișierele asociate acesteia, recomand cu tărie să faceți copii ale bazelor de date ale sistemului. Să începem prin a ne uita la ce baze de date există în MS SQL. Sunt doar 5 dintre ele:

Am ales să fac copii de rezervă doar pentru 2 baze de date de sistem:

msdb - deoarece sarcinile configurate și altele sunt stocate acolo
master – toate setările SQL Server configurate sunt stocate.

Aceste informații nu sunt încă foarte critice și pot fi restaurate manual, dar de ce să pierzi timp suplimentar când le poți lua pur și simplu dintr-o copie de rezervă.

4. Plan de rezervă

Pe baza celor de mai sus, vom întocmi planul nostru de backup al datelor. Poate diferi de ceea ce aveți nevoie, totul depinde de cerințele pentru recuperarea bazei de date. Când am pregătit planul, a trebuit să țin cont că era necesar să recuperez datele cât mai mult posibil și pierderea datelor nu a fost mai mare de o oră.

Vom face următoarele backup-uri:

Nu este necesară o copie completă a bazei de date principale, mai mult de o dată pe săptămână
Copie diferențială a bazei de date principale, în fiecare zi
Copii ale jurnalului principal de tranzacții ale bazei de date, la fiecare oră
Copie a bazei de date a sistemului principal, o dată pe săptămână
Copie a bazei de date a sistemului msdb, o dată pe săptămână

Ca rezultat, am ajuns să avem următorul plan de backup pentru date:

Zi a săptămânii	Timp	Acțiuni	Frecvență	Descriere
luni vineri	De la 8-00 la 21-00	Backup-uri Jurnal de tranzacții	În fiecare oră	După efectuarea unei copii de siguranță a bazei de date, jurnalul de tranzacții este comprimat și trunchiat
Sâmbătă duminică	De la 8-00 la 18-00	Backup-uri Jurnal de tranzacții	În fiecare oră
luni duminica	22-00	Copie diferențială a bazei de date principale	1 pe zi	După rularea cu succes a unei copii diferențiale, toate copiile vechi ale jurnalului de tranzacții sunt șterse
sâmbătă	12-00	Verificarea bazei de date	1 pe zi	Verificarea integrității bazei de date.
sâmbătă	18-00	Crearea unei copii complete a bazei de date	1 pe zi	La finalizarea acestei operațiuni, se trimite o notificare prin e-mail. Dacă copia de rezervă a fost creată cu succes, aceasta este ștearsă backup complet vechi toate exemplarele diferențiale vechi toate jurnalele vechi de tranzacții
luni duminica	23-30	Crearea unei copii a bazei de date a sistemului principal	1 pe zi
duminică	12-30	Crearea unei copii a bazei de date a sistemului msdb	1 dată pe lună	Doar ultima instanță a bazei de date este întotdeauna stocată

Utilizați opțiunea BACKUP WITH CHECKSUM
pentru a te asigura că totul a mers bine. Dezavantajul acestei soluții este că pentru bazele de date mari, verificarea sumei de control poate încărca serios sistemul.
Nu faceți copii de rezervă ale fișierelor pe același disc fizic care stochează baza de date sau jurnalul de tranzacții.
Dacă utilizați MS SQL 2008 sau o versiune ulterioară, vă recomand să utilizați compresia de rezervă SQL. Următorul cod va activa implicit compresia: USE master; GO EXEC sp_configure „compresie implicită de rezervă”, „1”; RECONFIGURAȚI CU OVERRIDE;
Păstrați copiile de siguranță timp de câteva zile în cazul în care una dintre ele este coruptă - o copie de rezervă veche este mai bună decât nici o copie de rezervă.
Utilizare DBCC CHECKDB pentru a verifica fiecare bază de date înainte de copiere, acest lucru vă va alerta cu promptitudine asupra problemelor iminente. DBCC CHECKDB(„Numele bazei de date”) WITH NO_INFOMSGS, ALL_ERRORMSGS; Notă:În practică, am folosit această verificare numai înainte de a efectua o copie de rezervă completă.
Actualizați periodic statisticile și reorganizați indexurile bazelor de date

Folosind aplicația

Câteva nuanțe despre aplicație:

Toate textele și solicitările din cod sunt incluse în resurse, mi-a fost mai ușor
Când introduceți parametrii de conexiune și alte setări, acestea sunt salvate într-un fișier. Pentru Express și Standard, sunt utilizate fișiere diferite (dbStandart, udExpress) și clasa UserData este stocată în ele
Unele operațiuni pot necesita drepturi de administrator
Momentan, conexiunea la baza de date sub un cont de domeniu nu funcționează
Programul nu are o interfață super frumoasă

1. Configurarea notificării administratorului

Mi-a fost prea lene să mă conectez la server de fiecare dată și să verific dacă sarcina a funcționat sau a apărut o eroare. Și am vrut să pot primi alte notificări, nu doar despre finalizarea sarcinii.

DatabaseMail MS SQL este utilizat în acest scop (pentru versiunea Standard și superioare)
În aplicația mea am făcut o secțiune specială pentru a automatiza această sarcină

Când faceți clic, va apărea un formular pentru completarea informațiilor necesare pentru a crea un profil de corespondență:

Aplicația este configurată automat la portul SMTP standard 25 pentru adresa de la care sunt trimise scrisorile. Dacă este necesar, poate fi schimbat în procedura sysmail_add_account_sp
Un utilizator și o parolă sunt necesare în cazul în care serviciul de e-mail are autentificare configurată.

Numele operatorului din sistem este indicat astfel încât să putem crea corect un profil în DatabaseMail. Scrie orice nume care îți va fi clar. Mai jos este un exemplu de formular completat.

Parametrii sistemului MS SQL se modifică.
Profilul DatabaseMail este creat
Activat în profilul SQL Agent
Contul DatabaseMail este creat
Adăugarea unui cont DatabaseMail la profilul Database Mail
DatabaseMail Operator este creat

Este descris mai detaliat în cele ce urmează și, parțial, am luat-o de aici. Desigur, aceste acțiuni pot fi efectuate folosind SSMS.

2.Notări suplimentare pentru administrator

Programul oferă 2 sarcini aplicate bazei de date:

verificarea integrității bazei de date. Procedura standard DBCC CHECKDB a fost folosită pentru a verifica baza de date.
informarea despre spațiul liber în grupurile de fișiere.
A doua sarcină a fost implementată folosind o interogare la tabelul de sistem dbo.sysfiles
Iată un exemplu de această interogare care a fost executată în baza de date:

Selectați NUME = stânga(a.NAME,15), a.FILEID, = convert(zecimal(12,2),round(a.size/128.000,2)), = convert(zecimal(12,2),round( fileproperty(a.name,"SpaceUsed")/128.000,2)), = convert(zecimal(12,2),round((a.size-fileproperty(a.name,"SpaceUsed"))/128.000,2) ) , FILENAME = a.FILENAME Din dbo.sysfiles a
Răspunsul de la server vine la e-mailul administratorului sub formă de markup html. Această sintaxă este posibilă datorită următoarei funcții standard MS SQL FOR XML.

De asemenea, în timp ce căutam cum să convertesc rezultatul returnat al interogărilor în text html, am dat peste următoarea pagină, unde o persoană a creat o procedură întreagă în aceste scopuri
Puteți configura aceste operații folosind elementul corespunzător din meniul programului:

Va apărea o fereastră pentru a indica căsuța poștală la care trebuie trimis textul html al raportului:

3. Rezolvarea problemelor la configurarea DatabaseMail

În MS SQL 2008 am întâmpinat o problemă la configurarea Agentului SQL Server. Simptomele sunt următoarele: după configurare, este imposibil să porniți SQL Agent. Acest lucru este rezolvat în principal prin instalarea actualizării pe serverul SQL.

Dacă aceste actualizări nu ajută, trebuie să descărcați remedierea. Poate fi găsit pe acest site; nu pot furniza linkul final chiar acum pentru a ajunge la pachetul de reparații, va trebui să răspundeți la o serie de întrebări.
Dacă există probleme cu modulul DatabaseMail. După configurarea acestui modul folosind aplicația, trebuie să accesați Agentul SQL și să vizualizați jurnalul de evenimente. Dacă există erori „nu se poate conecta la căsuța poștală”. Aceasta înseamnă că există o problemă, chiar dacă scrisoarea este trimisă prin verificare.

Acest lucru poate fi corectat prin următoarele manipulări:

Management Studio - Agent SQL Server - Proprietăți.
Sistem de alertă
Debifați Enable mail profile
Faceți clic pe OK
Conectați-vă din nou și bifați caseta
Reporniți SQL Server Agent.

Verificați-vă contul pentru serviciul SQL Agent. Dacă acesta este un cont de domeniu, schimbați-l într-un cont de sistem sau invers. Totul ar trebui să funcționeze.

4.Configurați backup folosind aplicația SQL Standard:

Selectați versiunea Standard. Configurarea notificărilor. (vezi secțiunea, setări de notificare):

Ne conectăm la baza de date, completând datele de conectare și specificând baza de date pentru care va fi utilizat Job-ul:

Selectați setarea de rezervă:

Specificați căile pentru salvarea copiilor bazei de date. Dacă folderele specificate nu există, programul va încerca să le creeze (sunt necesare drepturile corespunzătoare).

Faceți clic pe salvare și sarcinile corespunzătoare sunt configurate în baza de date. Este recomandabil să configurați foldere diferite pentru fiecare backup, deoarece... La ștergere, toate fișierele cu extensia bak vor fi șterse. (cm. secțiunea de ștergere a copiilor bazei de date)

5.Configurați backup folosind o aplicație pentru SQL Express:

Deoarece SQL Express nu are un agent SQL, sarcina de a automatiza backup-urile a trebuit să fie rezolvată într-un mod diferit. Un fișier bat este creat în folderul specificat de utilizator, care descrie interogarea SQL responsabilă pentru crearea copiei de rezervă. Dacă este necesar, îl puteți edita direct. În plus, programul standard Windows ar trebui să funcționeze, creează o sarcină care va rula o dată pe zi, la ora specificată.

Pentru a face acest lucru, lansați aplicația. Selectați MS SQL Express:

Apare un formular pentru completarea parametrilor:

Indicăm unde va fi salvată copia noastră, precum și unde va fi amplasat fișierul bat pentru a crea o copie a bazei de date (nu este nevoie să specificați numele fișierului, acesta va fi specificat automat). În continuare, specificăm setările de conectare și ora la care trebuie lansată sarcina.

Singurul dezavantaj al acestei abordări este că parola pentru conectarea la baza de date trebuie să fie stocată în text clar.

6.Ștergerea sarcinilor din baza de date.

Dacă trebuie să ștergeți toate sarcinile din baza de date (de exemplu, ați dorit să schimbați căile pentru a salva baza de date). Pentru a face acest lucru, utilizați elementul corespunzător din meniul programului. Toate sarcinile cu un anumit prefix de început (în cazul meu King) vor fi șterse din SQL Agent:

7.Ștergerea copiilor bazei de date

În unele sarcini, vechile copii ale bazei de date sunt configurate pentru a fi șterse. Pentru a face acest lucru, folosesc procedura master.dbo.xp_delete_file. Exemplu de utilizare: va șterge toate fișierele cu extensia bak din folderul specificat a cărui dată de creare este mai veche de 14 zile.
EXECUTE master.dbo.xp_delete_file 0,"E:\backups",N"bak",dateadd(d,-14,getdate()),0;
Și iată un alt exemplu mai detaliat și informații despre ce parametri ia această funcție.

Cum să restabiliți copiile de siguranță

Din cauza lipsei de timp, modulul de recuperare nu a fost încă implementat, poate că în viitor îl voi adăuga, dar deocamdată voi descrie pe scurt cum poate fi restaurată baza de date.

Folosind scriptul SQL. Pentru a restaura o bază de date, utilizați comanda RESTORE.

Dacă trebuie doar să restaurați baza de date dintr-o copie completă, atunci rulați următorul script:
RESTAURARE BAZĂ DE DATE [Numele bazei de date] FROM DISK = "Z:\SQLServerBackups\back.bak" CU ÎNLOCUIT
Dacă trebuie să restaurați secvențial o copie completă, copii diferențiale și jurnalele de tranzacții, atunci trebuie să scrieți următorul script SQL.

RESTORE DATABASE TEST_DB – restaurarea unei copii complete FROM test_db_full WITH NORECOVERY; GO RESTORE DATABASE TEST_DB – restaurarea copiei diferenței FROM test_db_diff WITH FILE = 1, NORECOVERY; GO RESTORE LOG TEST_DB – restaurați jurnalul de tranzacții nr. 1 DE LA test_db_tran_1 WITH FILE = 1, WITH NORECOVERY; GO RESTORE LOG TEST_DB – restaurarea jurnalului de tranzacții nr. 2 DE LA test_db_tran_2 WITH FILE = 1, WITH NORECOVERY; RESTAURAȚI BAZA DE DATE TEST_DB CU RECOVERY; MERGE
De asemenea, puteți utiliza SSMS pentru a restaura baza de date.

Etichete:

Backup MS SQL
Backup MS SQL

Adaugă etichete