MS-SQL 自動備份 刪除舊備份檔
'刪除 .bak檔 -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO -- Del .bak file of 3 days ago --
Declare @data_ago nvarchar(50)
Declare @cmd varchar(50)
set @data_ago =N'C:\SqlBackup\'+'XX'+convert(varchar,getdate()-1,112)+N'.bak'
set @cmd = 'del '+@data_ago
exec master..xp_cmdshell @cmd
go
'建立備份檔
Declare @backupName nvarchar(1000)
Set @backupName=N'C:\SqlBackup\'+'XX'+convert(varchar,getdate(),112)+N'.bak'
BACKUP DATABASE [intraware] TO DISK = @backupName WITH NOFORMAT,INIT,NAME=N'WeLink-Full Database Backup',SKIP,NOREWIND,NOUNLOAD,STATS=10
GO