MS SQL - Sauvegarder & restaurer

1. Sauvegarde d'une base - Exemples
2. Restore BDD 01 - Exemple perte du fichier DATA unique - CAS n°1
3. Restore BDD 02 - Exemple perte du fichier DATA unique - CAS n°2
4. Restore BDD 03 - Exemple with Stopatmark & Stopbeforemark
5. Restore BDD 04 - Exemple with MOVE files
6. Restore BDD 05 - Exemple perte d'un seul fichier DATA - BDD multi-fichiers
7. Restore BDD 06 - Exemple Always ON AG -MarketDev - REPLACE

​Retour...

-- Exécutez une sauvegarde sur la même unité à l'aide de T-SQL
-- (Valable pour BDD non répliquée uniquement !!)
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin F:\MyBDD_BACKUPs_Folder\ par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table YourExempleTable par celui que vous souhaitez utiliser
--
-- Ce fichier recense des exemples de commandes pour faire une sauvegarde de base MS SQL
-- Remplacer MyBDD par le nom de votre base de données
-- Remplacer le chemin de destination des sauvegarde F:\MyBDD_BACKUPs_Folder fourni en exemple par le votre
--
-- Supprimer la BDD si elle existe déjà pour un précédent test 
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
-- Créer la base de données de test pour vérifier le mécanisme
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD', 
  FILENAME = N'D:\MyBDD\DATAs\MyBDD.mdf' , 
  SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 5120KB , FILEGROWTH = 10%);
GO

-- Sauvegarde avec les options par défaut.
BACKUP DATABASE MyBDD
TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak';
GO
-- Sauvegarde avec  l'option INIT.
BACKUP DATABASE MyBDD
TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak'
WITH INIT;
GO
-- Sauvegarde en mode compressée sans format. Le fichier ne pas pas déjà existé à la destination
BACKUP DATABASE MyBDD
TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak'
WITH INIT, COMPRESSION;
GO
-- Sauvegarde en mode compressée avec format.
BACKUP DATABASE MyBDD
TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak'
WITH FORMAT, COMPRESSION;
GO
-- Configurer votre instance pour utiliser la compression de la sauvegarde par défaut.
EXEC sp_configure 'backup compression default', 1;
GO
RECONFIGURE;
GO
-- Interrogation de la vue sys.configurations pour voir tous les paramètres de configuration,
-- Vérifier en particulier les valeurs possibles de compression de la sauvegarde.
SELECT * FROM sys.configurations ORDER BY name;
GO

---  Vérification des sauvegardes :
-----------------------------------
--  Interrogez les informations d'historique de sauvegarde via T-SQL.
--  A savoir que le type de sauvegarde :
--   D signifie une sauvegarde complète de la base de données,
--         I une sauvegarde différentielle
--   L une sauvegarde du journal des transactions. 
USE msdb;
GO
SELECT 
bs.media_set_id,
bs.backup_finish_date,
bs.type,
bs.backup_size,
bs.compressed_backup_size,
mf.physical_device_name
FROM dbo.backupset AS bs
INNER JOIN dbo.backupmediafamily AS mf
ON bs.media_set_id = mf.media_set_id
WHERE database_name = 'MyBDD'
ORDER BY backup_finish_date DESC;
GO
-- Utiliser RESTORE HEADERONLY pour récupérer les informations de sauvegarde à partir d'une unité de sauvegarde.
RESTORE HEADERONLY 
FROM DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak';
GO
-- Utiliser RESTORE FILELISTONLY pour obtenir la liste des fichiers contenus dans la sauvegarde.
RESTORE FILELISTONLY 
FROM DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak';
GO
-- Utilisez RESTORE VERIFYONLY FROM pour vérifier la sauvegarde.
RESTORE VERIFYONLY 
FROM DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.bak';
GO

​Retour...

-- Exemple de processus de restauration complet suite à la perte du fichier DATA unique (fichier MDF) à l'aide de T-SQL
-- NB : repose sur la nécessité de réaliser une sauvegarde de fin de journal avant opération de restauration 
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:\MyBDD\DATAs par celui ou seront placés les fichiers Datas de la BDD
-- Remplacer le chemin D:\MyBDD\LOGs par celui ou seront placés les fichiers journaux de la BDD
-- Remplacer le chemin F:\MyBDD_BACKUPs_Folder\ par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table MyTable par celui que vous souhaitez utiliser
-- Remplacer les noms de colonnes MyTableID, ExempleColString et ExempleColInt par ce qui vous convient - Vous pouvez aussi adapter en ajoutant d'autres colonnes
--
-- Se connecter à votre instance de BDD via SSMS

