SQL Server 2005 /2008 Express Edition Backup automation

SQL Database Backup Script

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)   select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') while @IDENT is not null begin     SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT /*Change disk location here as required*/     SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQLBACKUP\Backup\'+@DBNAME+'.BAK''WITH INIT' PRINT @SQL     EXEC (@SQL)     select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') end

 

DOS Batch files to backup the databases

@echo off c: if not exist c:\SQLBackup\Logs MD C:\SQLbackup\Logs if not exist c:\SQLBackup\Backup MD C:\SQLbackup\Backup cd c:\SQLBackup Echo Backup Started >> c:\SQLBackup\Logs\SQLBAckup.log Date /T > c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log Echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log isql -S (local)\Express -E -i c:\sqlbackup\backup.sql >> c:\SQLBackup\Logs\SQLBAckup.log echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log echo. Date /T >> c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log echo Backup Finished >> c:\SQLBackup\Logs\SQLBAckup.log echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

 

Folders will look like,

 
Schedule it on Windows Task Scheduler

That's it the SQL Server 2005 / 2008 Express Edition database backup is automated now!