SQL Server Management Studio (SSMS)

SQL Server Management Studio est une application spécialisée pour gérer les bases de données MS SQL. Il s'agit de la même application qu'on retrouvera généralement sur les serveurs IIS.

Attention : vous devez installer SSMS en plus de Visual Studio. Il s'agit de deux programmes distincts et complémentaires.

▼Publicité

Installation de SSMS

La version Express de SSMS est gratuite. Il est possible de la télécharger sur le site de Microsoft : http://www.microsoft.com/fr-ca/download/details.aspx?id=29062 en suivant les étapes suivantes :

  • Choisissez la langue English (vous risquez d'obtenir une erreur d'incompatibilité avec la version française)
  • Cliquez sur Download. 
  • Dans la liste des fichiers disponibles, choisissez le fichier « ENU\x64\SQLManagementStudio_x64_ENU.exe ».

Utilisation de SSMS

Connexion au serveur

Lorsque vous ouvrez SSMS, l'écran d'accueil vous demande d'entrer le nom du serveur sur lequel vous désirez vous connecter.

LocalDB

Pour travailler avec une base de données locale développée sous Visual Studio 2013, vous devez entrer « (localdb)\v11.0 ». Attention : si vous avez créé votre base de données dans une instance différente de SQL Server, vous devrez utiliser cette instance à la place de v11.0 ( ex : « localdb)\Projects » )

Choix du serveur

SQL Express

Si vous travaillez avec SQL Express, le nom du serveur sera composé du nom de votre ordinateur suivi de SQLEXPRESS.

Choix du serveur

Attacher la base de données

Normalement, les bases de données créées dans Visual Studio seront affichées sous Databases. Si vous ne les voyez pas, vous pouvez remédier à la situation en les attachant :

  • Clic droit sur Databases
  • Attach
  • Bouton Add
  • Sélectionnez la base de données
  • Devant le nom de la base de données nouvellement attachée, vous retrouvez une série de caractères ainsi que le chemin du fichier. Il s'agit du nom logique donnée à la base de données. Modifiez ce nom pour ne conserver que la dernière partie (ex : GAGNONANNIE.MDF)

Schémas de bases de données (Database diagrams)

Avoir sous la main une représentation graphique de la base de données est toujours pratique, surtout lorsque les relations entre les tables sont nombreuses et complexes. L'option « Database diagrams » permet de générer de tels schémas. Notez que cette option était disponible directement dans l'explorateur de serveurs sous Visual Studio 2010.

Pour créer un schéma de bases de données, faites un clic droit sur le noeud « Database Diagrams » sous la base de données et choisissez « New Database Diagram ».

Schéma de base de données

Vous pouvez choisir quelles tables inclure dans le diagramme. Vous pouvez également utiliser le diagramme pour créer les contraintes d'intégrité référentielle de façon graphique.

Schéma de base de données

Script SQL pour recréer la base de données (exporter la BD)

Il est important de générer un script SQL de la base de données sur une base régulière. À chaque fois, le fichier devra être enregistré dans le dossier dev de votre solution et la date du jour devra faire partie de son nom (ex : GagnonAnnie-2015-03-12.sql).

La génération d'un script SQL se fera à partir de SSMS.

L'option pour générer le script SQL est disponible par un clic droit sur la base de données, option Tasks / Generate Scripts.

Génération d'un script SQL

Attention : par défaut, les données ne feront pas partie du script SQL. Pour les inclure, vous devez cliquer sur le bouton « Advanced » et préciser que le script doit contenir « Shema and data ».

Ajouter les données dans le script SQL

Copie de sécurité d'une base de donnée MS-SQL (fichier .bak)

Lorsqu'une base de données MS-SQL est utilisée par un site Web qui est en ligne, il n'est pas possible de faire un copier-coller de cette base de données (fichier .mdf) sans auparavant la « détacher » du site Web. Il faut donc arrêter le site, ce qui n'est pas souhaitable.

Les outils Microsoft nous permettent de prendre une copie de sécurité à chaud d'une base de données MS-SQL. Ceci créera un fichier .bak qui pourra être utilisé pour recréer un fichier .mdf. Notez que le fichier .bak ne contient aucune requête SQL. Il n'est pas possible de le lire directement à l'aide d'un éditeur de texte.

Création d'un fichier .bak à partir de SSMS

  • Dans SSMS, faites un clic droit sur le nom de la base de données à copier puis choisissez Tasks / Back Up.
  • Sélectionnez les options correspondant à ce que vous désirez effectuer. Par exemple, pour obtenir une copie exacte de la BD, vous effectuerez une sauvegarde complète sans date d'expiration.
  • Dans la zone Destination, cliquez sur Ajouter puis sélectionnez le nom du fichier de sauvegarde et son emplacement. Prenez soin d'indiquer l'extension .bak dans le nom du fichier.

Création du fichier .bak

Script pour effectuer une copie de sécurité

Il est possible d'obtenir exactement le même fichier .bak à partir d'un script SQL. Notez que la base de données doit être attachée dans SSMS pour que le script fonctionne. Dans le script, NomBD doit correspondre au nom logique utilisé dans SSMS.

Ex :

Fenêtre de commande DOS

@echo off

REM Effectue une copie de sécurité d'une BD

REM Le fichier créé sera sous la forme NomBD-aaaa-mm-jj.bak. 

REM Attention : crée un fichier .bak et non un script SQL

REM Vous devez remplacer NomBD par le nom logique utilisé dans SSMS. 

REM Si le nom logique se termine par .MDF, NomBD doit aussi se terminer par .MDF.