-- Supprimer la BDD si elle existe déjà pour un précédent test 
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
-- Création une base de données de test pour vérifier le mécanisme
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD', 
  FILENAME = N'D:\MyBDD\DATAs\MyBDD.mdf' , 
  SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 5120KB , FILEGROWTH = 10%);
GO
-- Définir la base de données en mode de récupération complète.
-- Rappel : le mode de récupération complète n'est actif qu'après la première sauvegarde complète de base de données,
-- En effet, les sauvegardes de journal nécessitent la réalisation préalable d'une sauvegarde complète.
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO
-- Lancer une sauvegarde complète de base de données. 
BACKUP DATABASE MyBDD
TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
WITH INIT;
GO
-- Création d'une table
USE MyBDD;
GO
SET NOCOUNT ON;
GO
CREATE TABLE MyTable
( MyTableID int IDENTITY(1,1) PRIMARY KEY,
  ExempleColString nvarchar(600),
  ExempleColInt bigint
);
GO
-- Insertion de 2000 lignes de données dans la table 
INSERT INTO MyTable (ExempleColString,ExempleColInt)
  VALUES('Voici des exemples de test',12345);
GO 2000

-- Vérifier que l'insertion est OK
Select * from dbo.MyTable
-- Stopper le serveur.
SHUTDOWN;
GO
-- Avec l'Explorateur Windows, supprimer le fichier MyBDD.mdf.
-- Relancer le serveur et son instance.
--         Pour cela, avec SCCM, via l'Explorateur d'objets, cliquez avec le bouton droit sur l'instance 
--         et cliquez sur Démarrer. Cliquez sur Oui pour confirmer le démarrage.

-- Vérifier que l'état de la base de données est RECOVERY_PENDING (puisque la récupération n'a pas pu être lancée)
Use master 
GO
SELECT name, state_desc 
FROM sys.databases
WHERE name = 'MyBDD';
GO

-- Essayer de sauvegarder le journal des transactions. Sachez qu'une erreur est renvoyée lorsque les fichiers sont inaccessibles
-- Message type ..."BACKUP LOG is terminating abnormally"
BACKUP LOG MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH INIT;
GO
-- Cette fois, essayer de réaliser une sauvegarde de fichier journal après défaillance.
-- Sachez qu'une sauvegarde de la fin du journal peut toujours être créée même si le fichier de données 
-- de la base de données est inaccessible. 
BACKUP LOG MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH INIT, CONTINUE_AFTER_ERROR;
GO

-- Lancement du processus de restauration :
--- Restauration de la dernière sauvegarde de base complète en mode NORECOVERY
--- Restauration de la sauvegarde de fin de journal en mode NORECOVERY
--- Lancement du processus de recovery
RESTORE DATABASE [MyBDD] FILE = N'MyBDD' FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD_full.bak' WITH  FILE = 1,  NORECOVERY, STATS = 10
GO
RESTORE LOG [MyBDD] FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.trn' WITH  FILE = 1,  NORECOVERY, STATS = 10
GO
RESTORE LOG [MyBDD] WITH  RECOVERY
GO
-- Vérification de l'état de la base de données qui doit désormais être à l'état ONLINE.
SELECT name, state_desc 
FROM sys.databases
WHERE name = 'MyBDD';
GO
-- S'assurer que toutes les données ont été restaurées. La table dbo.MyTABLE doit contenir 2 000 lignes.
USE MyBDD;
GO
SELECT * FROM dbo.MyTABLE;
GO


​

​Retour...

-- Exemple de processus de restauration complet suite à la perte du fichier DATA unique (fichier MDF) à l'aide de T-SQL
-- NB : dans la cas N°2, on considère qu'il est impossible de réaliser un backup de fin de journal avant opération de restauration
-- Le mode restauration est ici REPLACE - 
-- ATTENTION : dans ce cas, les modifications faites après le backup full seront perdues (aucun log restauré)
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:\MyBDD\DATAs par celui ou sera placé les fichiers Datas de la BDD
-- Remplacer le chemin D:\MyBDD\LOGs par celui ou sera placé les fichiers journaux de la BDD
-- Remplacer le chemin F:\MyBDD_BACKUPs_Folder\ par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table MyTable par celui que vous souhaitez utiliser
-- Remplacer les noms de colonnes MyTableID, ExempleColString et ExempleColInt par ce qui vous convient - Vous pouvez aussi adapter en ajoutant d'autres colonnes
--

