SQL Server daily full backup cycled in one week

This article is about how to full backup the SQL Server 2005 database. this solution uses the SQL Server Agent to start a job with schedule to fullback it automatically. it always have 7. bak files in recent 7days. the schedule is 2: 00 am every day. the detail is as followed:
1. Connect to the SQL Server Management Studio, find "SQL Server Agent ";

2. Add a new job;

3, find "step", then add a new step with SQL transaction.

4, find "schedules", set it to meet your requirement. I choose all days in one week here.

5. click "OK", and you can start the job for test.

 

Declare @ pWeekDay varchar (3)
Declare @ SQL varchar (200)
Select @ pWeekDay = left (datename (weekday, getdate (), 3)
Set @ SQL =''
Set @ SQL = @ SQL + 'backup DATABASE DBName TO DISK = n' S: \ DBBackup \ dbname' + @ pWeekDay
Set @ SQL = @ SQL + 'full. bak ''with noformat, INIT, NAME = n' DBName-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
Exec (@ SQL)