in het openingshoofdstuk van Craig Mullin ’s boek, Database Administration, zegt hij “in veel opzichten is business today data”. Binnen de meeste organisaties de persoon die verantwoordelijk is voor het beschermen van gegevens is de database administrator… u.

dat klopt; het hele bedrijf is in jouw capabele handen, draait op die server die nooit crasht, met al die eindgebruikers die nooit fouten maken met behulp van applicaties, gebouwd door die ontwikkelaars die foutloze code schrijven de eerste keer, elke keer, met de bekwame hulp van die nieuwe co-op die ‘sa’ privileges heeft dankzij je baas.

OK. Stop met huilen. Er zijn dingen die je kunt doen om de SQL Server gegevens onder uw zorg te beschermen en een van de belangrijkste is het uitvoeren van regelmatige database back-ups.

back-ups

Microsoft definieert in SQL Server Books Online back-ups als:

een kopie van gegevens die wordt gebruikt om gegevens te herstellen en te herstellen na een systeemstoring

SQL-back-ups kunnen op een aantal manieren worden gemaakt en kunnen alle of een deel van de gegevens bevatten, evenals een deel van het transactielogboek. Hoewel dit artikel is gericht op 2005 syntaxis, de meeste van de concepten zijn van toepassing op 2000. Dit is een enorm onderwerp. In het beste geval ga ik je genoeg informatie geven zodat je niet meer gaat huilen. Na het lezen van dit, moet u in staat zijn om het opzetten van een redelijke set van back-ups voor uw systeem.

Herstelmodellen

om aan back-ups te beginnen, definieert de business needs een database herstelmodel. In essentie, een herstelmodel bepaalt wat je gaat doen met de transactie log gegevens.

er zijn drie herstelmodellen: Full, Simple en Bulk Logged. Deze zijn vrij eenvoudig te definiëren:

• eenvoudig-in de eenvoudige herstelmodus wordt geen back-up gemaakt van het transactielogboek, zodat u alleen kunt herstellen naar de meest recente volledige of differentiële back-up.
• volledig-in de volledige herstelmodus maakt u een back-up van de database en het transactielogboek, zodat u de database op elk moment kunt herstellen.
• bulk Logged-in bulk logged mode, de meeste transacties worden opgeslagen in het transactielogboek, maar sommige bulk operaties zoals bulk ladingen of index creatie worden niet geregistreerd.

de twee meest gebruikte modi zijn eenvoudig en vol. Ga er niet per se van uit dat, natuurlijk, je altijd nodig hebt om volledig herstel te gebruiken om uw gegevens te beschermen. Het is een zakelijke beslissing. Het bedrijf gaat om u te vertellen als je nodig hebt om te herstellen naar een punt in de tijd of als je gewoon de laatste volledige back-up nodig. Het gaat om te bepalen of uw gegevens kunnen worden hersteld door andere middelen, zoals handmatige invoer, of als je zoveel mogelijk te beschermen als het komt over de draad. U gebruikt eenvoudig herstel als u zich kunt veroorloven om de gegevens die zijn opgeslagen sinds de laatste volledige of differentiële back-up te verliezen en/of u hoeft gewoon niet herstel nodig om een punt in de tijd. In de eenvoudige modus moet u alle secundaire lees/schrijf bestandsgroepen herstellen wanneer u de primaire herstelt. U gebruikt eenvoudig meestal op secundaire databases die niet een absoluut essentieel onderdeel van de onderneming of rapportagesystemen zijn, met alleen-lezen toegang, zodat er geen transactielogboek om je zorgen over hoe dan ook. U gebruikt Volledige als elk bit van de gegevens van vitaal belang is, moet u herstellen naar een punt in de tijd of, meestal in het geval van zeer grote databases (VLDB), moet u individuele bestanden en bestandsgroepen onafhankelijk van andere bestanden en bestandsgroepen herstellen.