-- Se connecter à votre instance de BDD via SSMS

-- Supprimer la BDD si elle existe déjà pour un précédent test 
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
-- Création une base de données de test pour vérifier le mécanisme
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD', 
  FILENAME = N'D:\MyBDD\DATAs\MyBDD.mdf' , 
  SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 5120KB , FILEGROWTH = 10%);
GO
-- Configurer la base de données en mode de récupération complète. 
-- Rappel : le mode de récupération complète n'est actif qu'après la première sauvegarde complète de base de données,
-- En effet, les sauvegardes de journal nécessitent la réalisation préalable d'une sauvegarde complète. 
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO
-- Lancer une sauvegarde complète de base de données. 
BACKUP DATABASE MyBDD
TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
WITH INIT;
GO
-- Création d'une table
USE MyBDD;
GO
SET NOCOUNT ON;
GO
CREATE TABLE MyTable
( MyTableID int IDENTITY(1,1) PRIMARY KEY,
  ExempleColString nvarchar(600),
  ExempleColInt bigint
);
GO
-- Insertion de données dans la table 
INSERT INTO MyTable (ExempleColString,ExempleColInt)
  VALUES('This is some testdata',12345);
GO 2000

-- Vérifier que l'insertion est OK
USE MyBDD
GO
Select * from dbo.MyTable
-- Stopper le serveur
USE master
GO
SHUTDOWN;
GO
-- Avec l'Explorateur Windows, supprimer le fichier MyBDD.mdf.
-- Relancer le serveur et son instance.
--         Pour cela, avec SCCM, via l'Explorateur d'objets, cliquez avec le bouton droit sur l'instance 
--         et cliquez sur Démarrer. Cliquez sur Oui pour confirmer le démarrage.

-- Vérifier que l'état de la base de données est RECOVERY_PENDING (puisque la récupération n'a pas pu être lancée)
-- Cette commande peut échouer à la première tentative - La relancer si c'est le cas
SELECT name, state_desc 
FROM sys.databases
WHERE name = 'MyBDD';
GO
--IMPORTANT : -- Cette fois, nous considérons qu'il n'est pas possible de réaliser une sauvegarde de fichier journal après défaillance.
-- Il faut donc tenter un autre processus de restauaration - Le mode REPLACE
-- Lancement du processus de restauration en mode REPLACE :
--- Restauration de la dernière sauvegarde de base complète en mode NORECOVERY et REPLACE 
--- Pas de restauration de sauvegarde de fin de journal car nous n'en avons pas dans cet exemple
--- Lancement du processus de recovery - Les logs présents et exploitables seront rejoués
RESTORE DATABASE [MyBDD] FILE = N'MyBDD' FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD_full.bak' WITH  FILE = 1,  NORECOVERY, REPLACE, STATS = 10
GO

RESTORE LOG [MyBDD] WITH  RECOVERY
GO
-- Vérifier l'état de la base
-- Elle est restaurée avec succès mais comme il n'y a pas eu de backup de fin de journal avant d'arrêter et de resaurer la base, les éléments
-- post-backup (création table Mytable et insertion de données sont perdues !!)
Use MyBDD
Select * from MyTable

.

​Retour...

-- Exemple de processus de restauration complet avec STOPATMARK à l'aide de T-SQL
-- NB : repose sur la nécessité de réaliser une sauvegarde de fin de journal avant opération de restauration 
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:\MyBDD\DATAs par celui ou sera placé les fichiers Datas de la BDD
-- Remplacer le chemin D:\MyBDD\LOGs par celui ou sera placé les fichiers journaux de la BDD
-- Remplacer le chemin D:\MyBDD_BACKUPs_Folder\ par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table MyTable par celui que vous souhaitez utiliser
-- Remplacer les noms de colonnes MyTableID, ExempleColString et ExempleColInt par ce qui vous convient - Vous pouvez aussi adapter en ajoutant d'autres colonnes
--
-- Se connecter à votre instance de BDD via SSMS
-- Supprimer la BDD si elle existe déjà pour un précédent test 
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
-- Créer la base de données de test pour vérifier le mécanisme
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD', 
  FILENAME = N'D:\MyBDD\DATAs\MyBDD.mdf' , 
  SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 5120KB , FILEGROWTH = 10%);
