Sauvegarde des bases SQL serveur 2005 express

Je désire sauvegarder mes bases de données Express Server 2005 avec la requête suivante :

 

CREATE PROCEDURE dbo.SP_System_SauvegardesTotales

@SauvegardeLogs AS int,

@SauvegardeBases AS int,

@RepertoireSauvegarde AS VarChar(8000)

AS

DECLARE @name sysname

DECLARE @status int

DECLARE @TestAutoTruncate bit

DECLARE @LaRequette varchar(8000)

DECLARE @DateJour varchar(20)

DECLARE @Repertoire varchar(8000)

DECLARE @RepertoireDefaut varchar(100)

SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_')

SET @RepertoireDefaut = 'C:\BACKUPBASES\'

PRINT '-----------------------------------------------------'

PRINT ' DATE DE LA SAUVEGARDE LANCEE : '+ @DateJour

PRINT '-----------------------------------------------------'

IF RTRIM(@RepertoireSauvegarde) != ''

BEGIN

PRINT 'Répertoire Fourni à la SP'

IF RIGHT(@RepertoireSauvegarde, 1) != '\'

SET @Repertoire = @RepertoireSauvegarde + '\'

ELSE

SET @Repertoire = @RepertoireSauvegarde

END

ELSE

BEGIN

PRINT 'Répertoire Non Fourni à la SP - Création du répertoire par défaut : '+ @RepertoireDefaut

SET @LaRequette = 'master.dbo.xp_cmdshell ''MKDIR '+ @RepertoireDefaut +''', NO_OUTPUT '

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

SET @Repertoire = @RepertoireDefaut

END

PRINT 'Répertoire de Sauvegarde des Bases et Logs : '+ @Repertoire

DECLARE TESTCURSEUR CURSOR

FOR SELECT master.dbo.sysdatabases.name, master.dbo.sysdatabases.status

FROM master.dbo.sysdatabases

OPEN TESTCURSEUR

FETCH NEXT FROM TESTCURSEUR

INTO @name, @status

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TestAutoTruncate = @status & 8

IF (@SauvegardeLogs = 1) AND (@TestAutoTruncate = 0)

BEGIN

PRINT ''

PRINT '-----------------------------------------------------'

PRINT 'BACKUP DU LOG DE LA BASE DE DONNEES : '+ @name

SET @LaRequette = 'USE '+ @name +' EXEC sp_addumpdevice ''DISK'', '''+ @name +'Log'', '''+ @Repertoire + @name +'Log.BAK'' BACKUP LOG '+ @name +' TO '+ @name +'Log'

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

SET @LaRequette = 'USE '+ @name +' EXEC sp_dropdevice '''+ @name +'Log'' '

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

SET @LaRequette = 'master.dbo.xp_cmdshell ''C:\WINDOWS\system32\makecab /V[1] /L '+ @Repertoire +' '+ @Repertoire + @name +'Log.BAK '+ @name +'Log_'+ @DateJour +'.cab'', NO_OUTPUT '

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL '+ @Repertoire + @name +'Log.BAK'', NO_OUTPUT '

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

END

IF @SauvegardeBases = 1

BEGIN

PRINT ''

PRINT '-----------------------------------------------------'

PRINT 'BACKUP DE LA BASE DE DONNEES : '+ @name

SET @LaRequette = 'USE '+ @name +' BACKUP DATABASE '+ @name +' TO DISK ='''+ @Repertoire + @name +'.BAK'''

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

SET @LaRequette = 'master.dbo.xp_cmdshell ''C:\WINDOWS\system32\makecab /V[1] /L '+ @Repertoire +' '+ @Repertoire + @name +'.BAK '+ @name +'_'+ @DateJour +'.cab'', NO_OUTPUT '

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL '+ @Repertoire + @name +'.BAK'', NO_OUTPUT '

PRINT 'Requette : '+ @LaRequette

EXECUTE (@LaRequette)

END

PRINT ''

PRINT '-----------------------------------------------------'

FETCH NEXT FROM TESTCURSEUR

INTO @name, @status

END

CLOSE TESTCURSEUR

DEALLOCATE TESTCURSEUR

GO

 

 

La requête semble fonctionner mais lorsque je lance son exécution par la requète suivante :

 

EXEC master.dbo.SP_System_SauvegardesTotales 1, 1, 'C:\BackupExpress\'

 

J'ai les messages d'erreurs suivants :

 

 

-----------------------------------------------------

 DATE DE LA SAUVEGARDE LANCEE : 2009_06_22

-----------------------------------------------------

Répertoire Fourni à la SP

Répertoire de Sauvegarde des Bases et Logs : C:\BackupExpress\

 

-----------------------------------------------------

