SQL Server - La FAQConsultez toutes les FAQ

Nombre d'auteurs : 13, nombre de questions : 119, dernière mise à jour : 31 mai 2011  Ajouter une question

 

Question / réponses à tout ce que vous avez toujours voulu savoir sur Microsoft SQL Server sans jamais oser le demander


SommaireAdministration de la base de donnéesSauvegardes et restauration (11)
précédent sommaire suivant
 

Via la commande backup

Code tsql :
backup database MaBase to DISK=N'c:\temp\MonFichier.bak'

Mis à jour le 28 avril 2006 fadace

Déplacement, sauvegardes et restauration de bases sous MS-SQL Server

Via la commande restore, en ayant au préalable supprimé toutes les connexions existantes sur la base qui sera écrasée

Code tsql :
restore database MaBase from DISK=N'c:\temp\MonFichier.bak'

Mis à jour le 28 avril 2006 fadace

Déplacement, sauvegardes et restauration de bases sous MS-SQL Server

via commandes backup/restore

Code tsql :
1
2
backup database MaBase to DISK=N'c:\temp\Mabase.bak' 
restore database MaCopie from DISK=N'c:\temp\Mabase.bak'

Mis à jour le 28 avril 2006 fadace

Déplacement, sauvegardes et restauration de bases sous MS-SQL Server

Code tsql :
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Recovery')

Mis à jour le 3 mai 2006 SQLpro

Code tsql :
ALTER DATABASE MABASE SET RECOVERY FULL

Mis à jour le 3 mai 2006 SQLpro

Si vous avez exécuté cette restauration via un job de Microsoft SQL Server, vous pouvez rechercher l'information via la base système msdb

Code tsql :
1
2
3
4
5
6
7
8
9
10
11
12
USE VotreBase 
GO 
SELECT destination_database_name, 
    restore_date, 
    b.database_name, 
    physical_name, 
    backup_start_date 
FROM msdb.dbo.RestoreHistory h  
    INNER JOIN msdb.dbo.BackupSet b ON h.backup_set_id = b.backup_set_id 
    INNER JOIN msdb.dbo.BackupFile f ON f.backup_set_id = b.backup_set_id 
WHERE b.database_name = db_name() 
GO
Si par contre vous avez effectué a restauration via la ligne de commande ou l'assistant interactif, ne vous restera plus qu'à aller rechercher dans les journaux

Mis à jour le 5 janvier 2007 fadace rudib WOLO Laurent

Ces fichiers sont cex d'une base de données qui a été détachée d'un serveur par l'ordre sp_detache_db.

Pour les rattacher à votre serveur SQL, utilisez la procédure stockée sp_attach_db

Code tsql :
1
2
3
EXEC sp_attach_db @dbname ='AdventureWorks',  
@filename1 = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',  
@filename2 = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf'

Mis à jour le 23 avril 2007 fadace

Déplacement, sauvegardes et restauration de bases sous MS-SQL Server
Attacher une base de données dont le journal de transaction est manquant

Voici une procédure destinée à opérer une sauvegarde globales de toutes les bases d'un serveur. Différentes options sont possible comme de sauvegarder aussi les bases systèmes ou utiliser une unité de sauvegarde (device).

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
CREATE PROCEDURE dbo.P_SAVE  
   @FILE_PATH    NVARCHAR(256),             -- chemin pour stockage des fichiers de sauvegarde  
   @FILE_EXT     NVARCHAR(3) = 'BAK',       -- extension pour les fichiers de sauvegarde  
   @FILE_NAME    NVARCHAR(128) = 'DB_SAVE', -- nom générique des fichiers de sauvegarde  
   @FILE_DATE    BIT = 1,                   -- incorporer la date dans le nom des fichiers  
   @FILE_HOUR    BIT = 1,                   -- incorporer l'heure dans le nom des fichiers  
   @ONLY_USER_DB BIT = 0,                   -- ne sauvegarder que les bases utilisateur (pas les bases systèmes)  
   @TO_DEVICE    BIT = 1,                   -- créer une unité de sauvegarde (device) pour y inclure toutes les sauvegardes  
   @CLEAN_DEVICE BIT = 1                    -- purger l'unité de sauvegarde (device) des sauvegrades qu'elle contient  