GO
-- Définir la base de données en mode de récupération complète.
-- Rappel : le mode de récupération complète n'est actif qu'après la première sauvegarde complète de base de données,
-- En effet, les sauvegardes de journal nécessitent la réalisation préalable d'une sauvegarde complète. 
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO
-- Lancer une sauvegarde complète de base de données. 
BACKUP DATABASE MyBDD
  TO DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH INIT;
GO
-- Création d'une table et ajout de données dans cette table
USE MyBDD;
GO
SET NOCOUNT ON;
CREATE TABLE MyTable
( MyTableID int IDENTITY(1,1) PRIMARY KEY,
  ExempleColString nvarchar(600),
  ExempleColInt bigint
);
GO
-- Ajout d'une première ligne dans la table
INSERT INTO MyTable (ExempleColString,ExempleColInt)
VALUES('Première ligne de la BDD!',3840);
GO
-- Lancement d'une transaction imbriquée pour insérer une seconde ligne dans la table 
-- Remarquez que la transaction est nommée et que la marque (MARK) comporte aussi une description.
-- IMPORTANT : la marque a le même nom que la transaction
USE MyBDD;
GO
BEGIN TRAN PriorToInsert WITH MARK 'Mark added prior to insert activity'
  INSERT INTO MyTable (ExempleColString,ExempleColInt)
    VALUES('ces données insérées sont marquées par une transaction',7495);
COMMIT TRAN PriorToInsert;
GO
-- Insertion d'une 3ème ligne dans la table puis affichage du contenu de la table.
INSERT INTO MyTable (ExempleColString,ExempleColInt)
VALUES('Ceci est une valeur post-transaction',2375);
GO
SELECT * FROM MyTable;
GO
 
-- Lancer une sauvegarde de fichier journal.
USE master;
GO
BACKUP LOG MyBDD
  TO DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH INIT;
GO
-- Processus de restauration :
------------------------------
--
----- Nous allons utiliser 3 processus :
--------- Le premier sans Stopatmark pour montrer que la base restaurée est récupérée jusqu'à la dernière insertion
--------- Le second avec Stopatmark pour montrer que la base restaurée est récupérée uniquement jusqu'à la dernière transaction marquée incluse
--------- Le dernier avec Stopbeforemark pour montrer que la base restaurée est récupérée uniquement jusqu'à avant la dernière transaction marquée

-- Commençons par une restauration la sauvegarde complète et la sauvegarde du journal classique (sans Stopatmark)
USE master;
GO
RESTORE DATABASE MyBDD
  FROM DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH REPLACE, NORECOVERY;
GO
RESTORE LOG MyBDD
  FROM DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH RECOVERY;
GO
---  Vérification du contenu de dbo.MyTable pour constater que toutes les lignes sont présentes. 
USE MyBDD;
GO
SELECT * FROM dbo.MyTable;
GO
-- Réalisons maintenant une restauration de la sauvegarde complète et du journal avec StopatMark 
-- sur la transaction qui avait été marquée
USE master;
GO
RESTORE DATABASE MyBDD
  FROM DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH REPLACE, NORECOVERY;
GO
RESTORE LOG MyBDD
  FROM DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH RECOVERY, STOPATMARK = 'PriorToInsert';
GO
---  Vérification du contenu de dbo.MyTable pour constater que la ligne insérée après l'ajout de la transaction marquée est absente. 
---  Seules 2 lignes sont affichées
USE MyBDD;
GO
SELECT * FROM dbo.MyTable;
GO
-- Réalisons maintenant une restauration de la sauvegarde complète et du journal avec StopbeforeMark 
-- sur la transaction qui avait été marquée
USE master;
GO
RESTORE DATABASE MyBDD
  FROM DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH REPLACE, NORECOVERY;
GO
RESTORE LOG MyBDD
  FROM DISK = 'D:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH RECOVERY, STOPBEFOREMARK = 'PriorToInsert';