met zowel eenvoudige als volledige herstelmodellen kunt u nu een Copy-Only back-up uitvoeren waarmee u de database naar een back-upbestand kunt kopiëren, maar dit heeft geen invloed op het logboek, differentiële back-upschema ‘ s of impactherstel tot op een bepaald moment. Ik zal proberen om zo veel van deze onderwerpen als mogelijk door het artikel, maar niet de bestanden en filegroups.

werken met eenvoudig herstel

genoeg gepraat. Laten we beginnen met back-ups maken. Laten we aannemen dat we in eenvoudige herstel op een kleine tot middelgrote database. Ik ga AdventureWorks gebruiken voor alle voorbeeldscripts. Om het op een eenvoudige recovery:

Je eenvoudigste back-up strategie wordt uitgevoerd, op regelmatige tijdstippen, de volgende SQL Server back-up opdracht die zal het uitvoeren van een volledige back-up van de database:

waarom al dat typen dat je vraagt? Hebben we geen GUI tools om het werk voor ons af te handelen? Ja, de meeste eenvoudige back-ups kunnen worden uitgevoerd met SQL Server Management Studio. Echter, als u wilt leren en begrijpen wat Management Studio voor u doet, of als u wilt een fijnkorrelige controle over wat er een back-up, hoe en waar, dan ga je moet breken uit het toetsenbord en zet de muis weg.

het bovenstaande commando zal een basisback-up naar de schijf neerslaan. De meeste DBA ‘ s die ik ken back-up naar Bestand en schrap de bestanden op een tape of een andere media. Dit komt omdat bestanden op de schijf eenvoudig en snel te herstellen zijn, terwijl media soms een beetje pijn kan zijn. Bijvoorbeeld, we hebben over het algemeen twee tot drie dagen ter waarde van back-ups op onze bestandssystemen voor onmiddellijk herstel. We gaan alleen naar de tape-systemen als we herstellingen voor oudere back-ups moeten draaien.

wat deed dat Commando? Het maakte een kopie van alle gecommitteerde gegevens in de database. Het kopieerde ook niet-gecommitteerde log entries. Deze worden gebruikt tijdens het herstel of commit of rollback wijzigingen die zich voordeden aan de gegevens tijdens het back-upproces.

back-ups alleen kopiëren

normaal gesproken heeft een back-up van een database invloed op andere back-up-en herstelprocessen. Bijvoorbeeld na het uitvoeren van de vorige opdracht, elke differentiële back-ups (een back-up die alleen de gegevens die zijn gewijzigd sinds de laatste back-up kopieert) zou dit gebruiken als het startpunt voor gegevenswijzigingen, niet de back-up die u gisteravond hebt uitgevoerd. Zoals eerder opgemerkt, introduceert SQL 2005 een nieuw concept voor back-ups, COPY_ONLY back-ups, waarmee we kunnen voorkomen dat de cyclus wordt onderbroken:

we hebben al een van die meer korrelige momenten gevonden waarop de Management Studio je niet wilde helpen. Als u alleen een kopie back-up wilt, moet u de opdrachtregel gebruiken.

differentiële back-ups

laten we even aannemen dat we nog steeds in eenvoudige herstelfase zitten, maar dat we te maken hebben met een grotere database, Laten we zeggen iets groter dan 100 GB in grootte. Volledige back-ups kunnen eigenlijk beginnen te vertragen het proces een beetje. In plaats daarvan, na overleg met het bedrijf, hebben we besloten om een wekelijkse volledige back-up en dagelijkse differentiële back-ups te doen. Differentiële back-ups maken alleen een back-up van de gegevenspagina ‘ s die zijn veranderd sinds de laatste volledige back-up. Hieronder volgt het SQL backup commando om een differentiële back-up uit te voeren:

als we nu deze database moesten herstellen, zouden we eerst naar de laatste volledige back-up gaan, die herstellen en dan de differentiële back-ups in volgorde herstellen (daarover later meer).

