SSMS - Automatisation de la sauvegarde - Historique des versions

Gdaubeze le 27 septembre 2022 à 10:30

2022-09-27T10:30:13Z

◂ Version précédente		Version du 27 septembre 2022 à 12:30
Ligne 11 :		Ligne 11 :
	Ressource documentaire PDF : [[Fichier:Restauration SQL fichier BAK.pdf]]		Ressource documentaire PDF : [[Fichier:Restauration SQL fichier BAK.pdf]]

	== Remerciement ==		=== Remerciement ===

	Nous remercions notre partenaire MCO INFORMATIQUE pour la réalisation de cette documentation, et plus spécialement Bastien OUARY pour son perfectionnisme.		Nous remercions notre partenaire MCO INFORMATIQUE pour la réalisation de cette documentation, et plus spécialement Bastien OUARY pour son perfectionnisme.

Gdaubeze le 27 septembre 2022 à 10:30

2022-09-27T10:30:00Z

◂ Version précédente		Version du 27 septembre 2022 à 12:30
Ligne 10 :		Ligne 10 :

	Ressource documentaire PDF : [[Fichier:Restauration SQL fichier BAK.pdf]]		Ressource documentaire PDF : [[Fichier:Restauration SQL fichier BAK.pdf]]

			== Remerciement ==

			Nous remercions notre partenaire MCO INFORMATIQUE pour la réalisation de cette documentation, et plus spécialement Bastien OUARY pour son perfectionnisme.

Gdaubeze : Contenu remplacé par « Category:EBPCategory:EBP Gestion commercialeCategory:EBP Comptabilité = SSMS - Automatisation de la sauvegarde = == Plan de maintenance et de sauvegarde avec SQL SERVER 2014 == Ressource documentaire PDF : Fichier:Plan de maintenance et Sauvegarde.pdf == Restauration d’une sauvegarde BAK == Ressource documentaire PDF : Fichier:Restauration SQL fichier BAK.pdf »

2022-09-27T10:28:42Z

Contenu remplacé par « Category:EBP Category:EBP Gestion commerciale Category:EBP Comptabilité = SSMS - Automatisation de la sauvegarde = == Plan de maintenance et de sauvegarde avec SQL SERVER 2014 == Ressource documentaire PDF : Fichier:Plan de maintenance et Sauvegarde.pdf == Restauration d’une sauvegarde BAK == Ressource documentaire PDF : Fichier:Restauration SQL fichier BAK.pdf »

Voir les modifications

Gdaubeze le 27 septembre 2022 à 10:13

2022-09-27T10:13:55Z

◂ Version précédente		Version du 27 septembre 2022 à 12:13
Ligne 22 :		Ligne 22 :
	PRINT 'Agent is not connected!';		PRINT 'Agent is not connected!';

	[[Fichier:Image 2022-09-27 113621218.png~~\|sans_cadre~~]]		[[Fichier:Image 2022-09-27 113621218.png]]

	Aucun problème ici, l’agent est en cours d’exécution.		Aucun problème ici, l’agent est en cours d’exécution.
Ligne 32 :		Ligne 32 :
	Cliquer droit dessus :		Cliquer droit dessus :

	[[Fichier:Image 2022-09-27 113735235.png~~\|sans_cadre~~]]		[[Fichier:Image 2022-09-27 113735235.png]]

	Sélectionner « Assistant Plan de maintenance ».		Sélectionner « Assistant Plan de maintenance ».

	[[Fichier:Image 2022-09-27 113943684.png~~\|sans_cadre~~]]		[[Fichier:Image 2022-09-27 113943684.png]]

	Suivant…		Suivant…
