juin
2010
Un article de zinzineti
Comment auditer un fichier backup sous SQL 2008 ?
Voici un script T-SQL pour obtenir les informations suivantes :
–>Nom du compte utilisateur sous lequel le backup est fait
–>Nom du serveur sur lequel le backup est fait
–>Nom de la base de données sauvegardée
–>le backup est-il réalisé par d’une sauvegarde instantanée type SNAPSHOT ?
–>la base de données était accessible en lecture seule au moment de la sauvegarde ?
–>la base de données était en mode mono-utilisateur au moment de la sauvegarde ?
–>Mode de restauration dans lequel était la base de données
–>Version du logiciel qui a créé la sauvegarde
–>Version de la base de donnée sauvegardée
–>Niveau de compatibilité de la sauvegarde
–>Collation utilisée par la base de données backupée
–>le fichier backup est-il compressé ?
–>…
SET @FichierBackup = 'E:\BACKUP\FichierBackup.bak'
DECLARE @maTableVar table (BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType int
,ExpirationDate datetime
,Compressed int
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,[CodePage] smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Indicateurs int
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums nvarchar(128)
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize int )
INSERT INTO @maTableVar
EXEC ('RESTORE HEADERONLY
FROM DISK = N'''+@FichierBackup+'''
WITH NOUNLOAD');
SELECT BackupName [NomBackup]
,BackupDescription[Description]
,CASE BackupType
WHEN '1' THEN 'Sauvegarde de base de données'
WHEN '2' THEN 'Sauvegarde Journal des transactions'
WHEN '4' THEN 'Sauvegarde Fichier'
WHEN '5' THEN 'Sauvegarde différentielle de Base de données'
WHEN '6' THEN 'Sauvegarde Fichier différentiel'
WHEN '7' THEN 'Sauvegarde Partielle'
WHEN '8' THEN 'Sauvegarde Partielle différentielle'
ELSE ''
END 'Type de sauvegarde'
,ExpirationDate [Date d'expiration du jeu de sauvegarde]
, CASE Compressed
WHEN '0' THEN 'Non'
WHEN '1' THEN 'Oui'
ELSE ''
END [Fichier backup est compressé ?]
,UserName [Utilisateur ayant effectué la sauvegarde]
,ServerName [Serveur sur lequel la sauvegarde est faite]
,DatabaseName [Base de données sauvegardée]
,CASE DatabaseVersion
WHEN '515' THEN 'SQL 7.0'
WHEN '539' THEN 'SQL 2000'
WHEN '611' THEN 'SQL 2005'
WHEN '612' THEN 'SQL 2005'
WHEN '655' THEN 'SQL 2008'
ELSE ''
END [Version de la base de données à partir de laquelle la sauvegarde a été créée]
,DatabaseCreationDate [Date création de la base]
,ROUND((BackupSize/1024),0) [Taille de la sauvegarde (en Ko)]
,BackupStartDate [Date et Heure démarrage du backup]
,BackupFinishDate [Date et Heure fin du backup]
,CASE CompatibilityLevel
WHEN '80' THEN 'SQL 2000'
WHEN '90' THEN 'SQL 2005'
WHEN '100' THEN 'SQL 2008'
ELSE ''
END [niveau de compatibilité de la base à partir de laquelle la sauvegarde a été créée]
,SoftwareVersionMajor [version du logiciel qui a cré la sauvegarde]
,MachineName [Machine ayant effectuée la sauvegarde]
,Collation [Collation utilisé par la base de données]
,CASE IsSnapshot
WHEN '1' THEN 'Oui'
ELSE 'Non'
END [Sauvegarde instantanée type SNAPSHOT ?]
,CASE IsReadOnly
WHEN '1' THEN 'Oui'
ELSE 'Non'
END [La base de données était accessible en lecture seule au moment de la sauvegarde ?]
,CASE IsSingleUser
WHEN '1' THEN 'Oui'
ELSE 'Non'
END [La base de données était en mode mono-utilisateur au moment de la sauvegarde ?]
,RecoveryModel [Mode de restauration de la base de données]
FROM @maTableVar
Remarques :
1. ) Si vous exécutez ce script sous SQL Server Management Studio (SSMS)2005, avec un fichier backupé sous SQL 2005 vous aurez une erreur du type :
Msg 213, Level 16, State 7, Line 1
Erreur INSERT : le nom ou le numéro de colonne des valeurs fournies ne correspond pas à la définition de la table.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY s’est terminé anormalement.
Dans ce cas exécuter tout simplement la commande :
RESTORE HEADERONLY FROM DISK = N'E:\Backup\FichierBackup.bak' WITH NOUNLOAD
2.)
Si vous exécutez ce script sous SSMS 2005, avec un fichier backupé sous SQL 2008 vous aurez une erreur du type :
Msg 3241, Level 16, State 7, Line 1
La famille de supports sur l’unité ‘E:\Backup\FichierBackup.bak’ est incorrecte. SQL Server ne peut pas la traiter.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY s’est terminé anormalement.
3.) Sous SSMS 2008 le script marche bien, vous pouvez auditer les fichiers backupés sous : SQL 2005 ou SQL 2008
/*********************************************************
— SQL 2008 – AUDIT FICHIER BACKUP
— Auteur : Etienne ZINZINDOHOUE
*********************************************************/