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)