Lekce 4 - MS-SQL krok za krokem: Export

V minulé lekci, MS-SQL krok za krokem: Vkládání a mazání dat v tabulce, jsme si ukázali vkládání a mazání záznamů.

V tomto MS-SQL tutoriálu si ukážeme, jak exportovat data naší databáze a získat tak její zálohu. Vytvoření zálohy se nám bude jistě mnohokrát hodit ať už pro případné obnovení našich dat nebo jejich sdílení s ostatními.

Typy exportů

Export (nebo také "záloha") je soubor s SQL příkazy, který nám může sloužit na zálohu či migraci databáze nebo jen její části. Máme několik typů exportu:

• Kompletní export – Soubor bude obsahovat jak strukturu tabulek, tak i jejich data.
• Export struktury – Soubor bude obsahovat pouze strukturu databáze. Takový export může obsahovat všechny nebo pouze vybrané tabulky.
• Export dat – Soubor bude obsahovat pouze data tabulek. Tabulky můžeme specifikovat.

Příprava databáze

Než začneme, musíme mít nejprve k dispozici nějakou databázi s daty. Použijeme naši databázi s tabulkou Uzivatele, kterou jsme si tvořili v minulých lekcích. Tabulku nejprve celou vyprázdníme. Otevřeme si tedy okno pro vykonání T-SQL skriptu (v okně SQL Server Object Explorer klikneme na databázi pravým tlačítkem a vybereme New Query...) a zavoláme na tabulce Uzivatele příkaz TRUNCATE TABLE:

TRUNCATE TABLE [Uzivatele];

Tabulku Uzivatele následně znovu naplníme, abychom v ní měli všichni shodná data:

INSERT INTO [Uzivatele] (
    [Jmeno],
    [Prijmeni],
    [DatumNarozeni],
    [PocetClanku]
)
VALUES
    ('Jan', 'Novák', '1984-03-11', 17),
    ('Tomáš', 'Marný', '1989-02-01', 6),
    ('Josef', 'Nový', '1972-12-20', 9),
    ('Michaela', 'Slavíková', '1990-08-14', 1);

V databázi nyní tedy máme čtyři záznamy uživatelů:

Export dat

Zkusme si nejprve exportovat data konkrétní tabulky, v našem případě tabulky Uzivatele. Nejjednodušeji to můžeme udělat tak, že si přes okno SQL Server Object Explorer otevřeme data tabulky pomocí možnosti View Data:

Poté klikneme buď na tlačítko Script v horní liště, anebo pravým tlačítkem myši do tabulky a zvolíme Script:

V novém okně se nám vygeneruje tento T-SQL skript:

SET IDENTITY_INSERT [dbo].[Uzivatele] ON
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (1, N'Jan', N'Novák', N'1984-03-11', 17)
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (2, N'Tomáš', N'Marný', N'1989-02-01', 6)
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (3, N'Josef', N'Nový', N'1972-12-20', 9)
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (4, N'Michaela', N'Slavíková', N'1990-08-14', 1)
SET IDENTITY_INSERT [dbo].[Uzivatele] OFF

Skript nejprve nastaví, aby bylo možné při vkládání záznamů pomocí příkazu INSERT nastavit konkrétní hodnotu sloupce Id. Ten má totiž nastavenou Identity Specification a přiřazování hodnot daného sloupce je tak ve výchozím nastavení v režii databáze.

Poté už následují příkazy pro vložení všech záznamů. V případě, že některé záznamy kopírovat nechceme, tak jejich příkazy můžeme smazat.

Skript je zakončen obnovením výchozího chování Identity Specification.

Uložení scriptu do souboru

Skript si uložíme někam do souboru, protože jej budeme využívat pro budoucí import Učiníme tak jednoduše kliknutím na tlačítko pro uložení souboru v horním panelu, nebo klávesovou zkratkou CTRL + S:

Případně ho lze uložit rovnou při vytváření skriptu, kdy neklikneme na Script, ale na Script to File....

Export celé tabulky

Pokud chceme exportovat celou tabulku, tzn. jak data, tak i strukturu tabulky, pak je postup trochu složitější. Přesuneme se do okna SQL Server Object Explorer. Zde klikneme na tabulku Uzivatele pravým tlačítkem myši a zvolíme možnost Script As. Nabídnou se nám tři možnosti:

• CREATE To – Vygeneruje T-SQL skript na vytvoření tabulky.
• DROP To – Vygeneruje T-SQL skript na smazání tabulky.
• DROP And CREATE To – Vygeneruje T-SQL skript na smazání a následné znovuvytvoření tabulky.

Všechny tyto možnosti dále pak nabízí:

