Today I got a task to backup all databases on our Microsoft SQL 2005 running on Windows 2008. The solution also needed to satisfy two conditions:
- must always backup all databases even if we add new one's
- must remove backup files older than 3 days
So I started Googling for solutions. At the end I combined different solutions into script below. This script can be used on Windows server 2003 and 2008 with MS SQL 2005 and 2008 with slight difference in one line:
SQL 2008:
sqlcmd -E -S "%ServerName%" -h-1 -W -Q "SET NoCount ON;SELECT NAME FROM sys.sysdatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > %DBList%
SQL 2005
sqlcmd -E -S "%ServerName%" -h-1 -W -Q "SET NoCount ON;SELECT NAME FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > %DBList%
And what this script does?
- Gets system date for later use in backup file names.
- Saves names of all databases in temporary file.
- Removes backup files older than 3 days.
- Backup each database, prepending the server name and date to the filename.
- Clean up the temp file.
Warning: Text marked as this should be customized according to your specifications!
***************************************************
REM @ECHO OFF
SETLOCAL
REM: get computer name ad write it into variable
set ServerName=%computername%
REM: Get System Date to be used for DB backup file namesSETLOCAL ENABLEEXTENSIONS
if "%date%A" LSS "A" (set toks=1-3) else (set toks=2-4)
for /f "tokens=2-4 delims=(-)" %%a in ('echo:^|date') do (
for /f "tokens=%toks% delims=.-/ " %%i in ('date/t') do (
set '%%a'=%%i
set '%%b'=%%j
set '%%c'=%%k))
if %'yy'% LSS 100 set 'yy'=20%'yy'%
set Today=%'yy'%-%'mm'%-%'dd'%
ENDLOCAL & SET v_year=%'yy'%& SET v_month=%'mm'%& SET v_day=%'dd'%
REM Build a list of databases to backup
SET DBList=C:\BackupSQL\SQLDBList.txt
echo %DBList%
sqlcmd -E -S "%ServerName%" -h-1 -W -Q "SET NoCount ON;SELECT NAME FROM sys.sysdatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > %DBList%
REM Remove backup files older than 3 days
REM /d -3 defines how old must be files before they are deleted
IF EXIST "%DBList%" forfiles /p C:\BackupSQL /s /m * /d -3 /c "cmd /c del @file"
REM Backup each database, prepending the servername and date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S "%ServerName%" -Q "BACKUP DATABASE [%%I] TO Disk='C:\BackupSQL\Backup_%ServerName%_%v_year%_%v_month%_%v_day%_%%I.bak'"
ECHO
)
REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"
ENDLOCAL
***************************************************
Now just copy/paste this script into BackupSQL.bat file and run it from Scheduled Tasks.
I also suggest to all that they test restore of their databases because you never know if it's OK until you try :)
This is it for today. Have fun!
