Search This Blog

Wednesday, October 02, 2013

Backup all custom MSSQL databases


:: Set variables
set backuplocation="\\fileserver\files\Backup"
set mainsqlbase=SQLSERVER\MSSMLBIZ
set DBList=%temp%\SQLDBList.txt

:: Delete backups older than 7 days
forfiles -p %backuplocation% -s -m *.bak -d -7 -c "cmd /c del @PATH"

:: Get date in format YYYY-MM-DD (assuming the locale is US)
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ("%DATE%") DO SET NowDate=%%D-%%B-%%C

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

:: Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S %mainsqlbase% -Q "BACKUP DATABASE [%%I] TO DISK = N'%backuplocation%\%NowDate%_%%I.bak' WITH NOFORMAT, NOINIT,  NAME = N'MSSmallBusiness-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

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


Yes, it can be done directly from the SQL itself, but it's funnier to write a script  :)

No comments:

Post a Comment

Blog Archive