AS  
  
-- ATTENTION : les noms des bases de données doivent repecter la norme ISO (pas de caractères illicites)  
-- dans le cas contraire prévoir les crochet dans le SQL dynamique  
  
IF @FILE_PATH IS NULL OR @FILE_EXT IS NULL OR @FILE_NAME IS NULL  
BEGIN  
   SET @FILE_PATH = COALESCE(@FILE_PATH, '?');  
   SET @FILE_EXT  = COALESCE(@FILE_EXT, '?');  
   SET @FILE_NAME = COALESCE(@FILE_NAME, '?');  
   RAISERROR ('Un des paramètres de la procédure est manquant. Chemin : %s - Nom fichier : %s - Extension : %s', 16, 1)  
   RETURN  
END;  
  
-- la variable @PATH se termine t-elle par un "\" ?, si non, l'ajouter  
IF SUBSTRING(REVERSE(@FILE_PATH), 1, 1) <> '\'  
   SET @FILE_PATH = @FILE_PATH + '\';  
-- l'extension ne doit pas avoir de point  
SET @FILE_EXT = REPLACE(@FILE_EXT, '.', '');  
  
  
-- le répertoire doit exister. S'il n'existe pas on le créé.  
-- utilisation de xp_cmdshell  
DECLARE @SHOW_OPTIONS BIT, @CMDSHELL BIT;  
SELECT @SHOW_OPTIONS = CAST(value_in_use AS BIT)  
FROM   sys.configurations  
WHERE  name = 'show advanced options'  
SELECT @CMDSHELL = CAST(value_in_use AS BIT)  
FROM   sys.configurations  
WHERE  name = 'xp_cmdshell'  
IF @CMDSHELL = 0  
BEGIN  
   IF @SHOW_OPTIONS = 0  
      EXEC sp_configure 'show advanced options', 1;  
   EXEC sp_configure 'xp_cmdshell', 1;  
   RECONFIGURE;  
   IF @SHOW_OPTIONS = 0  
      EXEC sp_configure 'show advanced options', 0;  
   RECONFIGURE;  
END  
-- création du répertoire    
EXEC ('xp_cmdshell ''MKDIR "' + @FILE_PATH+'"''');  
IF @CMDSHELL = 0  
BEGIN  
   EXEC sp_configure 'xp_cmdshell', 0;  
   IF @SHOW_OPTIONS = 0  
      EXEC sp_configure 'show advanced options', 0;  
   RECONFIGURE;  
END;  
  
-- variables de travail  
DECLARE @DATE NVARCHAR(9), @HOUR NVARCHAR(7),   
        @DEVICE_NAME NVARCHAR(128), @DEVICE_FILE NVARCHAR(512),  
        @FILE_SAVE   NVARCHAR(512), @SQL VARCHAR(max);  
  
-- conversion en chaine date et heure au format ISO court  
IF @FILE_DATE = 1  
   SET @DATE = REPLACE(CONVERT(NCHAR(10), CURRENT_TIMESTAMP, 121), '-', '');  
IF @FILE_HOUR = 1  
   SET @HOUR = SUBSTRING(REPLACE(CONVERT(NCHAR(25), CURRENT_TIMESTAMP, 121), ':', ''), 12, 6);  
  
-- la sauvegarde se fait sur un device. Il faut le créer.  
IF @TO_DEVICE = 1   
BEGIN  
   SET @DEVICE_NAME = @FILE_NAME + COALESCE('_' + @DATE,'') + COALESCE('_' + @HOUR, '');  
   SET @DEVICE_FILE = @FILE_PATH + @DEVICE_NAME + '.' +@FILE_EXT;  
END;  
  
