Backup automatique BDD SQL server Express

**Feliperennes** · 13/09/2018, 06h33

Bonjour,
J'utilise une BDD SQL server 2008 R2 Express.
Actuellement je fais des backup manuels et ça fonctionne très bien.
Je souhaiterais automatiser les backup dans le planificateur de tâches de W10.
J'ai créé le script suivant en .bat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
--- <Commencer le code script de code> ---
 sqlcmd -S NomDeMonPC \ SQL Server 10.50.1600 - MonNom -E -Q 
 "BACKUP DATABASE NomBDD TO NomFichierBackup"
 --- <Fin Code Batch Script> -----

Problème ça ne fonctionne pas automatiquement alors que manuellement il n'y a aucun problème
Quelqu'un peut-il m'aider ? (je suis un peu novice ...)
Merci

**aieeeuuuuu** · 13/09/2018, 11h34

bonjour,

Pouvez vous préciser le "ça ne fonctionne pas" ???

Le bat ne se lance pas ?
Il se lance mais plante ? (quel est alors le message d'erreur)
Il se lance, mais vous ne trouvez pas le fichier de sauvegarde ?

**nico84** · 13/09/2018, 14h57

Bonjour,

J'ai fait cela aussi il y a qq temps et mon .bat contient ceci qui marche très bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

sqlcmd -S SRV-TSE -E -Q "EXEC sp_BackupDatabases @backupLocation='\\srv-hg-ad\serveur2\planet\HG\tmp\', @databaseName='HG', @backupType='F'"

De mémoire on ne peut pas faire le backup directement avec SQL Express, il faut utiliser la procédure sp_BackupDatabase. Une recherche sur ce mot t'en dira plus

NB : j'accède à la base avec l'identité windows

Invité · 13/09/2018, 15h43

Envoyé par nico84

De mémoire on ne peut pas faire le backup directement avec SQL Express, il faut utiliser la procédure sp_BackupDatabase. Une recherche sur ce mot t'en dira plus

??? J'ai mis en place un .bat qui fait un backup avec la commande BACKUP DATABASE sur pas loin d'une centaine de version Express et ça marche !
Il faut juste avoir un user qui a les bons droits niveau OS, répertoire et instance pour lancer la job dans le scheduler.
Si ça vous intéresse, je peux essayer de vous retrouver la doc que j'avais faite là dessus.

**nico84** · 13/09/2018, 16h48

Comme c'était en 2015 j'ai replongé dans mes archives et à l'époque après avoir lu https://support.microsoft.com/en-us/kb/2019698 j'en avais conclu qu'il fallait utiliser une procédure d'où mon script

sp_backupdatabases est en pratique une procédure que j'ai écrit à partir d'un modèle trouvé sur le net et qui a 2 avantages :
- elle génère le nom du fichier sauvegarde à partir de la date de la sauvegarde
- elle a une option sauvegarde totale (1 fois par jour) ou différentielle (3 fois par jour)

Au final cela me convient bien mais si je comprends bien on peut faire plus simple !

**i.chafai** · 13/09/2018, 21h27

Vous pouvez utiliser les scripts de Ola Hallengren
https://ola.hallengren.com

**Feliperennes** · 14/09/2018, 03h51

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

Si ça vous intéresse, je peux essayer de vous retrouver la doc que j'avais faite là dessus.

Bonjour,
Oui ça m'intéresse, je suis débutant et ne demande qu'à apprendre ...
La meilleure façon d'apprendre c'est encore par l'exemple
Merci

**Feliperennes** · 14/09/2018, 05h39

Envoyé par nico84

Bonjour,

J'ai fait cela aussi il y a qq temps et mon .bat contient ceci qui marche très bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

sqlcmd -S SRV-TSE -E -Q "EXEC sp_BackupDatabases @backupLocation='\\srv-hg-ad\serveur2\planet\HG\tmp\', @databaseName='HG', @backupType='F'"

De mémoire on ne peut pas faire le backup directement avec SQL Express, il faut utiliser la procédure sp_BackupDatabase. Une recherche sur ce mot t'en dira plus

NB : j'accède à la base avec l'identité windows

J'ai essayé ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sqlcmd -S SRV-TSE -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\Temp\', @databaseName='MaBDD', @backupType='F'"

La fenêtre d'exécution s'ouvre pendant quelques instants puis se referme sans aucun message mais pas de fichier backup dans D:\Temp
Que signifie SRV-TSE ? Dois-je remplacer ça par le nom de mon PC ? (désolé pour mon ignorance, pas toujours facile de partir de 0)
Pour info l'authentification sur le server SQL est "SQL server and Windows authentification mode"

**nico84** · 14/09/2018, 10h56

Envoyé par Feliperennes

Que signifie SRV-TSE ? Dois-je remplacer ça par le nom de mon PC ?

Oui nom du serveur ou du PC

Comme expliqué dans mon 2e post, la procédure sp_backupdatabases est à créer, voici son code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/* =============================================  
  NG/22.12.2015 extrapolé depuis la procédure MS : https://support.microsoft.com/en-us/kb/2019698
  Description: Backup Databases for SQLExpress
  - Parameter1: databaseName  
  - Parameter2: backupType F=full, D=differential, L=log
  - Parameter3: backup file location
 
Appel par msql-backup.bat dans tache planifiée :
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='\\srv-hg-ad\serveur2\planet\backup\', @databaseName=’HG’, @backupType='F'"
ou à midi :
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='\\srv-hg-ad\serveur2\planet\backup\', @databaseName=’HG’, @backupType='D'"
*/
USE [master]  
GO  
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
 
CREATE PROCEDURE [dbo].[sp_BackupDatabases] @databaseName sysname = null, @backupType CHAR(1), @backupLocation nvarchar(200)  
AS  
  SET NOCOUNT ON;  
 
  -- Declare variables
  DECLARE @BackupName varchar(100)
  DECLARE @sqlCommand NVARCHAR(500)  
  /* pour tests
  DECLARE @databaseName VARCHAR(20)    set @databaseName='xxx'
  DECLARE @backupType VARCHAR(1)       set @backupType='D'
  DECLARE @backupLocation VARCHAR(100) set @backupLocation='\monpc\repertoire_backup\'
  */
 
  -- nom de la sauvegarde = date YYMMDD + option heure HHMMSS
  SET @BackupName = @databaseName+REPLACE(CONVERT(VARCHAR, GETDATE(),2),'.','')
  --cast(datediff(d, GETDATE(), DATEFROMPARTS(year(getdate()), 1, 1)) as nvarchar)
 
  IF @backupType = 'D' 
    SET @BackupName = @BackupName+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')+'.log'  -- 108 = hhmmss
  ELSE                 
    SET @BackupName = @BackupName+'.dat'
 
  -- Generate the dynamic SQL command to be executed
  SET @sqlCommand = 'BACKUP DATABASE [' +@databaseName+ '] TO DISK='''+@backupLocation+@BackupName+''' WITH INIT, NOSKIP, NOFORMAT'
  IF @backupType = 'D' SET @sqlCommand = @sqlCommand+', DIFFERENTIAL'
 
  -- Execute the generated SQL command
  --select @sqlCommand from hg.dbo.parametres
  EXEC(@sqlCommand)
GO

**Feliperennes** · 14/09/2018, 14h34

Envoyé par nico84

Comme expliqué dans mon 2e post, la procédure sp_backupdatabases est à créer, voici son code :

Si je comprends bien:
1/ il me faut cliquer droit sur master dans le SQL Server Management studio et cliquer sur New Query
2/ Recopier le code que tu m'as envoyé à partir de USE [master] jusqu'à la fin dans cette New Query
3/ Sauvegarder ce code avec le nom sp_BackupDatabases (y-a-t-il un lieu où sauvegarder ce fichier ou bien est-ce automatique ?)
4/ Créer un fichier en .bat comprenant la ligne de commande suivante si je veux un full backup

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\Temp\', @databaseName=’NomBDD’, @backupType='F'"

et appeler ce fichier à partir du planificateur de tâche.

Est-ce bien ça ?

**Feliperennes** · 14/09/2018, 04h23

Envoyé par aieeeuuuuu

bonjour,

Pouvez vous préciser le "ça ne fonctionne pas" ???

Le bat ne se lance pas ?
Il se lance mais plante ? (quel est alors le message d'erreur)
Il se lance, mais vous ne trouvez pas le fichier de sauvegarde ?

Pour préciser la tâche s'effectue à l'heure programmée et est déclarée avoir été exécutée avec succès.
Aucun message d'erreur d'aucune sorte.
Pas de fichier de sauvegarde trouvé même après une recherche sur tous les disques du PC

**janlouk** · 14/09/2018, 13h16

Envoyé par Feliperennes

Pas de fichier de sauvegarde trouvé même après une recherche sur tous les disques du PC

Ce n'était pas une opération de backup dans mon cas, mais j'ai déjà eu ce genre de problème et pour le résoudre j'ai du mettre "everyone" avec full rights sur le dossier. C'est une piste même si je doute dans ton cas.

Mais comme dit plus haut, tu devras t'intéresser aux scripts OLA. J'ai toujours utilisé ces scripts par des jobs via l'agent SQL, mais je suppose qu'il est possible de les implémenter avec le planificateur de Windows.

**aieeeuuuuu** · 14/09/2018, 15h00

Envoyé par Feliperennes

Pas de fichier de sauvegarde trouvé même après une recherche sur tous les disques du PC

La question peut paraitre idiote, mais le fait que vous parliez de PC me fait dire qu'elle mérite tout de même d'être posée :
Est-ce bien sur le serveur (et non sur la machine depuis laquelle vous avez lancé la commande de sauvegarde) que vous avez vérifié ?

Par ailleurs, avez-vous regardé le journal des évènements de SQL Serveur ? y voyez vous des traces de la commande de sauvegarde ?

**Feliperennes** · 14/09/2018, 15h47

Envoyé par aieeeuuuuu

La question peut paraitre idiote ...

La question n'est pas idiote, en fait je débute dans l'univers SQL et pour l'instant je travaille en local avec SQL server 2008 R2 installé sur mon PC W10.
Par la suite, je compte bien installer mes DBB sur un serveur mais j'y vais par étapes ...

Par ailleurs, avez-vous regardé le journal des évènements de SQL Serveur ? y voyez vous des traces de la commande de sauvegarde ?

Non je n'ai pas regardé et c'est ça qui peut paraître idiot mais je ne sais pas où aller voir ...