GO
---  Vérification du contenu de dbo.MyTable pour constater que seule le première ligne est présente cette fois.
-- (il manque l'insertion de la transaction marquée et la suivante) 
---  Seule une ligne est affichée (la première ligne insérée)
USE MyBDD;
GO
SELECT * FROM dbo.MyTable;
GO
-- Supprimer la BDD à la fin de vos tests 
USE master;
GO
SELECT 1 FROM sys.databases WHERE name = N'MyBDD'
  DROP DATABASE MyBDD;
GO

.

​Retour...

-- Exemple de processus de restauration complet avec déplacement des fichier d'origine à l'aide de T-SQL
-- NB : repose ici sur la réalisation d'une sauvegarde de fin de journal avant opération de restauration 
-- Les anciens chemin sont identifiés par D:\MyBDD
-- Les nouveaux chemin sont identifiés par D:\MyBDD_NewPath
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:\MyBDD\DATAs et D:\MyBDD_NewPath\DATAs par ceux ou seront placés les fichiers Datas de la BDD
-- Remplacer le chemin D:\MyBDD\LOGs et D:\MyBDD_NewPath\LOGs par ceux ou seront placés les fichiers journaux de la BDD
-- Remplacer le chemin G:\MyBDD_BACKUPs_Folder\ par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table MyTable par celui que vous souhaitez utiliser
-- Remplacer les noms de colonnes MyTableID, ExempleColString et ExempleColInt par ce qui vous convient - Vous pouvez aussi adapter en ajoutant d'autres colonnes
-- Remplcer Your-domain\Your-Administrateur-SQL par le nom de votre domaine et de votre administrateur SQL
-- Se connecter à votre instance de BDD et créer une base de données de test pour vérifier le mécanisme
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD', 
  FILENAME = N'D:\MyBDD\DATAs\MyBDD.mdf' , 
  SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 5120KB , FILEGROWTH = 10%);
GO
-- Définir la base de données en mode de récupération complète.
-- Rappel : le mode de récupération complète n'est actif qu'après la première sauvegarde complète de base de données,
-- En effet, les sauvegardes de journal nécessitent la réalisation préalable d'une sauvegarde complète. 
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO
-- Lancer une sauvegarde complète de base de données. 
BACKUP DATABASE MyBDD
  TO DISK = 'G:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH INIT;
GO
-- Création d'une table
USE MyBDD;
GO
SET NOCOUNT ON;

CREATE TABLE MyTABLE
( MyTABLEID int IDENTITY(1,1) PRIMARY KEY,
  ExempleColString nvarchar(600),
  ExempleColINT bigint
);
GO
-- Ajout de données dans cette table via une boucle d'insertion (2000 lignes insérées)
DECLARE @Counter int = 0;
WHILE @Counter < 2000
BEGIN
  INSERT INTO MyTABLE (ExempleColString,ExempleColInt)
    VALUES('Test insertion de données',96745);
  SET @Counter += 1;
END;
GO
-- Sauvegarder le journal des transactions.
BACKUP LOG MyBDD
  TO DISK = 'G:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH INIT;
GO

-- Lancement du processus de restauration :
--- Restauration de la dernière sauvegarde de base complète en mode NORECOVERY
--- Restauration de la sauvegarde de fin de journal en mode NORECOVERY
--- Lancement du processus de recovery
USE master
GO
RESTORE DATABASE MyBDD
FROM DISK = 'G:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
WITH MOVE 'MyBDD' TO 'D:\MyBDD_NewPath\MyBDD.mdf',
     MOVE 'MyBDD_Log' TO 'D:\MyBDD_NewPath\LOGs\MyBDD.ldf',
REPLACE, NORECOVERY;
     GO
RESTORE LOG [MyBDD] 
FROM  DISK = N'G:\MyBDD_BACKUPs_Folder\MyBDD.trn' WITH  FILE = 1,  NORECOVERY, STATS = 10
GO
RESTORE LOG [MyBDD] WITH  RECOVERY
GO
-- Valider que la resytauration est OK
use MyBDD
select * from MyTABLE
-- Fournir les droits d'admin sur cette base restaurer à un administrateur de BDD
ALTER AUTHORIZATION ON DATABASE::MyBDD TO [Your-domain\Your-Administrateur-SQL];
GO


.

​Retour...

-- Exemple de restauration d'un seul fichier DATA manquant ou corrompu pour une BDD composée de plusieurs fichiers de BDD à l'aide de T-SQL
-- NB : repose sur la nécessité de réaliser une sauvegarde de fin de journal avant opération de restauration 
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:\MyBDD\DATAs par celui ou sera placé les fichiers Datas de la BDD
-- Remplacer le chemin D:\MyBDD\LOGs par celui ou sera placé les fichiers journaux de la BDD
-- Remplacer le chemin F:\MyBDD_BACKUPs_Folder\ par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table MyTable par celui que vous souhaitez utiliser
-- Remplacer les noms de colonnes MyColSTRING et MyColINT par ce qui vous convient - Vous pouvez aussi adapter en ajoutant d'autres colonnes
--
-- Imaginons une BDD MyBDD avec 2 fichiers DATA : 
-----  FILE-BDD-1.mdf (nom logique : MyBDD1)
-----  FILE-BDD-2.ndf (nom logique : MyBDD2)
-----  Le fichier journal est FILE-LOG.ldf