er zijn een aantal andere back-up opties die ik hier niet zal detailleren. Lees de boeken online om details te zien over BLOCKSIZE, EXPIREDATE, RETAINDAYS, wachtwoord, naam, statistieken, enzovoort.

u kunt ook een statement uitvoeren dat de integriteit van een databaseback-up controleert. Het controleert niet de integriteit van de gegevens in een back-up, maar het controleert wel of de back-up correct en toegankelijk is geformatteerd.

Full recovery and log backups

we hebben voornamelijk gewerkt aan een database die zich in de eenvoudige herstelmodus bevond (dit werd vroeger afgekapt Log op Checkpoint genoemd). In deze modus maken we geen back-up van de transactielogboeken voor later herstel. Elke back-up onder dit mechanisme is een database back-up. Log back-ups zijn gewoon niet mogelijk.

echter, u hebt alleen de gegevens beschermd vanaf de laatste goede back-up, ofwel volledig of differentieel. Laten we onze aannames veranderen. Nu hebben we te maken met een grote, bedrijfskritische applicatie en database. We willen deze database tot op het laatste moment kunnen herstellen. Dit is een zeer belangrijk punt. In theorie, omdat de logboeken worden opgeslagen en geback-upt, zijn we beschermd tot het punt van een storing. Echter, sommige storingen kunnen leiden tot corruptie van het logboek, waardoor herstel tot een punt in de tijd onmogelijk. Dus, we moeten bepalen wat de redelijke minimale tijd tussen log back-ups zal zijn. In dit geval kunnen we leven met niet meer dan 15 minuten aan verloren gegevens.

dus, laten we beginnen met het zetten van onze database in volledige herstelmodus:

Dan, op een geregelde basis, in dit geval om de 15 minuten, we zullen het uitvoeren van de SQL-opdracht voor de transactie log:

dit script maakt een back-up van vastgelegde transacties vanuit het transactielogboek. Het heeft markeringen in het bestand die de start en stop tijd tonen. Het zal het logboek afkappen wanneer het succesvol is voltooid, en de toegezegde transacties die naar het back-upbestand zijn geschreven uit het transactielogboek verwijderen. Indien nodig kunt u het statement WITH NO_TRUNCATE gebruiken om gegevens uit het transactielogboek op te nemen, ongeacht de status van de database, ervan uitgaande dat deze online is en niet in een noodtoestand. Dit is alleen voor noodgevallen.

merk op dat we in dit geval geen gebruik maken van het init statement, maar u kunt dit doen als u daarvoor kiest. Bij het maken van log back-ups, je hebt opties:

1. Voer alle back-ups naar een enkel bestand, waar ze zullen stack en alles wat je hoeft te doen, op Herstellen (later behandeld), is cyclus door hen.
2. Geef de back-ups een unieke naam, waarschijnlijk met datum en tijd in de tekenreeks.

in dat laatste geval, zegt de veiligheid, gebruik INIT omdat je maximale controle uitoefent over wat waar wordt geback-upt, en je zult in staat zijn om precies te weten wat een back-up is, wanneer het werd genomen en van waar gebaseerd op de naam. Dit is nog een andere plek waar het bedienen van back-ups vanaf de opdrachtregel je meer controle geeft dan de GUI. We hebben beide benaderingen gebruikt in onze systemen om verschillende redenen. U kunt beslissen wat het beste is voor uw technologie en zakelijke eisen.

de meeste beschikbare opties voor de databaseback-up zijn opgenomen in de logback-up, inclusief COPY_ONLY. Hiermee kunt u een set transactiegegevens vastleggen zonder het logboek of de volgende geplande reservekopie van het logboek te beïnvloeden. Dit zou handig zijn voor het nemen van productiegegevens naar een ander systeem voor het oplossen van problemen enz.

als uw database is ingesteld op volledig herstel, moet u log backups uitvoeren. Soms, mensen vergeten en het transactielogboek groeit tot het punt dat het vult de disk drive. In dit geval kunt u uitvoeren:

