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

• Les bases de données n'ont pas de fichiers associés spécifiquement.
• Des fichiers (devices) sont mis à disposition du serveur.
• Les bases se partagent des espaces sur ces devices.
• Une base peut donc utiliser plusieurs parts de devices.
• Un device peut être utilisé par plusieurs bases .

Une base ne pouvant être associée à un fichier spécifique, seule la sauvegarde de l'instance entière est possible, et ceci uniquement lorsque l'instance est arrêtée (shutdown). Tout autre essai vous expose à corrompre votre base, une désynchronisation mémoire/disque étant quasi sûre. La liste des fichiers à sauver peut s'afficher grâce au T-SQL suivant (la condition sur status supprime les dump devices) :

SELECT physname 
FROM master..sysdevices
WHERE status & 16=0

• Les bases de données ont des fichiers associés spécifiquement.
• Des fichiers et donc leur base associée sont plus aisément détachables.
• Les bases ne se partagent pas des espaces sur ces devices.
• Une base peut donc utiliser plusieurs fichiers.
• Un device ne peut donc être utilisé qu'à une seule et unique base.

Il devient donc possible de détacher et d'attacher une base de données en se fixant sur ses fichiers.

Afficher les fichiers d'une base

USE maBase
GO
SELECT filename 
FROM sysfiles
GO

Détacher une base

USE maBase
GO
EXEC sp_detach_db 'MaBase'
GO

Dès ce moment, la base n'est plus attachée à serveur SQL et n'est donc plus utilisable. Ses fichiers peuvent par contre être copiés, au besoin sur un autre serveur ayant une autre instance SQL Server.

Rattacher une base

USE maBase
GO
EXEC sp_attach_db @dbname = N'MaBase', 
    @filename1 = N'D:\MSSQL\Data\MaBase_Data.mdf', 
    @filename2 = N'D:\MSSQL\Log\MaBase_log.ldf' ;
GO

Trois segments principaux sont créés dans une base de données :

• le segment de données : il va contenir tous les objets utilisateurs ;
• le segment de log : il va contenir la table des transactions (syslogs), dès v.2000, il est sur un fichier dissocié spécifique ;
• le segment système : il se partage généralement le même fichier physique que le segment de données et contient les tables ou objets système.

Pour des raisons de performances, on peut se créer d'autres segments particuliers (index, tables spécifiques…) Jusqu'en prév.2000, il était fortement recommandé de dissocier le segment du journal de transactions des autres segments, ceci afin de simplifier la récupération lors de corruptions, mais surtout d'autoriser la sauvegarde du journal, que l'on peut associer à une sauvegarde incrémentielle. Depuis la version 2000, ceci n'est plus nécessaire puisque le journal se trouve sur un fichier dédié (Nomdebase.ldb).

Une base de données se découpe physiquement en blocs que l'on appelle « pages ». Une base peut donc être beaucoup plus grande que ce qu'elle contient. Elle contient alors beaucoup de pages non encore allouées à un objet. La sauvegarde complète va donc être de sauvegarder toutes les pages allouées d'une base dans un fichier externe. Ceci se fait à l'aide de la commande BACKUP.

BACKUP DATABASE MaBase TO DISK=N'D:\BACKUP\MaBase.bak'
BACKUP TRAN MaBase TO DISK=N'D:\BACKUP\MaBase_log.bak'
GO

On peut travailler avec des dumpdevices (cibles de fichiers prédéfinies), directement sur bande, etc., mais ceci n'est pas le sujet de ce didacticiel.

Relevons qu'il est tout à fait possible d'exécuter ce type de sauvegarde à chaud = durant l'utilisation courante de la base. Seules les performances seront sensiblement dégradées.

La récupération se fait avec la commande RESTORE, en ayant pris soin au préalable de supprimer toute connexion sur la base à remonter. Contrairement à la sauvegarde, la récupération requiert - on peut s'en douter - un accès exclusif à la base.

USE master
GO
SELECT 'kill',spid 
FROM sysprocesses 
WHERE dbid=db_id('MaBase')
GO
/* Exécution de l'output de la commande ci-dessus afin de libérer la base */
GO
RESTORE DATABASE MaBase FROM DISK=N'D:\BACKUP\MaBase.bak'
GO