REM Inspiré de http://stackoverflow.com/questions/880487/sql-server-command-line-backup-statement

REM Programmé par Christiane Lagacé : http://christianelagace.com

REM Le 25 avril 2014

set DATABASENAME=NomBD

echo COPIE DE SECURITE DE LA BASE DE DONNEES %DATABASENAME%

REM ***** Création des variables pour la date *****

REM La commande "WMIC OS GET localdatetime" retrouve la date au format ISO. 

REM Le caractère ^ (caret) est un caractère d'échappement. Le caractère | (pipe) permet de rediriger la sortie de WMIC vers la commande find. 

REM Puisqu'on veut conserver toute la chaîne, on recherche n'importe quel caractère : find "."

REM Autrement dit, cette ligne place toute la date dans la variable dateISO.

 

for /f %%a in ('WMIC OS Get localdatetime  ^| find "."') do set "dateISO=%%a"

set "annee=%dateISO:~0,4%"

set "mois=%dateISO:~4,2%"

set "jour=%dateISO:~6,2%"

set BACKUPFILENAME=C:\Users\VotreNom\CheminDeLaCopie\%DATABASENAME%-%annee%-%mois%-%jour%.bak

set SERVERNAME=(localDB)\v11.0

echo.

echo TENTATIVE DE CREATION DU FICHIER

echo %BACKUPFILENAME%

sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT"

pause

Créer une nouvelle base de données MS-SQL à partir d'un fichier .bak

  1. Ouvrez SSMS.
  2. Faites un clic droits sur Databases et choisissez « Restore Database ».
  3. Dans la zone Source, sélectionnez Device et cliquez sur le bouton avec trois points de suspension.

    Fenêtre Restore Database

  4. Dans la fenêtre « Select backup devices », sélectionnez le média de type File puis cliquez sur Add.
  5. Retrouvez le fichier .bak désiré puis cliquez sur OK.

    Fenêtre Select backup devices

  6. Dans la zone Destination, écrivez le nom que vous désirez donner à la base de données qui sera ainsi générée. Attention : il s'agit d'un nom logique et non du nom physique du fichier.
  7. Par défaut, si vous travaillez sur un poste local avec localDB, la nouvelle base de données sera créée dans le dossier C:\Users\VotreNom. De plus, elle portera exactement le même nom physique que la base de données qui a servi à créer le fichier .bak. Si vous désirez un autre emplacement ou un autre nom :
    1. Dans la zone de gauche, cliquez sur l'onglet Files. 
    2. Vous pouvez spécifier le nom et l'emplacement pour le fichier .mdf et pour le fichier .ldf. Prenez bien soin de donner le même emplacement aux deux fichiers. Assurez-vous également que leurs noms se terminent respectivement par .mdf et par _log.ldf.

      Changer le nom du fichier

Copier certaines données d'une table dans une autre base de données

Si votre base de données contient des données d'historique, il faudra tôt ou tard y effectuer une opération de nettoyage.

Parfois, le contexte demande à ce que les données soient tout de même conservées après l'opération de nettoyage. Une solution intéressante consiste à déplacer les données d'historique vers une autre base de données. De cette façon, la base de données utilisée au quotidien est soulagée d'un poids inutile et les données pourront être accessibles à partir de l'autre base de données.

Voici comment y arriver.

  • Ouvrez SSMS
  • Créez la base de données
    • Clic droit sur Bases de données / Nouvelle base de données
    • Donnez un nom à la base de données puis précisez sont chemin d'accès dans la zone « Chemin d'accès ». Assurez-vous de donner le même chemin d'accès pour le fichier .mdf que pour le fichier _log.ldf
  • Faites un clic droit sur la base de données originale puis choissez « Nouvelle requête ».
  • Commencez par vérifier la condition de votre requête en faisant afficher à l'écran les données qui y correspondent.

    Par exemple, si notre but est de déplacer tous les enregistrements dont la modification a eu lieu avant l'année dernière :

    MS SQL

    SELECT *

    FROM versionfiche

    WHERE YEAR(date_modif) < YEAR(GETDATE()) - 1

  • Copiez les enregistrements dans la nouvelle BD.
    • Si la table qui recevra les données dans la nouvelle BD n'existe pas encore, entrez une commande SELECT INTO qui copiera les données désirées vers la nouvelle BD. Votre condition WHERE sera la même que celle testée lors de l'étape précédente.

      Ex :

      MS SQL

      SELECT *

      INTO MaBDHistory.dbo.versionfiche

      FROM versionfiche

      WHERE YEAR(date_modif) < YEAR(GETDATE()) - 1

      ou

    • Si la table existait déjà et que vous souhaitez y ajouter des données,  troquez le SELECT INTO pour INSERT INTO FROM. Notez qu'il faudra cette fois spécifier chacun des champs si la clé primaire était auto-incrémentée.

      Ex :

      MS SQL

      INSERT INTO MaBDHistory.dbo.versionfiche (fiche_id, fiche_titre, fiche_texte, modif_par, date_modif)

      SELECT fiche_id, fiche_titre, fiche_texte, modif_par, date_modif

      FROM versionfiche

      WHERE YEAR(_date_modif) < YEAR(GETDATE()) - 1

  • Après voir vérifié de vos propres yeux que les données ont bien été copiées, retirez maintenant les données de la BD originale, toujours en utilisant la même clause WHERE.

    Ex :

    MS SQL

    DELETE

    FROM versionfiche

    WHERE YEAR(date_modif) < YEAR(GETDATE()) - 1

Merci de partager ! Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInPin on PinterestShare on StumbleUponEmail this to someone
Catégories