Un mode de récupération est une option de configuration de bases de données qui contrôle la façon dont les transactions ( insert,update,delete ) sont journalisés dans le fichier de log, s’il est effectué une sauvegarde du journal des transactions et quelles sont les options de récupération disponible. Le mode de récupération retenu pour votre base de données possède des implications à la fois sur la récupération de la base de données et sur les performances, en fonction de la journalisation effectuée ou non par le mode.

SQL Server 2005 propose trois mode de récupération pour les bases de données :
- complet
- simple
- journalisé en bloc

Dans le mode de récupération complet, le moteur de bases de données journalise toute les opérations sans jamais les tronquer. Ce mode permet de restaurer une base de données au point de défaillance. Il s’agit du mode recommandé pour une base OLTP fortement sollicité. Le journal est vidé à chaque sauvegarde. Toutes les opérations sont journalisés dans le journal.

Dans le mode de récupération simple, le moteur de base de données limite la journalisation de la majorité des transactions et tronque le journal de transaction ( suppression des données ) après chaque point de vérification ( toutes les secondes ). Ce mode ne permet pas de sauvegarder et de restaurer le journal de transaction. Il s’agit d’un mode facile à maintenir car on ne gère guère le journal de transaction, par contre, il ne permet pas de revenir à l’instant t de la défaillance ce que permet la sauvegarde régulière du journal.

Dans le mode de récupération journalisé en bloc, le moteur de base de données journalise de façon minimal les opérations de masse comme select into et bulk insert ( BCP également ). Dans ce mode, si une sauvegarde de journal contient une quelconque opération en bloc, vous pouvez restaurer la base de données à la fin de la sauvegarde du journal, pas à un point déterminé comme dans la journalisation complète. Ce mode doit être employé lors de grosses opérations en bloc.C’est le mode recommandé pour la business intelligence.

EXEMPLE :

ALTER DATABASE MaBase SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }

La plupart des opérations de restauration débutent par la re-création de la base de données à un instant précis, puis appliquent des sauvegardes ulterieures pour ramener la base de données à un point précis dans le temps.

exemple :

RESTORE DATABASE PUBS FROM DISK 'C:\DEMOPUBS.BAK' WITH REPLACE

WITH RECOVERY : la base de données est mise en ligne.
WITH NORECOVERY : la base de données reste fixée à RESTORING, on peux appliquer une nouvelle restauration sur la base.

Sauvegarde différentielle.

exemple :
RESTORE DATABASE PUBS FROM DISK ‘C:\DEMOPUBS_FULL.BAK‘ WITH NORECOVERY
RESTORE DATABASE PUBS FROM DISK ‘C:\DEMOPUBS_DIFF.BAK‘ WITH RECOVERY

Restauration d’une sauvegarde de journal de transaction.

Récupérer une base de données sans aucune perte de données serait plus facile si les problèmes arrivait juste apres une sauvegarde, avant meme que l’application n’est effectue une quelconque transaction. Malheureusement, nous n’avons pas cette chance. Aussi dans tout scénario catastrophe, le journal contient toujours des transactions qui n’ont pas ete sauvegarde.
c’est pour cette raison que la premiere etape de toute operation de recuperation doit toujours consister à emettre une derniere commande backup log. Ce processus capture toute transaction validée non encore sauvegarde et est sauvent nomme sauvegarde de queue de journal. comme vous pouvez executer une commande back up log sur une base de donnée meme en l’indisponibilite de tout fichier de donnees. La sauvegarde de la queue de journal est le dernier journal que vous restaurez dans un processus de restauration. Ainsi, vous ne perdez aucune données.

exemple :

RESTORE DATABASE AdventureWorks FROM DISK ‘c: estawf.bak’ WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK ‘c: estaw1.trn’ WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK ‘c: estaw2.trn’ WITH RECOVERY

