Backup automatique BDD SQL server Express

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

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, 10h34

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, 13h57

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, 14h43

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, 15h48

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, 20h27

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

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

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, 03h23

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

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

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, 09h56

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

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

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.

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

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 ?

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

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, 14h47

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

**aieeeuuuuu** · 14/09/2018, 14h57

vous pouvez, dans un premier temps regarder dans management studio : Gestion-->journaux SQL Server. Normalement, les sauvegardes y sont tracées.

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

Je ne vois que les sauvegardes que j'ai déclenchées dans management studio.
De toutes façon il me faut comprendre ce que m'a envoyé Nico84 et faire fonctionner ça avant d'aller plus loin.

**nico84** · 14/09/2018, 16h40

Envoyé par Feliperennes

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 ?

Pas tout à fait
1/ copier mon code dans un fichier texte backup.sql (par exemple)
2/ ouvrir ce fichier depuis sql server management studio
3/ cliquer sur executer : ainsi la procédure est ajoutée à la base master - attention pour la modifier ensuite il faudra remplacer "create procedure" par "alter procedure"
4/ ok sauf que .\SQLEXPRESS ne ressemble pas à un nom de PC...

**Feliperennes** · 14/09/2018, 17h58

Alors j'avais déjà réussi les 3 premières étapes. Je ne vois pas la raison de modifier cette procédure si elle fonctionne. D'ailleurs quand je charge à nouveau le fichier backup.sql j'ai l'indication que l'objet sp_BackupDatabases est déjà dans la DataBase.

Pour la l'étape 4 est-ce correct ? Car

Code :

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

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

Quand je double-clique sur le fichier .bat j'ai bien la fenêtre d'exécution qui s'ouvre très brièvement mais je ne trouve pas le backup dans le dossier D:\Temp

**nico84** · 14/09/2018, 18h55

\\MON-PC\D:\Temp\ c'est bizarre comme nom de dossier

Avant de tester avec un .bat :

- faire une sauvegarde en direct
- faire une sauvegarde avec la procédure

**Feliperennes** · 14/09/2018, 19h12

Envoyé par nico84

\\MON-PC\D:\Temp\ c'est bizarre comme nom de dossier

J'ai essayé plusieurs choses avec le même résultat, je ne trouve pas le backup dans Temp

Code :

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

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

Avant de tester avec un .bat :

- faire une sauvegarde en direct

Je fais depuis longtemps mes backup depuis le Manager et ça fonctionne très bien

- faire une sauvegarde avec la procédure

Par contre avec le .bat ça ne fonctionne pas
Quand tu dis avec la procédure tu veux dire avec le .bat ou autre chose ?

Backup automatique BDD SQL server Express [2008R2]

Administration SQL Server

Discussions similaires

Partager

Partager