Schedule backup in SQL Server 2005

Anyone knows how to schedule backup of database in SQL server 2005?

You can set up a job in SQL Server Agent that does the backup, and schedule the job there.

/Kenneth

|||

Hi,

or you could use the Maintenance Plan Wizard, which have some maintenance tasks for backup.

|||

if it is SQL Server 2005 Express , you can not schedule it from SQL Server .... you will have to use Windows Scheduler and SQLCMD option

Madhu

|||Can anyone tell how to do it practicallY?|||

(a) Create a procedure

Create Proc spBackupDatabase As

backup database Somedatabase to Disk='D:\somebakfile.bak' with init

(b) Create a batch file to execute this sp using sql scripting -- Open a notepad document and type the following script and save it as .bat extension

SQLCMD -q"Exec spBackupDatabase " -Uusername -Ppassword -Sservername -ddatabasename

save the above script as .bat file

(c) next step is to schedule this script using windows scheduller

program -- Accessories -- System Tool -- Schdueled Task

Create a task and select this file and schedule it accordingly

basically, u r using Storedprocdure with Backup script, SQLCMD and Windows schduller

Madhu

|||Hi, I run backups but now need to learn how to shrink log and data files with backup. I like to use Maintenance Plan Wizard. When I see "shrink DB when it grows beyond 50 MB - and Amt of free space after shrink 20% ~~ My database is 7703.25MB now. Should I put 8000MB in "...grows beyond"? and 10% or 20%? Also, is it best to release the space to the database or to the OS?|||

From windows schedule you can do.

Create a SP's
EXample:

backup database DBNAME to Disk='F:\Filename.bak' with init
NExt

Open Note pad copy and past
SQLCMD -q"Exec spBackupDatabase " -Uusername -Ppassword -Sservername -ddatabasename

Now Save aS filename.bat

schedule this script using windows scheduller
Thanks
Faiz Farazi,
Daudkandi,Comilla,Bangladesh
http://www.databasetimes.net/

|||dont forget to quit out your SP otherwise the batch will hang