MySQL and MS SQL backup scripts for Windows

backup-databases.cmd

@echo off
cd "%~dp0"
backup-mysql.cmd
backup-mssql.cmd

backup-mssql.cmd

@echo off
setlocal

set SelfPath=%~dp0
set BinPath=c:\bin
set SQLBinPath=%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn
set BackupPath=c:\backup\nightly\mssql
set MaxBackups=30
set TimeStamp=%date:~10,4%%date:~4,2%%date:~7,2%
set BackupFile=backup-%TimeStamp%

echo Cycling SQL Server Error Log file ...
"%SQLBinPath%\osql" -S (local) -E -Q "exec [sp_cycle_errorlog]"
echo.

echo Backing Up Databases to %BackupPath%
del /q "%BackupPath%\*.bak"
"%SQLBinPath%\sqlcmd" -S (local) -e -i "%SelfPath%backup.sql" -o "%SelfPath%backup.log"
echo.

echo Archiving Backups ...
if exist "%BackupPath%\backup.zip" del "%BackupPath%\backup.zip"
"%BinPath%\7zip\7za" a -tzip "%BackupPath%\backup.zip" "%BackupPath%\*.bak"
del /q "%BackupPath%\*.bak"
echo.

if exist "%BackupPath%\%BackupFile%.zip" del "%BackupPath%\%BackupFile%.zip"
ren "%BackupPath%\backup.zip" "%BackupFile%.zip"

echo Cycling Backups ...
set FileCount=0
for /f %%i in ('dir "%BackupPath%\*.zip" /B /O:-N') do call :cycle "%BackupPath%\%%i"
echo.

goto:eof


:cycle
set /A FileCount=%FileCount%+1
if %FileCount% GTR %MaxBackups% (
  echo Deleting %~nx1
  del "%~f1"
)
goto:eof

backup-mysql.cmd

@echo off
setlocal

set BackupPath=c:\backup\nightly\mysql
set BinPath=c:\bin
set SqlBinPath=%ProgramFiles%\MySQL\MySQL Server 5.1\bin
set SqlDataPath=%AllUsersProfile%\Application Data\MySQL\MySQL Server 5.1\data
set DBUser=root
set DBPass=password
set MaxBackups=30

set TimeStamp=%date:~10,4%%date:~4,2%%date:~7,2%

del /q "%BackupPath%\*.gz"
for /D %%f in ("%SqlDataPath%\*") do call :backup "%%f"
if exist "%BackupPath%\backup.zip" del "%BackupPath%\backup.zip"
"%BinPath%\7zip\7za" a -tzip "%BackupPath%\backup.zip" -mx0 "%BackupPath%\*.gz"
echo.
del /q "%BackupPath%\*.gz"

::ftp -i -s:upload.txt ftp.adrive.com
if exist "%BackupPath%\backup-%TimeStamp%.zip" del "%BackupPath%\backup-%TimeStamp%.zip"
ren "%BackupPath%\backup.zip" backup-%TimeStamp%.zip

echo Cycling Backups ...
set FileCount=0
for /f %%i in ('dir "%BackupPath%\*.zip" /B /O:-N') do call :cycle "%BackupPath%\%%i"
echo.

goto:eof


:backup

set DBName=%~n1
set DBName=%DBName:@002d=-%

echo Backing Up Database %DBName%

"%SqlBinPath%\mysqldump" --user=%DBUser% --password=%DBPass% --no-create-db --skip-extended-insert --databases %DBName% | "%BinPath%\7zip\7za" a "%BackupPath%\%DBName%.gz" -tgzip -si%DBName%.sql
echo.

goto:eof


:cycle
set /A FileCount=%FileCount%+1
if %FileCount% GTR %MaxBackups% (
  echo Deleting %~nx1
  del "%~f1"
)
goto:eof

backup.sql

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0 

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1 

        -- Not master, tempdb or model
    and db_name(s_mf.database_id) not in ('Master','model','msdb','tempdb')
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)    
    set @DBFileName = replace(replace(@DBName,':','_'),'\','_')

    exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''d:\backup\nightly\mssql\' + 
        @DBFileName + '.bak'' WITH NOFORMAT, INIT,  NAME = N''' + 
        @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')

  -- Truncate the log by changing the database recovery model to SIMPLE
    exec ('ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE')
  
	-- Shrink the truncated log file
    exec ('USE [' + @DBName + ']; DBCC SHRINKFILE ([' + @DBName + '_log], 1)')
	
	-- Reset the database recovery model
    exec ('ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL')

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR