Today, I helped my friend set up an automatic SQL 2005 backup. I couldn't even set it up. When I added the maintenance plan, I reported the generation plan error. I had to manually write a task and record it here.
It is mainly used to obtain the location of the last backup record from the relevant system table in MSDB, that is, the number of existing backup sets in this backup set. If there are more than 7 backup sets, all backups are cleared and backed up again, otherwise, differential backup is performed.
If you want to implement weekly backup, monthly backup, and so on, the Code is the same, just change the task execution time.
- Use [www-bpjd]
- Declare @ cc as int
- Set @ cc = (select top 1 position from MSDB. DBO. backupset where media_set_id in (
- Select media_set_id from MSDB. DBO. backupmediafamily where logical_device_name = 'daily backup'
- ) Order by backup_set_id DESC)
- -- Select @ CC
- If isnull (@ CC, 0)> 7
- Backup database [www-bpjd] to daily backup with init
- Else
- Backup database [www-bpjd] to daily backup with differential