Selon l'importance de la base, il est parfois préférable de faire, entre les divers BACKUP DATABASE, des sauvegardes incrémentielles des journaux de transactions, ceci afin de pouvoir retrouver au besoin non pas l'état de la base depuis la dernière sauvegarde complète (souvent la veille au soir), mais celui depuis la dernière sauvegarde du journal de transaction (par exemple chaque heure).

BACKUP TRAN MaBase TO DISK='D:\BACKUP\MaBase_MonHeureMaDate.trn' 
GO

Soit donc le programme de sauvegardes suivant :

• sauvegarde complète tous les soirs à 20h ;
• sauvegarde des journaux de transactions toutes les heures ouvrables (7-19h).

Imaginons maintenant qu'un crash apparaisse à 11h20 du matin. Que devez-vous faire ?

• tenter d'effectuer une dernière sauvegarde du journal de transactions ;
• remonter le serveur SQL dans un état stable (ou passer sous un autre SQL Serveur) ;
• restaurer la base depuis la sauvegarde compète la plus récente ;
• restaurer tous les journaux de transactions à disposition.

Soit, en T-SQL

USE master
GO
/* Tentative de sauvegarde du dernier journal */
BACKUP TRAN MaBase To DISK=N'D:\Backup\MaBase_crash11h30_20060518.Bak'
SELECT @@error
GO
 
/* Si même arborescence disque */
RESTORE DATABASE MaBase FROM DISK=N'D:\Backup\MaBase_20h_20060517.Bak' WITH NORECOVERY
GO    
 
/* Si arborescence disque différente, repositionnement sur la nouvelle arborescence */
RESTORE DATABASE MaBase FROM DISK=N'D:\Backup\MaBase_20h_20060517.Bak' 
WITH MOVE 'MaBase_Data' TO DISK='C:\MaNouvelleLocalisation\Data\MaBase.mdb' ,
     MOVE 'MaBase_Log' TO DISK='C:\MaNouvelleLocalisation\Log\MaBase.ldb' ,
     NORECOVERY
GO    
 
/* ...  et récupération des journaux de transactions successifs, juste avant le crash */
RESTORE TRAN MaBase FROM DISK=N'D:\Backup\MaBase_7h_20060518.Bak' WITH NO_RECOVERY
RESTORE TRAN MaBase FROM DISK=N'D:\Backup\MaBase_8h_20060518.Bak' WITH NO_RECOVERY
RESTORE TRAN MaBase FROM DISK=N'D:\Backup\MaBase_9h_20060518.Bak' WITH NO_RECOVERY
RESTORE TRAN MaBase FROM DISK=N'D:\Backup\MaBase_10h_20060518.Bak' WITH NO_RECOVERY
RESTORE TRAN MaBase FROM DISK=N'D:\Backup\MaBase_11h_20060518.Bak' WITH NO_RECOVERY
RESTORE TRAN MaBase FROM DISK=N'D:\Backup\MaBase_crash11h30_20060518.Bak' WITH RECOVERY STOPAT = 'May 18, 2006 11:19 AM'
GO

La récupération de la base master pose un problème particulier compte tenu qu'il faut obtenir un accès exclusif à une base pour la restaurer, et que la base master contient des processus internes de SQL Server.

Il va donc falloir arrêter le serveur et le remonter dans un mode bien particulier, avec une base master en mode accès exclusif.

net stop MSSQL$SQL2005
sqlservr.exe - m -s SQL2005

Connectez-vous ensuite via votre outil préféré (isql, sqlcmd ou un équivalent graphique) et exécutez l'opération de restauration

RESTORE DATABASE master FROM DISK='D:\BACKUP\master.bak'
GO

N'ayez pas d'inquiétude si à la fin de cette commande, vous perdez votre connexion : aussitôt la récupération effectuée, le SQL Server se suicide (shutdown) afin de pouvoir être redémarré correctement avec le nouvel état de sa base master.

net start MSSQL$SQL2005

Précondition : quelle que soit la méthode, il n'est pas possible de remonter une version récente sur un serveur moins récent (incompatibilité descendante).

Piège à éviter : une désynchronisation des logins. Les logins dépendant de la base master et les utilisateurs étant un mappage de login propre à la base, le fait de remonter une base et ses utilisateurs sur un serveur qui n'aurait pas les bons logins risque de causer problème.

Il est donc nécessaire, dans ces cas, de sauvegarder les DDL de création des users AVANT d'écraser l'ancienne base, via des ordres T-SQL de type

