Door: Atif Shehzad | Bijgewerkt: 2009-06-15 | Reacties (4) | Gerelateerd: Meer > Back-up

Probleem

Ik heb een back-upplan voor mijn SQL Server-databases geïmplementeerd. Ik maak dagelijks een volledige back-up ondersteund met elk uur een differentiële back-up. Op basis van dit back-upplan is een herstelplan gedocumenteerd, samen met de locatie en het tijdstip van de back-ups die in het plan worden vermeld. Vaak moet ik de test- of ontwikkelingsserver bijwerken met een recente kopie van de productiedatabase. Voor dit doel maak ik een volledige backup van de vereiste database. Het probleem is dat dergelijke ad hoc backups mijn geplande herstelvolgorde onderbreken in geval van een noodzakelijk herstel. Is er een manier om ervoor te zorgen dat mijn ad hoc backups de volgorde van mijn backup plan niet onderbreken?

Oplossing

Gelukkig hebben we in SQL Server 2005 en later een backup optie voor een dergelijke situatie. Deze backup optie staat bekend als Copy Only backups. Deze optie is specifiek voor het maken van een ad hoc backup die de geplande restore sequentie voor die database niet verstoort.

Copy Only-back-ups kunnen worden gebruikt voor het maken van een volledige back-up of een transactielogback-up. Deze optie is niet geïmplementeerd voor differentiële back-ups. In praktische scenario’s zult u zelden een Copy Only-logback-up hoeven te maken, maar de Copy Only-optie kan vaak worden gebruikt bij volledige back-ups.

Hoewel de Copy Only-optie beschikbaar is voor SQL Server 2005, is het niet mogelijk om deze met SSMS in SQL 2005 te maken. Als u SSMS 2008 hebt, kunt u deGUI gebruiken of moet u een T-SQL statement gebruiken om Copy Only-back-ups te maken.

Laten we eerst eens kijken hoe dit kan worden gedaan met behulp van SSMS 2008 om een Copy Only-backup te maken

• Ga naar het database back-up venster zoals u dat zou doen voor een normale back-up

• Net onder het “Backup type” menu, vindt u een selectievakje voor “Copy OnlyBackup”
• Klik op dit selectievakje
• Vul alle andere gerelateerde informatie in zoals u dat voor een normale back-up zou doen en klik op OK

Note: Het selectievakje voor “Copy Only Backup” is ook actief voor differentiële back-ups, maar het heeft geen ander effect dan het maken van een normale differentiële back-up.

Op de tweede plaats kunt u met T-SQL een Copy Only-back-up maken met het volgende commando:

-- Create full backup with Copy Only optionBACKUP DATABASE AdventureWorks TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'WITH COPY_ONLYGO

Het concept bewijzen

Voordat we verder gaan is het goed om het concept samen te vatten dat een volledige back-up met de copy only-optie onafhankelijk is van de volgorde van uw andere normale back-ups.Dus nadat u een back-up hebt gemaakt met de optie alleen kopiëren, kunt u met het herstelplan werken op basis van uw geplande back-ups zonder dat dit van invloed is op uw herstelproces.

We zullen LSN-informatie (logvolgordenummer) gebruiken om de volledige back-up te volgen die de basis vormt voor de differentiële back-ups. Eerst noteren we het LSN voor de differentiële basis van de AdventureWorks-database.

--Script # 1: Note current differential base LSNSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Note differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO

Het differentiële_basis_lsn is van invloed op de volgorde waarin de combinatie van back-ups bij een herstel moet worden teruggezet. U kunt ook gedetailleerde informatie over de LSN van elke database krijgen uit tabel msdb..backupset.

Nu moeten we bevestigen dat een volledige back-up zonder de optie Copy Only de differentiële_basis_lsn zal bijwerken. Hiertoe geven we een volledig back-upcommando zonder de optie Copy Only en noteren we de verandering in de LSN om te bewijzen dat de bijgewerkte differentiële basis-LSN overeenkomt met de LSN van onze laatste volledige back-up.