BACKUP DE LA BASE DE DONNEES : master

Requette : USE master BACKUP DATABASE master TO DISK ='C:\BackupExpress\master.BAK'

376 pages traitées pour la base de données 'master', fichier 'master' dans le fichier 1.

3 pages traitées pour la base de données 'master', fichier 'mastlog' dans le fichier 1.

BACKUP DATABASE a traité avec succès 379 pages en 0.263 secondes (11.805 Mo/s).

Requette : master.dbo.xp_cmdshell 'C:\WINDOWS\system32\makecab /V[1] /L C:\BackupExpress\ C:\BackupExpress\master.BAK master_2009_06_22.cab', NO_OUTPUT

Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress\master.BAK', NO_OUTPUT

 

-----------------------------------------------------

 

-----------------------------------------------------

BACKUP DE LA BASE DE DONNEES : tempdb

Requette : USE tempdb BACKUP DATABASE tempdb TO DISK ='C:\BackupExpress\tempdb.BAK'

Msg 3147, Niveau 16, État 3, Ligne 1

La sauvegarde et la restauration ne sont pas autorisées sur la base de données tempdb.

Msg 3013, Niveau 16, État 1, Ligne 1

BACKUP DATABASE s'est terminé anormalement.

Requette : master.dbo.xp_cmdshell 'C:\WINDOWS\system32\makecab /V[1] /L C:\BackupExpress\ C:\BackupExpress\tempdb.BAK tempdb_2009_06_22.cab', NO_OUTPUT

Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress\tempdb.BAK', NO_OUTPUT

 

-----------------------------------------------------

-----------------------------------------------------

BACKUP DE LA BASE DE DONNEES : SL_M2-200901

Requette : USE SL_M2-200901 BACKUP DATABASE SL_M2-200901 TO DISK ='C:\BackupExpress\SL_M2-200901.BAK'

Msg 102, Niveau 15, État 1, Ligne 1

Syntaxe incorrecte vers '-'.

Msg 102, Niveau 15, État 1, Ligne 1

Syntaxe incorrecte vers '-'.

Requette : master.dbo.xp_cmdshell 'C:\WINDOWS\system32\makecab /V[1] /L C:\BackupExpress\ C:\BackupExpress\SL_M2-200901.BAK SL_M2-200901_2009_06_22.cab', NO_OUTPUT

Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress\SL_M2-200901.BAK', NO_OUTPUT

 

-----------------------------------------------------

 

 

 

Qu'est-ce qui n'est pas correct ?

 

Par ailleurs, comment dois-je procéder pour ne sauvegarder que les bases d'une certaine instance ou que les bases commençant par exemple par sl ?

 

De plus, que risque-t-on lorsque l'on fait une sauvegarde par une simple copie des fichiers MDF/LDF ou du répertoire Data ? Que cela change-t-il par rapport à un agent de sauvegarde d'un logiciel spécifique ?

 

Merci d'avance !

Francois

A premiere vue je dirais que le - problématique viens du nom de la base dans la commande USE.

Je changerais ainsi :

SET @LaRequette = 'USE ['+ @name +'] BACKUP DATABASE '+ @name +' TO DISK ='''+ @Repertoire + @name +'.BAK'''

Cela devrais corriger ton probleme.


Pour restreindre les bases a sauvegarder je modifirais le curseur :


DECLARE TESTCURSEUR CURSOR
FOR SELECT master.dbo.sysdatabases.name, master.dbo.sysdatabases.status
FROM master.dbo.sysdatabases
WHERE master.dbo.sysdatabases.name LIKE 'SL%'

Pour ce qui est de sauvegarder le repertoir DATA, il n'y a aucun risque si ton SQL EXPRESS est arrêté, on appel cela une sauvegarde à froid.
La commande backup permet de faire des sauvegarde à chaud (instance SQL EXPRESS démarrée). C'est a toi de voir selon tes problématique de disponibilité.

Si ton SQL EXPRESS est démarré et que tu sauvegarde le repertoir DATA tes fichiers de bases de données ont toutes les chances de ne pas etre cohérant et de ne jamais pouvoir servir a réstauré quoi que ce soit.

Merci bcp pour ta rapide réponse. Je vais aller dans le sens indiqué et regarder le résultat

Par contre, concernant le chaud vs froid, j'imagine que ça peut poser un problème si on fait une sauvegarde (simple copie des fichiers) à chaud alors que des utilisateurs travaillent sur la base. Par contre, si le copie se fait au milieu de la nuit et que personne ne travaille dessus, est-ce vraiment problématique ?

Merci d'avance !

Francois

