SQL Server vu par Christian Robert

SQL Server : Sauvegarder ses bases de données vers un fichier horodaté et vérifier les sauvegarde automatiquement

Pour commencer la semaine, voici un script d'automatisation de sauvegarde, qui vous permet d'un part d'intégrer la date à vos fichiers de sauvegarde et d'autre part de lancer la vérification de celle-ci en se basant sur un calcul de CHECKSUM.

Dans ce genre de cas, une logique à base de curseur est de loin ce qui se fait de plus simple, on exécute la commande BACKUP DATABASE dans cette boucle en lui passant 2 variables, l'une correspondant au nom de la base de données, l'autre au nom du fichier cible.

J'utilise une nouveauté de SQL Server 2005, qui consiste dans le calcul d'un CHECKSUM pour permettre la vérification de l'intégrité du fichier de sauvegarde (ne pas confondre avec le CHECKSUM intégré dans les pages au sein de SQL Server) et à la suite de la sauvegarde la commande de vérification (RESTORE VERIFYONLY) qui n'est pas nouvelle, mais intègre l'option de CHECKSUM elle aussi.

Libre à vous d'y ajouter des blocs TRY / CATCH si nécessaire et aussi de mettre un DBCC CHECKDB avant la sauvegarde pour assurer l'intégrité logique de la base de données. On pourra aussi paralléliser un peu plus l'exécution en réalisant le CHECKDB d'une base pendant le BACKUP d'une autre et le VERIFY d'une autre encore. Mais là ça devient compliqué.

Le script :

DECLARE @path nvarchar(4000) ;
DECLARE @dbid int, @dbname sysname ;
DECLARE @filename nvarchar(max) ;

SET @path = 'C:\BACKUP\' ;

DECLARE crsDB CURSOR FOR SELECT database_id, name FROM sys.databases WHERE name != 'tempdb' FOR READ ONLY ;

OPEN crsDB ;

FETCH NEXT FROM crsDB INTO @dbid, @dbname ;

WHILE ( @@FETCH_STATUS = 0)
BEGIN

    SET @filename = @path + CASE WHEN RIGHT(@path, 1) != N'\' THEN N'\' ELSE N'' END + @dbname + CONVERT(nvarchar(20),GETDATE(), 112) + '.BAK';

BACKUP DATABASE @dbname TO DISK = @filename WITH CHECKSUM;
RESTORE VERIFYONLY FROM DISK = @filename WITH CHECKSUM ;

FETCH NEXT FROM crsDB INTO @dbid, @dbname ;

END

CLOSE crsDB ;
DEALLOCATE crsDB ;

Dernière chose pour déterminer l'emplacement par défaut au SQL Server dépose ses sauvegarde :

-- Récupère l'emplacement par défaut des sauvegardes

DECLARE @regpath nvarchar(4000);
DECLARE @instancename nvarchar(128), @regvalue nvarchar(1000);

-- Détermine le nom de l'instance
SELECT @instancename = CAST(CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER' ELSE serverproperty('InstanceName') END as nvarchar(128))

SET @regpath = 'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL' ;

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@regpath,
@value_name=@instancename,
@value=@regvalue OUTPUT

SET @regpath = 'Software\Microsoft\Microsoft SQL Server\' + @regvalue + '\MSSQLServer' ;

-- Trouve le chemin d'installation
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@regpath,
@value_name='BackupDirectory',
@value=@regvalue OUTPUT

SET @path = @regvalue ;

Ce dernier script peut être inséré dans la suite de la déclaration de variable du code de sauvegarde.

Bonne sauvegarde…