-- Supprimer la BDD si elle existe déjà si nécessaire
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO

-- Creer la BDD de test MyBDD avec les 2 fichiers de BDD :
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD1', 
  FILENAME = N'D:\MyBDD\DATAs\FILE-BDD-1.mdf' , 
  SIZE = 8192KB , FILEGROWTH = 1024KB ),
( NAME = N'MyBDD2', 
  FILENAME = N'D:\MyBDD\DATAs\FILE-BDD-2.ndf' , 
  SIZE = 8192KB , FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 4096KB , FILEGROWTH = 10%);
GO
-- PLacer la base de données en mode de récupération complète. Précisez que le mode de récupération complète prend effet après la première sauvegarde complète 
--         de base de données, car les sauvegardes de journal nécessitent une base. 
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO
-- Lancer une sauvegarde complète de base de données. 
BACKUP DATABASE MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH INIT;
GO
-- Création d'une table avec des données.
USE MyBDD;
GO
SET NOCOUNT ON;
CREATE TABLE MyTABLE
( MyTABLEID int IDENTITY(1,1) PRIMARY KEY,
  MyColSTRING nvarchar(600),
  MyColINT bigint
);
DECLARE @Counter int = 0;
WHILE @Counter < 2000
BEGIN
  INSERT INTO MyTABLE (MyColSTRING,MyColINT)
    VALUES('Test insertion de données',12345);
  SET @Counter += 1;
END;
GO
-- Shutdown du serveur.
SHUTDOWN;
GO
-- Supprimer le fichier D:\MyBDD\DATAs\FILE-BDD-1.mdf manuellement,
--         puis démarrez le serveur et son instance.
--         Pour cela, avec SCCM, via l'Explorateur d'objets, cliquez avec le bouton droit sur l'instance 
--         et cliquez sur Démarrer. Cliquez sur Oui pour confirmer le démarrage.
-- Ouvrer à nouveau la fenêtre de requête et vérifier que l'état de la base de donnée est RECOVERY_PENDING
SELECT name, state_desc 
FROM sys.databases
WHERE name = 'MyBDD';
GO


-- Essayer de sauvegarder le journal des transactions. Une erreur est renvoyée car des fichiers sont inaccessibles
BACKUP LOG MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH INIT;
GO
-- Cette fois, lancer une sauvegarde de fichier journal après défaillance.
BACKUP LOG MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH INIT, CONTINUE_AFTER_ERROR
GO
-- Désormais, la fin du journal a été sauvegardée. Il est donc possible de restaurer la base de données.
-- A noter que nous allons restaurer uniquement le fichier manquant de la sauvegarde complète 
-- de notre base de données pour démarrer la séquence de restauration. 
 
RESTORE DATABASE MyBDD
  FILE = 'MyBDD1'
  FROM DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD_full.bak'
  WITH NORECOVERY;
GO
-- Via l'Explorateur d'objets, cliquer avec le bouton droit sur Bases de données, puis sur Actualiser. 
-- L'état de la base de données MyBDD indique restauration.
-- Restauration de la sauvegarde de la fin du journal.
RESTORE LOG MyBDD
  FROM DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD.trn'
  WITH RECOVERY;
GO
-- Vérification de l'état de la base de données qui doit désormais être à l'état ONLINE.
SELECT name, state_desc 
FROM sys.databases
WHERE name = 'MyBDD';
GO
-- S'assurer que toutes les données ont été restaurées. dbo.MyTABLE doit contenir 2 000 lignes.
USE MyBDD;
GO
SELECT * FROM dbo.MyTABLE;
GO


.

​Retour...

-- Restauration d'une BDD sur une instance AlwaysOn via T-SQL
--
--
-- Remplacer Your-AlwaysOn-Instance-Name par le nom de l'instance AlwaysON de test que vous utiliserez