het koppelen van NO_LOG aan de log backup, en het niet specificeren van een locatie voor het log, zorgt ervoor dat het inactieve deel van het log te worden verwijderd en het doet dit zonder een log vermelding zelf, waardoor het verslaan van de volledige disk drive. Dit is absoluut niet aan te raden omdat het breekt de log keten, de reeks van log back-ups van waaruit u uw database zou herstellen tot een punt in de tijd. Microsoft raadt het uitvoeren van een volledige back-up onmiddellijk na het gebruik van deze verklaring. Verder, ze waarschuwen dat deze verklaring kan worden afgekeurd in een toekomstige release.

Databases herstellen

zo belangrijk als back-ups van SQL Server zijn, en ze zijn essentieel, ze zijn nutteloos zonder de mogelijkheid om de database te herstellen.

een volledige databaseback-up herstellen

een volledige databaseback-up herstellen is zo eenvoudig als het was:

het is echt zo eenvoudig – tenzij, als we we back-up van alles naar een bestand alsof het een back-up apparaat. In dat geval, je nodig hebt om op te geven welk bestand binnen de “apparaat” u toegang tot. Als u niet weet welk bestand, moet u een lijst genereren:

dit geeft je dezelfde lijst als ik hierboven liet zien van Management Studio. Dus nu, als we wilden het tweede bestand in de groep te herstellen, de COPY_ONLY back-up, zou je het volgende commando:

helaas, als je volgt langs, kunt u vinden dat u net gegenereerd deze fout:

wat dit betekent is, dat uw database is in volledige herstelmodus, maar je hebt niet een back-up van de” staart van de log”, wat betekent dat de transacties ingevoerd sinds de laatste keer dat u een back-up liep. U kunt deze eis overschrijven als u de vorige syntaxis wijzigt naar:

dat is de eerste keer dat we de met-clausules hebben gestapeld (met FILE=2 en met REPLACE wordt weergegeven als met FILE=2, REPLACE), maar het zal niet de laatste zijn. Lees de boeken online. De meeste met-clausule statements kunnen worden gebruikt in combinatie met de andere.

Wat gebeurt er als we een andere database willen herstellen dan het origineel? We willen bijvoorbeeld een kopie van onze database maken vanuit een aparte back-up. Misschien willen we het verplaatsen naar een production support server waar we wat werk aan gaan doen, los van de productie kopie van de database. Als we de eenvoudige aanpak nemen, probeer dan dit:

In dit geval moet u een hele reeks fouten zien met betrekking tot bestanden die niet worden overschreven. Je kunt echt nieuwe databases maken van back-ups, maar als je het doet op een server met de bestaande database, moet je de locatie van de fysieke bestanden wijzigen met behulp van de logische namen. Om de logische namen van de bestanden voor een bepaalde database te weten, voer dit uit voordat u probeert de bestanden te verplaatsen:

Dit kan vervolgens worden gebruikt voor het identificeren van de juiste logische namen voor het genereren van dit script:

terugzetten van een differentiële back-up

de laatste methode is het toepassen van de differentiële back-up. Dit vereist twee stappen. Eerst herstellen we de database, maar met een twist en dan passen we de differentiële back-up toe.:

het meeste hiervan is waarschijnlijk vanzelfsprekend gebaseerd op wat we al hebben besproken. De enige rimpel is het opnemen van het NORECOVERY trefwoord. Heel eenvoudig, tijdens een herstel, transacties kunnen zijn gestart tijdens het back-upproces. Aan het einde van een herstel worden voltooide transacties naar voren gerold in de database en onvolledige transacties worden teruggerold. Het instellen van NORRECOVERY houdt transacties open. Dit zorgt ervoor dat de volgende set van transacties worden opgehaald van de volgende back-up in volgorde.