• New Query Window – Otevře vygenerovaný skript v novém okně.
• Clipboard – Vloží vygenerovaný skript do schránky.
• File – Uloží vygenerovaný skript do souboru.

My zvolíme DROP And CREATE To a následně New Query Window:

Otevře se nám okno s následujícím T-SQL skriptem:

USE [databazeProWeb]
GO

/****** Object: Table [dbo].[Uzivatele] Script Date: 26.06.2021 15:48:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[Uzivatele];


GO
CREATE TABLE [dbo].[Uzivatele] (
    [Id]            INT           IDENTITY (1, 1) NOT NULL,
    [Jmeno]         NVARCHAR (60) NOT NULL,
    [Prijmeni]      NVARCHAR (60) NOT NULL,
    [DatumNarozeni] DATE          NOT NULL,
    [PocetClanku]   INT           NOT NULL
);

Skript začíná příkazem USE, který určuje, na jaké databázi se mají následující příkazy provést. Výchozí je zdrojová databáze, při importu ji případně musíme změnit. Příkazy SET pro nás nejsou až tak zajímavé – jedná se pouze o nastavení chování databáze, které je vyžadováno pro správné vykonání dalších příkazů.

Stěžejní jsou zde nám již známé příkazy DROP TABLE a CREATE TABLE, které zajistí smazání a následné znovuvytvoření tabulky.

Příkaz GO pouze odděluje příkazy skriptu do jednotlivých skupin. Toto dělení využívají především nástroje zpracovávající daný skript, aby jej mohly správně vykonat.

Export struktury i s daty

Skript výše využijeme v situaci, kdy chceme exportovat pouze strukturu tabulky. My však chceme spolu se strukturou exportovat i data dané tabulky. Visual Studio však neumožňuje takový skript automaticky vygenerovat. To pro nás ale není zásadní problém, takový skript si můžeme vytvořit sami. Jednoduše spojíme oba skripty, které jsme si již vygenerovali.

Vrátíme se ke skriptu pro export dat, celý ho zkopírujeme (CTRL + C) a vložíme (CTRL + V) na konec skriptu pro export struktury. Dostaneme tak následující skript:

USE [databazeProWeb]
GO

/****** Object: Table [dbo].[Uzivatele] Script Date: 26.06.2021 15:48:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[Uzivatele];


GO
CREATE TABLE [dbo].[Uzivatele] (
    [Id]            INT           IDENTITY (1, 1) NOT NULL,
    [Jmeno]         NVARCHAR (60) NOT NULL,
    [Prijmeni]      NVARCHAR (60) NOT NULL,
    [DatumNarozeni] DATE          NOT NULL,
    [PocetClanku]   INT           NOT NULL
);


SET IDENTITY_INSERT [dbo].[Uzivatele] ON
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (1, N'Jan', N'Novák', N'1984-03-11', 17)
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (2, N'Tomáš', N'Marný', N'1989-02-01', 6)
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (3, N'Josef', N'Nový', N'1972-12-20', 9)
INSERT INTO [dbo].[Uzivatele] ([Id], [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES (4, N'Michaela', N'Slavíková', N'1990-08-14', 1)
SET IDENTITY_INSERT [dbo].[Uzivatele] OFF

Tento skript tedy zajistí smazání tabulky Uzivatele, vytvoření nové tabulky Uzivatele a nakonec vložení dat.

Skript si opět uložíme někam do souboru pro budoucí import, například pomocí CTRL + S.

Export celé databáze

Visual Studio umožňuje exportovat celou databázi jako tzv. data-tier application (DAC), která se přenáší v rámci balíčku DACPAC (soubor s příponou .dacpac). V podstatě se jedná o formát pro přenos celých MS-SQL databází včetně struktur všech tabulek a dat v nich obsažených. DAC se kromě zálohy dá taktéž použít pro nasazení databáze na cloud, například na Azure.

Balíček DACPAC vyexportujeme tak, že klikneme na databázi pravým tlačítkem myši v SQL Server Object Explorer a zvolíme Extract Data-tier Application...:

Otevře se nám dialog, kde zvolíme umístění a název souboru, nejjednodušeji kliknutím na Browse...:

Dále si můžeme zvolit, jestli chceme vyexportovat pouze strukturu celé databáze (Extract schema only), nebo i data (Extract schema and data). V případě druhé možnosti si můžeme vybrat konkrétní tabulky pomocí tlačítka Select Tables...:

Vyberme tedy umístění exportovaného souboru, možnost Extract schema and data se všemi tabulkami a tlačítkem OK celý export potvrďme. Úspěšnost exportu se vypíše v okně Data Tools Operations:

Tím máme k dispozici export celé naší databáze.

V další lekci, MS-SQL krok za krokem: Import, si ukážeme import databáze.