--Script # 2: Create full backup and compare LSN information-- Create full backup -- Run script after changing Backup pathBACKUP DATABASE AdventureWorks TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'GO-- Get differential_base_lsn after full backupSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Updated differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO-- Get LSN of recent full backup for match purposeSELECT database_name, backup_start_date, is_copy_only,first_lsn as 'LSN of full bakup'FROM msdb..backupset WHERE database_name = 'AdventureWorks'ORDER BY backup_start_date DESCGO

In de volgende resultatenreeks kunnen we controleren of de LSN voor de herstelbasis van de differentiële back-up is gewijzigd en overeenkomt met onze LSN voor de volledige back-up. Beide gemarkeerde LSN’s hieronder zijn hetzelfde, wat bevestigt dat de laatste back-up onze differentiële basis is.

Nu gaan we een volledige backup maken met de Copy Only optie en het zal worden bewezen dat de volledige backup met de Copy Only optie geen invloed heeft op de differentialbase LSN van onze database. Met andere woorden, de volledige back-up met de Copy Only optie zal geen invloed hebben op de basis volledige back-up voor de differentiële back-ups.

--Script # 3: Create full backup with copy only option and compare LSN information-- Create full backup with copy only option-- Run script after changing Backup pathBACKUP DATABASE AdventureWorks TO DISK = 'D:\CopyOnly_AdventureWorks.bak'WITH COPY_ONLYGO-- Get differential_base_lsn after full backup with copy only optionSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Un changed differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO-- Get LSN of recent full backup with copy only option for match purposeSELECT database_name, backup_start_date, is_copy_only,first_lsn as 'LSN of last full bakup'FROM msdb..backupset WHERE database_name = 'AdventureWorks'ORDER BY backup_start_date DESCGO

In de volgende afbeelding kunnen we zien dat na een volledige back-up met de optie Copy Only de differentiële basis-LSN ongewijzigd is en overeenkomt met de LSN van de vorige volledige back-up (beide zijn rood gemarkeerd). Merk ook op dat de laatste volledige back-up met de Copy Only optie er ook is (groen gemarkeerd).

De volgende overwegingen kunnen nuttig zijn bij het gebruik van back-ups met de Copy Only optie.

• De optie Copy Only werkt ook voor compatibiliteitsniveau 80-databases in een SQL Server 2005-instance
• Transactielogboekback-ups met de optie Copy Only behouden het bestaande logarchiefpunt, waardoor de transactielogboeken van die database niet worden afgebroken.
• Er hoeft geen extra aandacht te worden besteed aan het terugzetten van een back-up die met de optie Copy Only is gemaakt.
• Een volledige back-up met de Copy Only-optie kan niet worden gebruikt als basis voor het herstellen van differentiële back-ups
• Een logboekback-up met de Copy Only-optie kan worden gemaakt voor databases met herstelmodel volledig of alleen in bulk gelogd.
• Een volledige backup met de Copy Only optie kan worden gemaakt voor databases met elk herstelmodel.

Volgende stappen

Wanneer u een backup uit uw back-upplan haalt, overweeg dan om de Copy Only optie te gebruiken om verwarring en verstoring van uw herstelplan te voorkomen. Vergeet niet dat de Copy Only back-ups onafhankelijk zijn van normale back-ups.

• Dus de volgende keer dat u uw test- of ontwikkelomgeving moet vernieuwen, overweeg dan de Copy Only optie te gebruiken om uw herstelproces niet te onderbreken
• Voor informatie over database herstelmodellen klik hier of hier
• Hier vindt u een gerelateerde tip overCOPY ONLY back-ups

Last bijgewerkt: 2009-06-15

Over de auteur

Atif Shehzad is een gepassioneerd SQL Server DBA, technisch reviewer en auteur van artikelen.
Bekijk al mijn tips

Gerelateerde bronnen

• Meer SQL Server DBA Tips…