Voici différentes façons de faire des backups de bases de données SQL Serveur, et quelques scripts pour en obtenir les informations.

Par Sql Serveur Management Studio (SSMS)

  • Voici une vidéo qui explique comment faire un backup avec SSMS

Par des logiciel tiers

Il existe plusieurs logiciels qui permettent de faire des backups de bases de données sql serveur.Il y en a un que je trouve très intéressant c’est SQLBackupAndFTP  car il est très simple à configurer et à utiliser.Il a comme avantages :

  • de pouvoir stocker les backups sur plusieurs types de destinations: FTP , réseau, dropbox, skydrive, google drive, …
  • De ziper les backups
  • De les encrypter
  • d’envoyer un mail à la fin de la tache
  • De suivre l’historique
  • Il permet aussi de restaurer des backups

Au niveau des licences il possède une version gratuite et une version commerciale.

(Les certifiés Microsoft MCP ont droit à une licence gratuite de la version commerciale)

En voici quelques écrans

Ecran principale de configuration
Choix de la destination
Configuration de l’email
Configuration avancée
Monitoring

Par un script T-SQL (Transact SQL)

Voici un exemple de script T-SQL de base qui permet de faire un FULL backup d’1 base de données

BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'

Il faut donc préciser le nom de la base (AdventureWorks2012) et la l’unité de sauvegarde (Z:\SQLServerBackups\AdventureWorks2012.Bak)

Pour faire un backup de toutes les bases d’un serveur, il suffit de mettre ce script T-SQL dans une boule While ou utiliser un Curseur.

Exemples de scripts

http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

http://dbadiaries.com/implementing-a-simple-sql-server-backup-script
Vidéo: How to Backup SQL Server Database using SQL Script


Automatisation et planification de sauvegardes :

A partir de l’exemple précédent, il est possible d’automatiser des backups de bases de données avec la commande sqlcmd qui se trouve dans un fichier bat et qui peut être exécuté par le planification de tache de windows.

En voici les étapes:

  • Mettre le (ou les) scripts T-SQL de backup dans un fichier .sql
  • Créer un fichier .bat qui contient la commande sqlcmd

    Un exemple de ce fichier bat :
    sqlcmd -E -S .\sqlexpress -i E:\Production\Backup_Bdd\DataBaseScriptsBackUp.sql
    On lui précise l’instance du serveur sql ainsi que le chemin du fichier .sql

  • Planifier ce fichier .bat dans le planificateur de tache

Par un script PowerShell

Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database

Tous les détails : ici

Date du dernier backup

Pour connaitre la date du dernier backup effectué sur un serveur pour chacune de ses bases ou sur une base executer le script

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12),
MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

Historique des backup d’une base

SELECT
 TOP ( 30 )
s.database_name, m.physical_device_name,cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+' '+'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date)AS VARCHAR(4))
 +' '+'Seconds' TimeTaken, s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc, backup_finish_date

Remplacer @db_name par le nom de la base


Quelques liens :

http://technet.microsoft.com/fr-fr/library/ms187510.aspx

http://www.kodyaz.com/articles/how-to-backup-sql-database-command-line-sqlcmd-sql-batch-file.aspx

http://blog.sqlauthority.com

Voilà pour cet article sur comment faire des backups des bases de données SQL Serveur, un autre article sera publié prochainement sur la restauration des backups.