we hebben voornamelijk te maken met eenvoudige back-ups en herstelt in dit artikel, maar een meer geavanceerde herstel in 2005 maakt het mogelijk secundaire bestandsgroepen te herstellen terwijl de database online is. De primaire bestandsgroep moet tijdens de bewerking online zijn. Dit zal nuttiger zijn voor zeer grote databasesystemen.

het herstellen van SQL Server databases naar een punt in de tijd

het herstellen van logs is niet veel moeilijker dan het herstel van de differentiële database die we zojuist hebben voltooid. Er is gewoon een beetje meer betrokken bij het herstellen naar een moment in de tijd. Ervan uitgaande dat u een back-up van uw logs naar een enkel bestand of apparaat:

anders ga je gewoon en krijg je de bestandsnamen die je nodig hebt. Voer eerst het herstel van de database uit en zorg ervoor dat deze in een niet-herstelde staat blijft. Volg dit op met een reeks van log herstelt tot een punt in de tijd.

wat we nu hebben is een database die voldoet aan de exacte, laatste transactie om 14:30:29 op 23 oktober. Vergeet niet, tijdens multi-stap herstelt zoals deze, je moet de database te verlaten in een herstellende status. Dat betekent dat NORECOVERY aan elk statement wordt toegevoegd totdat u het herstelproces hebt voltooid. Als u om de een of andere reden NORECOVERY hebt toegevoegd aan al uw verklaringen, of u stopt gewoon in het Midden, en wilt de database weer online te brengen, kunt u deze verklaring gebruiken om het proces te voltooien:

Database snapshots

SQL Server 2005 introduceerde het concept van een snapshot, of een alleen-lezen, statische weergave van een database. Snapshots worden voornamelijk gemaakt om een alleen-lezen versie van een database te leveren voor rapportagedoeleinden. Echter, ze functioneren op een soortgelijke manier als back-ups. Het belangrijkste verschil is dat alle niet-gecommitteerde transacties worden teruggedraaid. Er is geen optie voor het vooruitrollen, het vastleggen van logs, enz., dat back-ups bieden, noch zijn zeer veel SQL Server resources gebruikt op alle. In plaats daarvan wordt schijftechnologie gebruikt om een kopie van de gegevens te maken. Hierdoor zijn ze veel sneller dan back-ups zowel te maken en te herstellen.

opmerking:
voor meer details over SQL 2005 Snapshot, zie http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

een goed gebruik van snapshots, naast het rapporteren, zou kunnen zijn om er een te maken voorafgaand aan onderhoud nadat u al Alle actieve gebruikers (en hun transacties) uit het systeem hebt verwijderd. Hoewel snapshots de volatiliteit van live back-ups niet ondersteunen, zijn hun snelheid en het gemak van herstel een geweldig hulpmiddel voor snel herstel van een mislukte uitrol. Snapshots worden opgeslagen op de server, dus je moet ervoor zorgen dat je voldoende opslag hebt.

de syntaxis is anders omdat u geen back-up maakt van een database; u maakt een nieuwe:

nu zal het toegankelijk zijn voor alleen-lezen toegang. Aangezien we voornamelijk bezig zijn met het gebruik van dit als een back-up mechanisme, laten we ook de methode voor het terugzetten van een database naar een database snapshot.

identificeer eerst de snapshot die u wilt gebruiken. Als er meer dan één in een database staat die u gaat terugdraaien, moet u ze allemaal verwijderen, behalve degene die u gebruikt.:

Vervolgens kunt u herstellen van de database door het uitvoeren van een HERSTEL-en verliesrekening (gemengde metaforen, niet goed):

Dat is het. Op mijn systeem, het uitvoeren van de database snapshots van Adventureworks duurde 136 ms. de volledige back-up nam 5,670 ms. het herstellen van de snapshot nam 905ms en de database te herstellen duurde 13,382 ms. Het opnemen van dit in een productie-uitrolproces kan resulteren in aanzienlijke voordelen

