Backup automatique BDD SQL server Express

Version imprimable

13/09/2018, 06h33
Feliperennes

Backup automatique BDD SQL server Express
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:

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
13/09/2018, 11h34
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 ?
13/09/2018, 14h57
nico84

Bonjour,

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

Code:

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
13/09/2018, 15h43
Invité

Citation:

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.
13/09/2018, 16h48
nico84

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 !
13/09/2018, 21h27
i.chafai

Vous pouvez utiliser les scripts de Ola Hallengren
https://ola.hallengren.com
14/09/2018, 03h51
Feliperennes

Citation:

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
14/09/2018, 04h23
Feliperennes

Citation:

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
14/09/2018, 05h39
Feliperennes
Citation:

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:

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:

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"

Citation:

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:

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

14/09/2018, 13h16
janlouk

Citation:

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.
14/09/2018, 14h34
Feliperennes
Citation:

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:

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 ?
14/09/2018, 15h00
aieeeuuuuu

Citation:

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 ?
14/09/2018, 15h47
Feliperennes

Citation:

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

Citation:

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 ...
14/09/2018, 15h57
aieeeuuuuu

vous pouvez, dans un premier temps regarder dans management studio : Gestion-->journaux SQL Server. Normalement, les sauvegardes y sont tracées.
14/09/2018, 16h50
Feliperennes

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.
14/09/2018, 17h40
nico84
Citation:
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:

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...
14/09/2018, 18h58
Feliperennes
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:

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
14/09/2018, 19h55
nico84

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

Avant de tester avec un .bat :

- faire une sauvegarde en direct
- faire une sauvegarde avec la procédure
14/09/2018, 20h12
Feliperennes
Citation:

Envoyé par nico84

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

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

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

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

Citation:

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

C'est bien ça me fait réviser ;)
Il y a une erreur de syntaxe c'est 'MaBDD' et non ’MaBDD’ (apostrophe et non je sais pas quoi)

La procédure peut s'exécuter directement depuis le transac sql
Code:

1 2 3 USE [master] GO EXEC sp_BackupDatabases @backupLocation='c:\planet\', @databaseName='HG', @backupType='F'
J'ai en retour dans la fenêtre du bas
Code:

1 2 3 101392 pages traitées pour la base de données 'HG', fichier 'HG' dans le fichier 1. 2 pages traitées pour la base de données 'HG', fichier 'HG_log' dans le fichier 1. BACKUP DATABASE a traité avec succès 101394 pages en 5.775*secondes (137.165*Mo/s).
8-)

15/09/2018, 05h20
Feliperennes
Citation:

Envoyé par nico84

Il y a une erreur de syntaxe c'est 'MaBDD' et non ’MaBDD’ (apostrophe et non je sais pas quoi)

C'était bien ça :)
Lorsque j'utilise ce code ça fonctionne parfaitement :
Code:

1 2 sqlcmd -S MON-PC -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\Temp\', @databaseName='MaBDD', @backupType='F'"
Dans l'hypothèse où je voudrais enregistrer les fichiers backup sur une autre machine de mon réseau nommée par exemple SERVEUR sur le disque qui s'appelle DATA et dans un dossier nommé Temp, qu'elle est la syntaxe ? J'ai essayé ce code mais pas de backup créé
Code:

1 2 sqlcmd -S MON-PC -E -Q "EXEC sp_BackupDatabases @backupLocation='\\SERVEUR\DATA\Temp\', @databaseName='MaBDD', @backupType='F'
Par contre si je crée un lecteur réseau par exemple E: avec comme chemin d'accès SERVEUR\DATA ça fonctionne
15/09/2018, 20h27
nico84

Citation:

Envoyé par Feliperennes

C'était bien ça :)

Tant mieux, par contre c'est probablement possible de faire plus simple si on en croit les autres contributeurs !
Pour ma part je préfère que chaque sauvegarde porte un nom différent et cette procédure sert aussi à ça, par contre cela oblige à faire du ménage régulièrement...

Citation:

Envoyé par Feliperennes

Dans l'hypothèse où je voudrais enregistrer les fichiers backup sur une autre machine de mon réseau nommée par exemple SERVEUR sur le disque qui s'appelle DATA et dans un dossier nommé Temp, qu'elle est la syntaxe ?

C'est du windows pur et dur, sur mon réseau une syntaxe du type \\srv-hg-ad\serveur2\planet\tmp\ fonctionne mais il faut que l'utilisateur soit autorisé en écriture sur le dossier...
16/09/2018, 05h54
Feliperennes

Merci pour tout Nico84 :)
J'ai bien avancé dans mon projet et dans l'apprentissage ...
18/01/2019, 12h59
saveCreator

effectivement sqlcmd -S MON-PC -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\Temp\', @databaseName=’MaBDD’, @backupType='F'" ca marche très bien

sinon il existe des logiciels comme savecreator très simple d'utilisation qui peuvent planifier des saves en SQL :)