Ligne 42 :		Ligne 42 :
	Ensuite personnaliser les premières valeurs du plan en définissant un nom, une description puis une planification :		Ensuite personnaliser les premières valeurs du plan en définissant un nom, une description puis une planification :

	[[Fichier:Image 2022-09-27 114019276.png~~\|sans_cadre~~]]		[[Fichier:Image 2022-09-27 114019276.png]]

	Ici le Plan est nommé en « Sauvegarde» et planifié pour s’exécuter tous les jours à 23h.		Ici le Plan est nommé en « Sauvegarde» et planifié pour s’exécuter tous les jours à 23h.
	C’est le compte de service SQL Server défini par défaut qui sera utilisé pour l’exécution de la tâche.		C’est le compte de service SQL Server défini par défaut qui sera utilisé pour l’exécution de la tâche.

	[[Fichier:Image 2022-09-27 120212915.png]]		[[Fichier:Image 2022-09-27 120212915.png]]

	Suivant…		Suivant…
	Sélectionner le mode de sauvegarde souhaité. Pour des petites bases de données la sauvegarde complète suffit.		Sélectionner le mode de sauvegarde souhaité. Pour des petites bases de données la sauvegarde complète suffit.
			[[Fichier:Image 2022-09-27 120918450.png]]
	Suivant…		Suivant…
			[[Fichier:Image 2022-09-27 121106671.png]]

	Suivant…		Suivant…
	Sélectionner la base à sauvegarder :		Sélectionner la base à sauvegarder :

			[[Fichier:Image 2022-09-27 121130671.png]]

	Sélectionner le type de la destination (sur disque pour une sauvegarde sur le serveur lui-même) :		Sélectionner le type de la destination (sur disque pour une sauvegarde sur le serveur lui-même) :

			[[Fichier:Image 2022-09-27 121226457.png]]
	et définir le dossier de destination dans l’onglet du même nom :		et définir le dossier de destination dans l’onglet du même nom :

			[[Fichier:Image 2022-09-27 121254330.png]]
	Cocher « Vérifier l’intégrité de la sauvegarde » dans l’onglet « Options » :		Cocher « Vérifier l’intégrité de la sauvegarde » dans l’onglet « Options » :

			[[Fichier:Image 2022-09-27 121319076.png]]
	Suivant…		Suivant…
	Définir un emplacement pour le rapport de sauvegarde.		Définir un emplacement pour le rapport de sauvegarde.

			[[Fichier:Image 2022-09-27 121339615.png]]
	Il est possible également d’envoyer le rapport de sauvegarde par e-mail, dans ce cas il faudra paramétrer un serveur smtp dans SQL (https://docs.microsoft.com/fr-fr/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-ver15).		Il est possible également d’envoyer le rapport de sauvegarde par e-mail, dans ce cas il faudra paramétrer un serveur smtp dans SQL (https://docs.microsoft.com/fr-fr/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-ver15).
	Suivant…		Suivant…

Gdaubeze le 27 septembre 2022 à 10:02

2022-09-27T10:02:41Z

◂ Version précédente		Version du 27 septembre 2022 à 12:02
Ligne 11 :		Ligne 11 :

	SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')		SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
			[[Fichier:Image 2022-09-27 113327998.png]]

			Si la version affichée indique <span style="color:red">Express</span>, inutile de continuer, les outils ne seront pas disponibles.
	Si la version affichée indique Express, inutile de continuer, les outils ne seront pas disponibles.
	Dans notre cas aucun problème puisque nous sommes en « Standard Edition ».		Dans notre cas aucun problème puisque nous sommes en « Standard Edition ».
	Dans la documentation officielle il est dit que « Les plans de maintenance sont enregistrés en tant que packages Integration Services qui sont exécutés par les travaux de l'Agent SQL Server ». Il faut donc s’assurer que l’agent SQL tourne bien sur la machine qui exécutera le plan.		Dans la documentation officielle il est dit que « Les plans de maintenance sont enregistrés en tant que packages Integration Services qui sont exécutés par les travaux de l'Agent SQL Server ». Il faut donc s’assurer que l’agent SQL tourne bien sur la machine qui exécutera le plan.
Ligne 22 :		Ligne 22 :
	PRINT 'Agent is not connected!';		PRINT 'Agent is not connected!';

			[[Fichier:Image 2022-09-27 113621218.png\|sans_cadre]]

	Aucun problème ici, l’agent est en cours d’exécution.		Aucun problème ici, l’agent est en cours d’exécution.
	2 – Création d’un plan avec l’assistant :		== 2 – Création d’un plan avec l’assistant : ==
	Sous SQL Management Studio dans l’arborescence de gauche (explorateur d’objets) rechercher le menu suivant :		Sous SQL Management Studio dans l’arborescence de gauche (explorateur d’objets) rechercher le menu suivant :

			[[Fichier:Image 2022-09-27 113702177.png\|sans_cadre]]

	Cliquer droit dessus :		Cliquer droit dessus :

			[[Fichier:Image 2022-09-27 113735235.png\|sans_cadre]]

	Sélectionner « Assistant Plan de maintenance ».		Sélectionner « Assistant Plan de maintenance ».

			[[Fichier:Image 2022-09-27 113943684.png\|sans_cadre]]

	Suivant…		Suivant…
Ligne 37 :		Ligne 42 :
	Ensuite personnaliser les premières valeurs du plan en définissant un nom, une description puis une planification :		Ensuite personnaliser les premières valeurs du plan en définissant un nom, une description puis une planification :

			[[Fichier:Image 2022-09-27 114019276.png\|sans_cadre]]

	Ici le Plan est nommé en « Sauvegarde» et planifié pour s’exécuter tous les jours à 23h.		Ici le Plan est nommé en « Sauvegarde» et planifié pour s’exécuter tous les jours à 23h.
	C’est le compte de service SQL Server défini par défaut qui sera utilisé pour l’exécution de la tâche.		C’est le compte de service SQL Server défini par défaut qui sera utilisé pour l’exécution de la tâche.
			[[Fichier:Image 2022-09-27 120212915.png]]

	Suivant…		Suivant…

Gdaubeze : Page créée avec « Category:EBPCategory:EBP Gestion commercialeCategory:EBP Comptabilité = PLAN DE MAINTENANCE SQL SERVER 2014 = == 1 – Prérequis : == L’accès aux outils Plan de Maintenance n’est disponible qu’à partir de la version Standard de SQL. Il faut donc commencer par vérifier la version installée afin de s’assurer que les outils seront disponibles. Lancer SQL Management Studio et lancer une fenêtre nouvelle requête. Exécuter la requête s... »

2022-09-27T09:33:11Z

Page créée avec « Category:EBP Category:EBP Gestion commerciale Category:EBP Comptabilité = PLAN DE MAINTENANCE SQL SERVER 2014 = == 1 – Prérequis : == L’accès aux outils Plan de Maintenance n’est disponible qu’à partir de la version Standard de SQL. Il faut donc commencer par vérifier la version installée afin de s’assurer que les outils seront disponibles. Lancer SQL Management Studio et lancer une fenêtre nouvelle requête. Exécuter la requête s... »

Nouvelle page

[[Category:EBP]][[Category:EBP Gestion commerciale]][[Category:EBP Comptabilité]]

= PLAN DE MAINTENANCE SQL SERVER 2014 =

== 1 – Prérequis : ==
L’accès aux outils Plan de Maintenance n’est disponible qu’à partir de la version Standard de SQL.
Il faut donc commencer par vérifier la version installée afin de s’assurer que les outils seront disponibles.
Lancer SQL Management Studio et lancer une fenêtre nouvelle requête.
Exécuter la requête suivante :

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Si la version affichée indique Express, inutile de continuer, les outils ne seront pas disponibles.
Dans notre cas aucun problème puisque nous sommes en « Standard Edition ».
Dans la documentation officielle il est dit que « Les plans de maintenance sont enregistrés en tant que packages Integration Services qui sont exécutés par les travaux de l'Agent SQL Server ». Il faut donc s’assurer que l’agent SQL tourne bien sur la machine qui exécutera le plan.
Exécuter la requête suivante :
IF EXISTS (SELECT 1 FROM sysprocesses WHERE LEFT(program_name, 8) = 'SQLAgent')
PRINT 'Agent is running!'
ELSE
PRINT 'Agent is not connected!';

Aucun problème ici, l’agent est en cours d’exécution.
2 – Création d’un plan avec l’assistant :
Sous SQL Management Studio dans l’arborescence de gauche (explorateur d’objets) rechercher le menu suivant :

Cliquer droit dessus :

Sélectionner « Assistant Plan de maintenance ».

Suivant…

Ensuite personnaliser les premières valeurs du plan en définissant un nom, une description puis une planification :

Ici le Plan est nommé en « Sauvegarde» et planifié pour s’exécuter tous les jours à 23h.
C’est le compte de service SQL Server défini par défaut qui sera utilisé pour l’exécution de la tâche.

Suivant…
Sélectionner le mode de sauvegarde souhaité. Pour des petites bases de données la sauvegarde complète suffit.

Suivant…

Suivant…
Sélectionner la base à sauvegarder :

Sélectionner le type de la destination (sur disque pour une sauvegarde sur le serveur lui-même) :

et définir le dossier de destination dans l’onglet du même nom :

Cocher « Vérifier l’intégrité de la sauvegarde » dans l’onglet « Options » :

Suivant…
Définir un emplacement pour le rapport de sauvegarde.

Il est possible également d’envoyer le rapport de sauvegarde par e-mail, dans ce cas il faudra paramétrer un serveur smtp dans SQL (https://docs.microsoft.com/fr-fr/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-ver15).
Suivant…
Les options sélectionnées pour le plan de maintenance sont récapitulées dans une nouvelle fenêtre :

Terminer…
L’assistant exécute les tâches demandées et se termine

Dans l’explorateur d’objets le plan de maintenance apparaît sous le nom qui lui a été donné :

Double cliquer dessus pour le faire apparaître dans la partie de droite :

Le nom du sous-plan peut être personnalisé :
Les options de rapport peuvent être modifiés en cliquant sur l’icône suivant :

3 – Ajouter d’autres tâches au plan :
Il est possible d’automatiser d’autres opérations avec le plan de maintenance. Il faudra cependant être prudent dans le choix de ces opérations pour éviter les effets indésirables sur la base.
Par exemple une tâche offre la possibilité de réduire la base de données au-delà d’une certaine taille. Un processus qui récupère l'espace disque en supprimant les données vides et les logs. Pour ce faire, le moteur de la base doit déplacer les données vers la fin du fichier pour vider l'espace à l'avant du fichier. Bien qu'il en résulte plus d'espace disque, cela provoque également une fragmentation importante. De nombreux DBA expérimentés déconseillent l'utilisation de cette option mais si elle est inévitable les index devront être reconstruits par la suite.
On peut également utiliser une tâche pour automatiser la purge des anciennes sauvegardes. C’est intéressant si l’on veut éviter de saturer l’espace de stockage où est enregistré la sauvegarde.
Pour ajouter une tâche de purge il suffit de faire glisser la tâche correspondante depuis la boite à outil en haut à gauche vers la fenêtre de droite où est afficher le plan de maintenance :

Un nouveau pavé s’ajoute :

Pour le relier chronologiquement au pavé de sauvegarde afin qu’il s’exécute en suivant, cliquer droit pour faire apparaître le menu contextuel et sélectionner « Ajouter une contrainte de précédence » :

Sélectionner ensuite les deux tâches qui doivent être liées, dans l’ordre bien sûr, puis valider :

Sur le graphique les deux tâches sont à présent reliées par une flèche :

Définir les conditions de suppression des anciennes sauvegardes en éditant la tâche de nettoyage (double cliquer sur la pavé) :

Valider…
Le pavé se met à jour :

De la même manière on peut ajouter une tâche de vérification de l’intégrité de la base :

Editer la tâche :

Sélectionner la base sauvegardée précédemment et valider.
Le pavé se met à jour :

Modifier l’ordre d’exécution des tâches de manière à positionner la tâche de vérification d’intégrité entre la sauvegarde et la purge (Ajouter une contrainte de précédence) :

Supprimer l’ancien lien avec la tâche de purge en supprimant la flèche correspondante sur le graphique.
Recréer ensuite le bon lien pour obtenir :

4 – Vérifier le fonctionnement du plan :
Pour s’assurer du bon fonctionnement du plan on peut commencer par vérifier que les fichiers de sauvegardes sont bien générés dans le dossier de destination :

On peut également consulter les rapports enregistrés dans ce même dossier :

Enfin on peut consulter les journaux SQL depuis SQL Management Studio :

4 – Effectuer une sauvegarde manuelle :
La génération d’un fichier BAK peut être effectué manuellement au besoin, en passant par l’utilitaire SQLcmd.exe.
Sur le serveur où se trouve sqlcmd.exe, ouvrir une ligne de commande et se positionner sur le répertoire de sqlcmd.exe :
cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
Taper ensuite la commande suivante qui va déclencher une requête de sauvegarde sur le serveur souhaité :
sqlcmd.exe -S nom_serveur,port\instance_SQL -U utilisateur -P mot_de_passe –Q "BACKUP DATABASE [nom_de_la_base] TO DISK='chemin_vers_le_répertoire_de_sauvegarde'"
Pour sauvegarder sur le serveur sur lequel on se trouve on peut utiliser le point en nom de serveur. Ce qui donne :
sqlcmd.exe -S .,1433\MSSQLSERVER -U sa -P @ebp78EBP –Q "BACKUP DATABASE [S I_0895452f-b7c1-4c00-a316-c6a6d0ea4bf4] TO DISK='E:\Manual SQL Backups\SI_backup.bak'"
(Il n’est pas toujours nécessaire de préciser le n° du port sur lequel écoute l’instance. Dans ce cas précis c’était obligatoire sinon la connexion échouait.)