-- Remplacer Your-AlwaysOn-Database-Name-01 et Your-AlwaysOn-Database-Name-01 par le nom des bases de données de test que vous utiliserez
-- Remplacer Primary-SQL-Server-AlwaysON par le nom du serveur SQL primaire de vos instances AlwaysOn
-- Remplacer Secondary-SQL-Server-AlwaysON par le nom du serveur SQL secondaire de vos instances AlwaysOn
-- Remplacer Your-Domain-Name par votre nom de domaine court et your-SQL-Admin-Account par un login Windows qui a les droits d'admin sur vos instances de tests
-- Remplacer Your-Domain-FQDN par votre domaine FQDN
-- Remplacer Your-AlwaysOn-Group-Name par le nom du groupe AlwaysON
-- Remplacer D:\Your_BDD_Backup par le chemin à utiliser pour récuperer votre sauvegarde FULL
-- Remplacer D:\Your_LOGs_BDD_Backup par le chemin à utiliser pour récuperer votre sauvegarde des fichiers journaux
D:\Your_LOGs_BDD_Backup
--
--
-- Sur serveur de replique primaire :
-------------------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
REMOVE DATABASE [Your-AlwaysOn-Database-Name-01];
GO

-- Sur serveur de replique secondaire (Secondary-SQL-Server-AlwaysON):
----------------------------------------------------------------------

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Your-AlwaysOn-Database-Name-01'
GO
USE [master]
GO
DROP DATABASE [Your-AlwaysOn-Database-Name-01]
GO

-- Sur serveur de replique primaire (Primary-SQL-Server-AlwaysON):
------------------------------------------------------------------
-- NB : la base est désormais standalone sur le serveur primaire (la copie sur le serveur secondaire a été supprimée)
-- Restauration d'une sauvegarde antérieure sur le serveur primaire sans lancer de RECOVERY pour le moment pour permettre de restaurer ensuite les fichiers journaux
RESTORE DATABASE [Your-AlwaysOn-Database-Name-01] FILE = N'Your-AlwaysOn-Database-Name-01' FROM  DISK = N'D:\Your_FULL_BDD_Backup\Your-AlwaysOn-Database-Name-01-Primary-Full-BCK' WITH  FILE = 1,  NORECOVERY, REPLACE, STATS = 10
GO
-- Récupérer le ou les conteneurs de sauvegarde sur lesquels les sauvegardes de journaux de transactions ont été éffectuées
-- NB : dans l'exemple, le conteneur est Your-AlwaysOn-Database-Name-01_20140819105607.trn et il est placé sous D:\Your_LOGs_BDD_Backup
-- Vérifier le ou les conteneurs de sauvegarde qui contient les backups des journaux de transactions.
RESTORE HEADERONLY 
FROM DISK = 'D:\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01_20140819105607.trn';
GO
-- Utilisez RESTORE FILELISTONLY pour obtenir la liste des fichiers contenus dans la sauvegarde.
RESTORE FILELISTONLY 
FROM DISK = 'D:\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01_20140819105607.trn';
GO
-- Une seule sauvegarde des journaux de transactions doit être restaurée dans notre exemple
-- L'ID de fichier est 2 dans notre exemple
RESTORE LOG [Your-AlwaysOn-Database-Name-01] FROM  DISK = N'D:\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01_20140819105607.trn' WITH  FILE = 2,  NORECOVERY, STATS = 10
GO
-- Lancer cette fois le RECOVERY :
RESTORE LOG [Your-AlwaysOn-Database-Name-01] WITH  RECOVERY
GO
-- On  peut désormais remettre la base en mode répliquée Always ON :
USE [master]
GO
ALTER AVAILABILITY GROUP [Your-AlwaysOn-Group-Name]
ADD DATABASE [Your-AlwaysOn-Database-Name-01];
GO

BACKUP DATABASE [Your-AlwaysOn-Database-Name-01] TO  DISK = N'\\Primary-SQL-Server-AlwaysON\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
-- Sur serveur de replique secondaire (Secondary-SQL-Server-AlwaysON):
----------------------------------------------------------------------
-- Identification de la sauvegarde à restaurer :
RESTORE FILELISTONLY 
FROM DISK = '\\Primary-SQL-Server-AlwaysON\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01.bak';
GO
RESTORE DATABASE [Your-AlwaysOn-Database-Name-01] FROM  DISK = N'\\Primary-SQL-Server-AlwaysON\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01.bak' WITH FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

-- Sur serveur de replique primaire (Primary-SQL-Server-AlwaysON):
------------------------------------------------------------------
BACKUP LOG [Your-AlwaysOn-Database-Name-01] TO  DISK = N'\\Primary-SQL-Server-AlwaysON\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01_for_Repli.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