nogmaals, het is vermeldenswaard dat er enkele voorbehouden zijn aan het gebruik van de snapshot. Je moet genoeg schijfruimte hebben voor een tweede kopie van de database. Je moet voorzichtig omgaan met snapshots omdat de meeste syntaxis is vergelijkbaar met die gebruikt door databases zelf. Als laatste, terwijl er snapshots zijn gekoppeld aan een database, kunt u geen herstel uitvoeren vanuit een database-back-up van die database.

Best practices

de manier waarop u databaseback-ups uitvoert, mag geen technische beslissing zijn. Het moet worden gedicteerd door het bedrijf. Kleine systemen met lage transactie tarieven en/of rapportage systemen die regelmatig worden geladen zal alleen maar een volledige database back-up nodig. Middelgrote systemen en grote systemen worden afhankelijk van het soort gegevens dat wordt beheerd om te bepalen welke soorten back-up nodig zijn.

voor een middelgroot systeem zou een dagelijkse back-up met log-back-ups gedurende de dag waarschijnlijk tijdig voldoen aan de meeste gegevensvereisten.

voor een grote database is de beste aanpak om de back-ups te mixen en te matchen om maximale herstelbaarheid in minimale tijd te garanderen. Voer bijvoorbeeld een wekelijkse volledige back-up uit. Twee keer per dag door de week, doe een differentiële back-up. Elke 10 minuten gedurende de dag, voer een log back-up uit. Dit geeft u een groot aantal herstelmechanismen.

voor zeer grote databases moet u filegroup en file backups uitvoeren, omdat het mogelijk is om een volledige back-up of zelfs een differentiële back-up van de volledige database te maken. Een aantal extra functies zijn beschikbaar om te helpen op dit gebied, maar ik ga hier niet op in.

neem de tijd om enkele scripts te ontwikkelen voor het uitvoeren van uw back-ups en herstelt. Een naamgevingsconventie zodat u weet welke database, van welke server, vanaf welke datum, in welke specifieke back-up en indeling zeer bevorderlijk zal zijn voor uw geestelijke gezondheid. Een gemeenschappelijke locatie voor back-ups, log, volledig of Incrementeel, moet worden gedefinieerd. Iedereen die verantwoordelijk is moet worden opgeleid in zowel back-up en herstel en het oplossen van dezelfde problemen. Er zijn vele manieren om dit te doen, maar je kunt een paar suggesties vinden in Pop back-up en Pop herstelt.

de echte test is om uw back-upmechanismen uit te voeren en vervolgens een restore uit te voeren. Probeer dan een ander type herstel, en nog een, en nog een. Zorg ervoor dat, niet alleen heb je gedaan due diligence in het definiëren van hoe u een back-up van het systeem, maar dat je hebt gedaan de extra stap van ervoor te zorgen dat u die back-ups kunt herstellen. Als je dit niet hebt geoefend en de praktijk hebt gedocumenteerd en vervolgens het document hebt getest, ben je in feite nog niet klaar voor een ramp.

samenvatting

back-ups binnen uw onderneming moeten zijn als stemmen in Chicago, vroeg en vaak. Het opzetten van basis back-ups is vrij eenvoudig. Het toevoegen op log back-ups en differentiëlen is ook eenvoudig. Verken de opties om te zien hoe u back-ups van bestanden en bestandsgroepen kunt toevoegen en herstelt om de snelheid van uw back-ups te verhogen en herstelt die beide de beschikbaarheid van het systeem en de up-tijd zullen verhogen. Houd een gemeenschappelijke naamgevingsnorm. Wees voorzichtig bij het gebruik van snapshots, maar zeker gebruik maken van hen. Bewaar uw bestanden op een standaardlocatie tussen servers. Oefen je herstel. Tot slot, om echt uw back-ups zingen, download een gratis proefversie van Red Gate ‘ s SQL Backupâ¢. Het biedt hoogwaardige compressie en netwerkbestendigheid om het proces van het schrijven of kopiëren van back-ups over schilferige netwerken fouttolerant te maken.