IF @TO_DEVICE = 1 AND @CLEAN_DEVICE = 1  
-- création d'un "device" (unité de sauvegarde) en fait un super fichier qui va contenir toutes les sauvegardes  
   IF EXISTS (SELECT *   
              FROM   sys.backup_devices  
              WHERE  name = @DEVICE_NAME)  
      EXEC sp_dropdevice @DEVICE_NAME;  
IF NOT EXISTS(SELECT *   
              FROM   sys.backup_devices  
              WHERE  name = @DEVICE_NAME)  
   EXEC sp_addumpdevice 'DISK', @DEVICE_NAME, @DEVICE_FILE;  
  
-- génération de la commande SQL pour la liste des bases à sauvegarder  
SET @SQL = '';  
  
SELECT @SQL = @SQL + 'BACKUP DATABASE ' + name +' TO ' +   
-- vers device ou fichiers  
       CASE   
          WHEN @TO_DEVICE = 1   
             THEN @DEVICE_NAME  
          ELSE    'DISK = ''' + @FILE_PATH + @FILE_NAME + '_' + name +   
                    COALESCE('_' + @DATE,'') + COALESCE('_' + @HOUR, '')  +  
                    COALESCE('.' + @FILE_EXT, '') + ''''  
       END  +   
-- si device alors purger ou non  
       CASE   
          WHEN @TO_DEVICE = 1 AND N = 1 AND @CLEAN_DEVICE = 1 THEN ' WITH FORMAT, INIT'  
          ELSE ''  
       END + ';'  + CHAR(10) + CHAR(13)  
FROM   (SELECT name, ROW_NUMBER() OVER(ORDER BY database_id) AS N  
        FROM   sys.DATABASES   
        WHERE  state = 0  
          AND  source_database_id IS NULL  
          AND  (owner_sid <> 0x01 OR @ONLY_USER_DB <> 1)  
          AND  name <> 'tempdb' ) AS T;  
  
EXEC (@SQL);  
  
GO
Code sql :
1
2
P_SAVE 'C:\! test sauve', 'bkp', 'SAUVEGARDE_SQL_SERVER', 1, 1, 0, 1, 0   
-- @FILE_PATH @FILE_EXT @FILE_NAME @FILE_DATE @FILE_HOUR @ONLY_USER_DB @TO_DEVICE @CLEAN_DEVICE

Mis à jour le 24 mai 2009 SQLpro

Une requête simple dans la base de données système MSDB nous donne la réponse ... Valable sous SQL Server 2000 et ultérieur

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT plan_name,  
    database_name,  
    activity,  
    start_time,  
    end_time,  
    CASE LEN(CAST(duration / 3600 AS VARCHAR(4)))  
      WHEN 1 THEN '0' + CAST(duration / 3600 AS VARCHAR(4))  
      ELSE CAST(duration / 3600 AS VARCHAR(4))  
    END + ':' +  
    CASE LEN(CAST(duration / 60 % 60 AS VARCHAR(2)))  
      WHEN 1 THEN '0' + CAST(duration / 60 % 60 AS VARCHAR(2))  
      ELSE CAST(duration / 60 % 60 AS VARCHAR(2))  
    END  
    + ':' +   
    CASE LEN(CAST(duration % 60 AS VARCHAR(2)))  
      WHEN 1 THEN '0' + CAST(duration % 60 AS VARCHAR(2))  
      ELSE CAST(duration % 60 AS VARCHAR(2))  
    END Durée  
FROM msdb.dbo.sysdbmaintplan_history  
WHERE succeeded = 0 -- 0 : échec | 1 : réussite  
-- AND start_time BETWEEN '20090907' AND '20090908'  
ORDER BY database_name, start_time
Il est dès lors facile de placer cette requête dans un DataSet sous Reporting Services ou dans une application WinForms pour pouvoir s'afficher l'état des plans de maintenance qui doivent avoir lieu sur une instance de SQL Server.

Mis à jour le 11 juillet 2009 elsuket

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça


Réponse à la question

Liens sous la question
précédent sommaire suivant
 

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2014 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

 
 
 
 
Partenaires

PlanetHoster
Ikoula