-- Sur serveur de replique secondaire (Secondary-SQL-Server-AlwaysON):
----------------------------------------------------------------------
-- Identification des ID de sauvegardes contenant les journaux de transactions à restaurer :
RESTORE FILELISTONLY 
FROM DISK = '\\Primary-SQL-Server-AlwaysON\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01_for_Repli.trn';
GO
-- Restoration des logs :
RESTORE LOG [Your-AlwaysOn-Database-Name-01] FROM  DISK = N'\\Primary-SQL-Server-AlwaysON\Your_LOGs_BDD_Backup\Your-AlwaysOn-Database-Name-01_for_Repli.trn' WITH FILE = 1, NORECOVERY,  NOUNLOAD,  STATS = 5
GO
-- Ensuite activation de la copie secondaire :
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier 
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes 
if (serverproperty('IsHadrEnabled') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'Your-AlwaysOn-Group-Name'
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay '00:00:10'
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [Your-AlwaysOn-Database-Name-01] SET HADR AVAILABILITY GROUP = [Your-AlwaysOn-Group-Name];
GO
-- Sur serveur de replique primaire (Primary-SQL-Server-AlwaysON):
------------------------------------------------------------------
-- Vérifier via SQL Server Management Studio
.

​Retour...

-- Comprendre la troncature du fichier journal d'une BDD MS SQL
--
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin C:\BACKUPs par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table MyTable par celui que vous souhaitez utiliser
--
--
-- Suppression de la BDD si existe déjà
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO

--  Création de la base Via T-SQL :
USE master;
GO
CREATE DATABASE MyBDD
ON 
( NAME = MyBDD_01, 
  FILENAME = 'D:\MyBDD\DATAs\Your-Database-File-01.mdf',   
  SIZE = 50MB, MAXSIZE = 400MB,  
  FILEGROWTH = 20% 
)
LOG ON
( NAME = MyBDD_02,
  FILENAME = 'D:\MyBDD\LOGs\Your-LOGFILE-File-01.ldf',  
  SIZE = 10MB, 
  MAXSIZE = UNLIMITED,  
  FILEGROWTH = 10MB 
);
GO 
-- Mettre la BDD en mode Recovery FULL
USE [master]
GO
ALTER DATABASE [MyBDD] SET RECOVERY FULL WITH NO_WAIT
GO
-- Sauvegarder complètement la base de données. 
BACKUP DATABASE MyBDD
  TO DISK = 'C:\BACKUPs\MyBDD.bak'
WITH INIT;
GO
-- Créer une table dans la base de données.
USE MyBDD;
GO
CREATE TABLE MyTable
( MyTableId int IDENTITY(1,1) PRIMARY KEY,
  FirstLargeColumn nvarchar(600),
  BigIntColumn bigint
);
GO
-- Éxaminer les propriétés des fichiers de base de données
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files;
GO
--  Insérer des données (8 000 lignes)
SET NOCOUNT ON;
INSERT INTO MyTable (FirstLargeColumn,BigIntColumn)
  VALUES('Ce sont des données tests',0678675432);
GO 8000
-- Réexaminer les propriétés du fichier journal de base de données dont sa taille.
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type = 1;
GO
-- Créer un point de contrôle. 
-- Noter que la commande CHECKPOINT peut être utilisée pour demander à SQL Server
-- d'effectuer un point de contrôle au lieu d'attendre la prochaine 
-- occurrence où il se produit automatiquement.
CHECKPOINT;
GO
-- Réexaminer les propriétés du fichier journal de base de données.
-- Noter que le point de contrôle n'a apporté aucune modification en termes
-- de taille -> aucune troncation n'a été effectuée.
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type = 1;
GO
-- Vérifier ce qui empêche la troncation du journal.
-- Faites défiler et notez la colonne log_reuse_wait_desc pour la base de données LogTest.
-- Elle affichera LOG_BACKUP comme raison
SELECT name, log_reuse_wait_desc FROM sys.databases;
GO
--Éxécutez une sauvegarde de fichier journal.
BACKUP LOG MyBDD
  TO DISK = 'C:\BACKUPs\MyBDD.bak'
WITH INIT;
GO
-- Éxaminez l'utilisation du fichier journal. Le fichier journal doit avoir été tronqué 
-- et de l'espace doit avoir été libéré 
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type = 1;
GO

--  Pour réduire la taille du fichier des journaux de transaction : 
USE MyBDD;
GO
DBCC SHRINKFILE (N'MyBDD_02' , 1);
GO


.

​Retour...