Monday, January 11, 2010

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,

image 
Schedule it on Windows Task Scheduler

image

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

No comments: