Script de sauvegarde des bases SQL Server/Express en ligne de commande

Si vous avez besoin de sauvegarder des bases SQL Express ou SQL Server en dehors de SQL Server Management Studio (SSMS), vous pouvez utiliser ce script. A planifier pour un lancement journaliser.

A configurer :

• Instance à sauvegarder (variable SRV)
• Dossier de sauvegarde de destination (variable BCKFOLDER)
• Nombre de jour de rétention (copie à conserver localement) (variable RETENTION)

@ECHO OFF
SETLOCAL

SET SRV=localhost\EBP
SET BCKFOLDER=C:\Backup\SQL
SET RETENTION=5

FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%A-%%B-%%C

echo Build a list of databases to backup
SET DBList=%SystemDrive%SQLDBList.txt
SqlCmd -E -S %SRV% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

echo Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S %SRV% -Q "BACKUP DATABASE [%%I] TO Disk='%BCKFOLDER%\%NowDate%_%%I.bak'"
ECHO.
)

echo Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

echo Clean old backup files
ForFiles /p "%BCKFOLDER%" /s /d -%RETENTION% /c "cmd /c del @file"

ENDLOCAL