Sauvegarde des bases SQL serveur 2005 express

Question

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 empdb.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 empdb.BAK tempdb_2009_06_22.cab', NO_OUTPUT Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress empdb.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

nivsql · Answer

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.

francoissql · Answer

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

nivsql · Answer

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 )

nivsql · Answer

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'''

francoissql · Answer

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	empdb.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	empdb.BAK tempdb_2009_06_24.cab', NO_OUTPUT 
Requette : master.dbo.xp_cmdshell 'DEL C:\BackupExpress	empdb.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

nivsql · Answer

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

francoissql · Answer

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

nivsql · Answer

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.

francoissql · Answer

Mille mercis, ça fonctionne enfin parfaitement. @+

nivsql · Answer

Par contre si je peux me permettre, a moyen terme je te conseil de changer ton code pour interroger la vue master.sys.databases plutot que master.dbo.sysdatabases.

En effet la seconde est encore la pour des questions de compatibilité SQL 2000 mais risque de disparaitre à moyen terme. De plus la premiere vu dispose du champ recovery_model qui t'indique de maniere directe si la base est en mode de reccupération simple (valeur du champ 3), bulk-load (valeur du champ 2) ou complet (valeur du champ 1). Cela changerais ton code suivant :

SET @TestAutoTruncate = @status & 8 IF (@SauvegardeLogs 1) AND (@TestAutoTruncate 0)

en : 

IF (@SauvegardeLogs = 1) AND (@status  < 3)

francoissql · Answer

Bonjour,

Mes sauvegardes semblent bien fonctionner.

Lorsque je fais une sauvegarde d'une base de mon poste et que je restaure sur mon poste, c'est parfait

Par contre, si je fais une sauvegarde sur un autre serveur (donc autre instance) et que je veux restaurer sur mon poste avec mon instance, j'ai un message d'erreur.

La restauration n'est pas possible, problème d'accès :

"Échec de Restaurer pour Serveur « FM-PORTABLE\SELECTLINE ».  (Microsoft.SqlServer.Express.Smo)


Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restaurer+Server&LinkId=20476


------------------------------
INFORMATIONS SUPPLÉMENTAIRES :


System.Data.SqlClient.SqlError: Le système d'exploitation a retourné l'erreur '5(Accès refusé.)' lors de la tentative de 'RestoreContainer::ValidateTargetForCreation' sur 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\SL_MGM.mdf'. (Microsoft.SqlServer.Express.Smo)


Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476"

J'imagine que c'est normal mais comment peut-on solutionner ce problème ? En changeant la sauvegarde ou en changeant la restauration ?

Francois

nivsql · Answer

En fait, je pense que le probleme viens de ton ordre RESTORE 

L'OS t'empeche d'ecraser un fichier (ou de le créer) soit parceque le repertoire de destination n'existe pas soit parce le fichier est déjà présent et que tu n'a pas préciser l'option permettant d'ecraser ce fichier. Cela peut se produire si la base que tu as backup n'est pas dans la meme arborescence que ton portable.

francoissql · Answer

En fait je tente de restaurer par le studio manager.

J'ai cherché mais je n'ai pas trouvé d'option qui permettre d'écraser un fichier ou de créer un répertoire

J'ai retourné ça dans tous les sens mais je n'arrive pas à restaurer ma base

Francois

nivsql · Answer

Soit tu veux ecraser la base existante (meme nom de bases et memes noms de fichiers) dans ce cas coche la case indiquée par la fleche rouge du haut.

Soit tu ne veux pas auquel cas essaye de déplacer tout tes fichiers de base via les bouton indiqué par la fleche rouge verticale.

francoissql · Answer

Voilà ce qu'il se passe quand on n'ouvre pas correctement les yeux... 

Merci beaucoup et désolé pour le dérangement

Bon week-end !

Francois

nivsql · Answer

Pas de soucis ;)

cs_ferservadou · Answer

Bonjour,

  Vous qui avez une grande expérience de MSDE, pouvez-vous me dire s'il est possible de sauvegarder ce type de BD en l'arrêtant, puis en sauvegardant tous les fichiers .ldf et .mdf ?

  Si je restaure ensuite le jeu de fichiers d'une même date (toujours base arrêtée), pensez-vous qu'elle redémarrera proprement ?

  Merci d'avance de vos conseils.

nivsql · Answer

Si tu concerve correctement les chemins de fichiers oui. N'oublie pas que ces chemins sont stocké dans la base MASTER.

Cela dis l'interet est assez limité, autant faire une sauvegarde a chaud que tu pourra restaurer n'importe ou quelque que soit l'arborescence des repertoire (puisque tu pourra modifier les chemins de fichier lors de la restauration).

cs_ferservadou · Answer

Merci de ta réponse, qui me rassure ! :-)

Je ne fais pas de sauvegarde à chaud parce que le serveur sur lequel se trouve cette base est sauvegardé automatiquement par l'outil TimeNavigator (standard dans mon entreprise) et que, si cet outil a bien des modules qui lui permettent d'assurer la sauvegarde "à chaud" de la plupart des BD, il n'en a que pour SQL server et pas pour MSDE...

J'ai donc des scripts qui arrêtent l'application qui utilise la BD, puis la BD elle-même, et qui redémarrent tout ce petit monde une fois la sauvegarde effectuée. Heureusement, je peux me le permettre, car cette application ne tourne pas la nuit !

Mais si tu penses qu'une "vraie" sauvegarde serait mieux, je suis preneur de tous les conseils éclairés. ;-)

Merci.

nivsql · Answer

1) J'utilise Time Navigator aussi. 
2) MSDE est la version legere de SQL Serveur 2000 (DE = Desktop Edition) et est donc sauvegardable a chaud via TINA !

3) TINA est capable de lancer des script, tu peux donc sauvegarder a chaud tes bases via un script OSQL (version 2000 de SQLCMD) et sauvegarder les fichier .bak généré par le script via la sauvegarde "system" de TINA. 

4) Tu peux migrer facilement ton SQL Serveur 2000 DE vers un 2005 Express sans avoir a retoucher tes apply en laissant le niveau de compatibilité de tes base a 80 (a condition qu'elle soient toute en 80, si tu as sur ton MSDE des bases hérité de SQL 7 qui sont en compatibilité 70 c'est déjà plus problématique) et donc devenir completement compatible avec TINA pour l'agent de sauvegarde a chaud.

Sauvegarde des bases SQL serveur 2005 express

32 réponses

Votre réponse