Automatic SQL Server Backup

Automatic SQL Server Backup

I am running Windows Server 2008 with MS SQL Server and put togther this HOW-TO about how to backup a database automatically. I figured I would share it with you:

I am using Microsoft SQL Server 2008 Express but you should be able to use these instructions for all versions of Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

CREATE A SQL FILE and save it as "backup.sql"

declare @filepath varchar(255)
declare @filename varchar(255)
declare @file varchar(510)

set @filepath='D:\Praticle Test\'
declare @date varchar(8)
set @date=CONVERT(varchar(8), GETDATE(), 112)
set @filename= '-Backup.bak'
set @file=@filepath+ @date + @filename

Backup Database EHSolution To Disk = @file

CREATE A BATCH FILE With following content


sqlcmd -E -S pserver -i D:\backup.sql

SET BATCH FILE TO RUN IN WINDOWS TASK SCHEDULER

Start -> Run.

CODE

schtasks /create /sc Daily /st 03:30:00 /tn "MyTask" /tr "cmd /c D:\Backup.bat"