以下放在作业里做调度,每天自动备份和自动删除三天前的备份。
这是偶用的方法:
1
declare @data_3ago nvarchar(
50
)
2 declare @cmd varchar( 50 )
3
4 set @data_3ago = ' e:/data/ ' + convert(varchar( 10 ),getdate() - 3 , 112 )
5 set @cmd = ' del ' + @data_3ago
6 exec master..xp_cmdshell @cmd
7 go
8
9 declare @data nvarchar( 50 )
10 set @data = ' e:/data/ ' + convert(varchar( 10 ),getdate(), 112 )
11 BACKUP DATABASE job TO DISK = @data
12 with init
2 declare @cmd varchar( 50 )
3
4 set @data_3ago = ' e:/data/ ' + convert(varchar( 10 ),getdate() - 3 , 112 )
5 set @cmd = ' del ' + @data_3ago
6 exec master..xp_cmdshell @cmd
7 go
8
9 declare @data nvarchar( 50 )
10 set @data = ' e:/data/ ' + convert(varchar( 10 ),getdate(), 112 )
11 BACKUP DATABASE job TO DISK = @data
12 with init