USE MaBase
GO
 
/* Version pré-2005 */
 
select 'EXEC SP_ADDUSER ' + name + ', '+ suser_sname(sid) 
from sysusers 
WHERE  uid between 5 and 16000
AND STATUS=0 -- Suppression des application roles
 
/* Version 2005 */
 
select 'CREATE USER ' + name  ' FOR LOGIN '+suser_sname(sid) 
from sys.database_principals 
WHERE  principal_id between 5 and 16000
AND TYPE='S'

Il faudra ensuite détecter, après le chargement, les utilisateurs désynchronisés et les recréer au besoin à l'aide de la procédure sp_change_users_login.

On détache préalablement la base à cloner afin que SQL Server relâche son accès en lecture sur les fichiers.

EXEC SP_DETACH_DB 'MaBase'
GO

Via Windows, duplication des fichiers de la base

copy D:\MSSQL\Data\MaBase_Data.mdf D:\MSSQL\Data\MonNouveauNomDeBase_Data.mdf
copy D:\MSSQL\log\MaBase_Log.ldf D:\MSSQL\log\MonNouveauNomDeBase_Log.ldf

Puis rattachement de la base que nous venons de détacher et de son clone.

USE MASTER
GO
EXEC sp_attach_db @dbname = N'MaBase' , 
    @filename1 = N'D:\MSSQL\Data\MaBase_Data.mdf', 
    @filename2 = N'D:\MSSQL\Log\MaBase_log.ldf' 
GO
EXEC sp_attach_db @dbname = N'MonNouveauNomDeBase', 
    @filename1 = N'D:\MSSQL\Data\MonNouveauNomDeBase_Data.mdf', 
    @filename2 = N'D:\MSSQL\Log\MonNouveauNomDeBase_log.ldf' 
GO
ALTER DATABASE MonNouveauNomDeBase MODIFY FILE (NAME=N'MaBase_Data', NEWNAME=N'MonNouveauNomDeBase_Data')
ALTER DATABASE MonNouveauNomDeBase MODIFY FILE (NAME=N'MaBase_Log', NEWNAME=N'MonNouveauNomDeBase_Log')
GO

Les bases système de MS-SQL Server sont, via les outils graphiques, quasiment inamovibles. Pour les déplacer, il va falloir user de subterfuges.

V-C-1. Déplacer tempdb ou model▲

USE master
GO
sp_configure 'allow updates', 1
GO
UPDATE master..sysdevices SET phyname = 'D:\MaNouvelleLocalisation\tempdb.mdf' where name = 'tempdev'
UPDATE master..sysdevices SET phyname = 'D:\MaNouvelleLocalisation\templog.ldf' where name = 'templog'
GO
SHUTDOWN with nowait
GO

move "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf" D:\MaNouvelleLocalisation\Data\tempdb.mdf
move "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf" D:\MaNouvelleLocalisation\Log\templog.ldf
net start MSSQL$SQL2005

SELECT name, filename 
FROM tempdb..sysfiles
GO
ALTER DATABASE tempdb MODIFY FILE (NAME=N'tempdev', FILENAME = 'D:\NouvelleLocalisation\Data\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY FILE (NAME=N'templog', FILENAME = 'D:\NouvelleLocalisation\Log\templog.ldf' )
GO
SHUTDOWN WITH nowait

move "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf" "D:\NouvelleLocalisation\data\tempdb.mdf"
move "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf" "D:\NouvelleLocalisation\log\templog.ldf"
net start MSSQL$SQL2005

V-C-2. Déplacer msdb▲

USE MASTER
GO
BACKUP DATABASE msdb to DISK = N'd:\backup\msdb.bak' 
GO
RESTORE DATABASE msdb FROM  DISK = N'd:\backup\msdb.bak' 
     WITH MOVE N'MSDBData' TO N'C:\MaNouvelleLocalisation\DATA\MSDBData.mdf',  
          MOVE N'MSDBLog' TO N'C:\MaNouvelleLocalisation\log\MSDBLog.ldf'
GO

V-C-3. Déplacer master▲

net stop MSSQL$SQL2005
move "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf" "d:\MaNouvelleLocalisation\Data\master.mdf"
move "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf" "d:\MaNouvelleLocalisation\Log\mastlog.ldf"
net start MSSQL$SQL2005