Tant que ton serveur SQL est démarré cela reste problématique, il n'y a pas que les tables utilisateurs dans ta base et les tables systems vivent meme sans utilisateurs connectés. En revanche si tu es sur qu'aucun utilisateur ne se connecte, il y a peu de probleme pour arreter le service Sql Server (SQLEXPRESS) (via commande DOS : net stop MSSQL$SQLEXPRESS ) copier tes fichiers .mdf et .ldf dans un repertoire de sauvegarde et redémarrer le service (via la commande DOS : Net start MSSQL$SQLEXPRESS )

je me relis et je vois que j'ai oublier quelque chose.

Quand je te fais rajouter des [] pour encadrer ton nom de base dans la commande USE il faut aussi les rajouter dans la commande backup :

SET @LaRequette = 'USE ['+ @name +'] BACKUP DATABASE ['+ @name +'] TO DISK ='''+ @Repertoire + @name +'.BAK'''

Merci beaucoup, ça semble bien marcher. J'ai juste encore deux erreurs :

BACKUP DE LA BASE DE DONNEES : master
Requette : USE [master] BACKUP DATABASE [master] TO DISK ='C:\BackupExpress\master.BAK'
Msg 3201, Niveau 16, État 1, Ligne 1
Impossible d'ouvrir l'unité de sauvegarde 'C:\BackupExpress\master.BAK'. Erreur 3(Le chemin d'accès spécifié est introuvable.) du système d'exploitation.
Msg 3013, Niveau 16, État 1, Ligne 1
BACKUP DATABASE s'est terminé anormalement.
Requette : master.dbo.xp_cmdshell 'C:\WINDOWS\system32\makecab /V[1] /L C:\BackupExpress\ C:\BackupExpress\master.BAK master_2009_06_24.cab', NO_OUTPUT
Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress\master.BAK', NO_OUTPUT

-----------------------------------------------------
BACKUP DE LA BASE DE DONNEES : tempdb
Requette : USE [tempdb] BACKUP DATABASE [tempdb] TO DISK ='C:\BackupExpress\tempdb.BAK'
Msg 3147, Niveau 16, État 3, Ligne 1
La sauvegarde et la restauration ne sont pas autorisées sur la base de données tempdb.
Msg 3013, Niveau 16, État 1, Ligne 1
BACKUP DATABASE s'est terminé anormalement.
Requette : master.dbo.xp_cmdshell 'C:\WINDOWS\system32\makecab /V[1] /L C:\BackupExpress\ C:\BackupExpress\tempdb.BAK tempdb_2009_06_24.cab', NO_OUTPUT
Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress\tempdb.BAK', NO_OUTPUT


Par contre, ces deux tables (tempdb et master) ne semblent pas contenir de données significatives, je peux m'en passer je pense ?!

Merci encore !

Francois

Tempdb tu peux largement t'en passer. Elle ne contient que des objets temporaire déstiné aux tris. Pour information cette base est détruite et recréée a chaque démarrage de l'instance. C'est d'ailleur pour ca que tu ne peux pas la sauvegarder (comme indiquer dans le message d'erreur).

La base master en revanche est d'une importance capitale car c'est elle qui contient toutes les informations de fonctionnement du moteur.  Je te recommande de la sauvegarder à chaque fois que tu manipule un objet du serveur (connexion, nouvelle base, option de l'instance, etc.)
Pour ton erreur sur la sauvegarde de master c'est simplement que le repertoir de destination n'existe pas sur ton disque C:

Pour ne pas t'ennuyer tu peux ajouter au debut de ton script :

master

.dbo.xp_cmdshell'mkdir C:\BackupExpress\', NO_OUTPUT

Pour le problème Master, ça fonctionne

Par contre, je désire exclure la table tempdb mais ça ne joue toujours pas. J'ai essayé :

WHERE master.dbo.sysdatabases.name not LIKE 'tempdb%'

ou encore

WHERE master.dbo.sysdatabases.name <> 'tempdb'

Est-ce que ça vient du fait que c'est une base du système et non une base normale ? Y'a une solution pour l'exclure et ainsi avoir une sauvegarde sans erreur ?

Merci encore pour tes rapides réponses ! Normalement c'est ma dernière demande, après c'est tout bon !

Francois

normalement ton curseur devrait ressembler a ca :



DECLARE TESTCURSEUR CURSOR
FOR SELECT master.dbo.sysdatabases.name, master.dbo.sysdatabases.status
FROM master.dbo.sysdatabases
WHERE master.dbo.sysdatabases.name not in ('tempdb','model')

(j'exclus aussi model qui ne presente pas d'interet particulier dans la plus part des cas).

Je viens de tester ta proc chez moi avec ce curseur, elle fonctionne parfaitement et tempdb est bien exclue.

Mille mercis, ça fonctionne enfin parfaitement. @+