Merci à serge934 de la communauté developpez.com (http://www.developpez.net/forums/showthread.php?t=372323)

je te joins un script que j’utilise pour mes backups que j’ai mis dans une PSlancée par un travail

USE Master go
–drop table #BckDatabases
CREATE TABLE #BckDatabases ( databasename sysname)
SET nocount ON
declare @BckPath varchar(255)
declare @Prefix varchar(50)
declare @Extension varchar(10)
/****************************/
/* Paramétrage */
/****************************/
SET @BckPath = ‘chemin_backup’
SET @Prefix = cast(year(getdate()) AS varchar)+cast(month(getdate()) AS varchar)+cast(day(getdate()) AS varchar)
SET @Extension = ‘.bak’
INSERT INTO #BckDatabases values(‘base1′)
INSERT INTO #BckDatabases values(‘base2′) etc…
/****************************/
PRINT  »
PRINT ‘Liste des bases à Backuper :’
SELECT * FROM #BckDatabases
declare @CurrentDB sysname
declare @sql varchar(2000)
declare @fileName varchar(255)
SET nocount off
Declare curDB Cursor FOR
SELECT databasename FROM #BckDatabases
open curDBFetch
next FROM CurDB INTO @CurrentDB
while @@fetch_status = 0
begin SET @FileName = @bckPath +@Prefix +@CurrentDB + @Extension
PRINT ‘====================================================================’ PRINT ‘** ‘+@CurrentDB + ‘ Backup Started on ‘+Cast(GetDate() AS Varchar)
SET @sql = ‘BACKUP DATABASE ‘+@CurrentDB + ‘ TO DISK=N »’ + @filename +  » »
print @sql
exec (@sql)
PRINT ‘** ‘+@CurrentDB + ‘ Backup Ended on ‘+Cast(GetDate() AS Varchar)
PRINT ‘====================================================================’ PRINT  »
Fetch next FROM CurDB INTO @CurrentDB
end
deallocate CurDB
DROP TABLE #BckDatabases

tes sauvegardes s’appelleront
20070704_base1.bak
20070704_base2.bak
20070705_base1.bak
20070705_base2.bak
etc..
ensuite j’ai un travail qui supprime les fichiers dont la date est inférieure a 8 jours de la date du jour.

pour s’assurer que la base est bien sauvegarde, il faut :

tu peux le faire en faisant un « restore verifyonly » après ta sauvegarde. En fait ce n’est pas une restauration mais juste une verif.regardes
http://technet.microsoft.com/fr-fr/library/ms188902.aspx

quelques conseils:

le plan doit contenir:
-reindexations des tables les plus usitées => DBCC DBREINDEX …
-sauvegarde du journal de transaction ( et que lui !) après-test place dispo sur tes devices
-test des jobs qui pourraient se lancer pendant ta sauvegarde
-compactage SANS replacement en tête de fichier
-sauvegarde de tes bases COMPLETES (les differentielles plantent toujours a la restauration sauf chez oracle )
-test avec VERIFYONLY
-zip des .bak
-archivages des bak d’un coté et des zip d’un autre (sur un disque DIFFERENT)
PS: attention si tu es sous RAID5, ne garde que le dernier journal de transaction, supprime (del) les anciens.

Le seul moyen de s’assurer qu’une sauvegarde est bonne est d’appliquer la restauration sur une nouvelle base.

nom base initial : test
nom base finale : test2

USE master
GO
SELECT ‘kill’,spid
FROM sysprocesses
WHERE dbid=db_id(‘test2′)
GO
/* Exécution de l’output de la commande ci-dessus afin de libérer la base */
GO
RESTORE DATABASE test2 FROM DISK=N'C:\BACKUP\2007711TEST.bak' WITH
MOVE N’TEST’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test2.mdf‘,
MOVE N’TEST_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test2_Log.ldf‘, REPLACE
ALTER DATABASE test2 MODIFY FILE (NAME=N’TEST’, NEWNAME=N’test2′)
ALTER DATABASE test2 MODIFY FILE (NAME=N’TEST_log’, NEWNAME=N’test2_log’)
GO

cette technique de restauration en ligne de commande n’est pas tres utilise. Dans l’article suivant, Romelard Fabrice nous explique comment restaurer une base sur une autre à l’aide de sql serveur manager : http://www.technos-sources.com/tutorial.aspx?ID=37

Pour connaitre les processus sur le serveur :
EXEC SP_WHO

Dans cet article, j’aborderais l’utilisation des unités de sauvegarde, un outil trés pratique pour gérer toutes ses sauvegardes au même endroit dans le cas d’une sauvegarde sur bande.
Lire la suite →

Pour automatiser un backup :

declare
@filename varchar(255),
@heure char(2),
@min char(2),
@date char(8)
set @date=convert(char(8),getdate(),112)
set @heure=datepart(hh,getdate())
set @min=datepart(mi,getdate())
if @heure<=9
set @heure=’0’+@heure
if @min<=9
set @min =’0’+@minset @filename=’G:BACKUPmabase_tran_’+@date+’_’+@heure+’h’+@min+’.trn’
backup log mabase to @filename
go

Disposer d’au moins une copie d’une base de données opérationnelle en cas d’incident majeur est la tache la plus fondamentale de tout administrateur de bases de données.
Effecter une sauvegarde de bases de données est la méthode la plus fréquente pour accomplir cette tâche. Cependant, qu’effectuer une sauvegarde de base de données soit une opération fréquente ne signifie pas qu’elle est banale. Récupérer une base de données en répondant à des exigences de travail qui imposent une indisponibilité limitée et un taux maximal de pertes de données.

Sauvegarde complète.

Cette méthode de sauvegarde est toujours disponible, quelque soit le mode de récupération configuré pour une base de données.

Une sauvegarde n’est pas instantanée et susceptible de s’effectuer tandis que des utilisateurs sont connectés à la base de données et émettent des requêtes. SQL Serveur verouille la base de données , bloquant toutes les transactions, lors d’une sauvegarde.

exemple :
BACKUP DATABASE nom_base TO DISK ‘nom_dossier
om_fichier’ WITH INIT

Sauvegarde différentielle.

Différence entre une sauvegarde différentielle et une sauvegarde incrementielle. La sauvegarde différentielle contient toute l’information depuis la dernière sauvegarde complète.

exemple :
BACKUP DATABASE nom_base TO DISK ‘nom_dossier
om_fichier’ WITH DIFFERENTIAL

Sauvegarde du journal de transaction.

Vous ne pouvez effectuer une sauvegarde du journal de transactions que pour une base de données dont le mode de récupération est fixé à complet ou à journalisé en bloc et qui n’a pas encore executé de transactions journalisées de façon minimale. Une sauvegarde du journal de transactions n’est autorisée qu’apres une sauvegarde complete.

Une sauvegarde du journal sauvegarde le journal actif. Elle débute au numéro de séquence de journal LSN où a pris fin la précédente sauvegarde de journal. SQL Server sauvegarde alors toutes les transactions jusqu’à trouver une transaction ouverte. La sauvegarde se termine alors.
Tous les LSN sauvegardes peuvent alors etre supprimé du journal des transactions, ce qui permet au système de reemployer l’espace du journal.

exemple :
BACKUP LOG nom_base TO DISK ‘nom_dossier
om_fichier’ WITH INIT

Sauvegarde de groupe de fichiers.

Une sauvegarde de groupes de fichiers constitue une stratégie alternative aux sauvegardes complètes. Vous choissisez la sauvegarde par groupe de fichier lorsque la taille de la base rend peu pratique la sauvegarde et la restauration de la totalite de la base. Si vous restaurez un ou plusieurs groupes de fichiers à l’aide de sauvegarde effectuées à des moments différents, une sauvegarde du journal des transactions est une exigence minimale pour amener les groupes de fichiers à un état cohérent à un moment donné.

exemple:
BACKUP DATABASE nom_base GROUPE DE FICHIERS = ‘nom_groupe’ TO DISK ‘nom_dossier
om_fichier’ WITH INIT

sauvegarde miroir

Chaque opération de sauvegarde crée une unique copie des données sur disque ou sur bande. Il revient à l’administrateur de créer plusieurs copies pour protéger l’entreprise contre toute defaillance du support de sauvegarde.

exemple :

BACKUP DATABASE nom_base GROUPE DE FICHIERS = ‘nom_groupe’ TO DISK ‘nom_dossier
om_fichier’ WITH INIT MIRROR TO ‘nom_dossier
om_fichier’, ‘nom_dossier
om_fichier’

Sauvegarde partielle.

Il est possible qu’une base de donnée possèdent quelques groupes de fichiers en lecture seule et un groupe en lecture/ecriture.

AVEC L’option READ_WRITE_FILEGROUPS, on ne sauvegarde pas les groupes en lecture seule.

un tutorial sur la sauvegarde avec sql express :
http://www.asp-php.net/tutorial/sql-server/sauvegarder-bases-de-donnees-sql-express.php?page=6