建一个作业,每晚执行就好了,
-- 执行备份
DECLARE @strPath NVARCHAR(200)
DECLARE @name SYSNAME
DECLARE userDB CURSOR FOR select name from master.dbo.sysdatabases WHERE dbid>6 order by dbid desc
OPEN userDB
FETCH NEXT FROM userDB INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @strPath = CONVERT(NVARCHAR(19), GETDATE(), 120)
SET @strPath = REPLACE(@strPath, ':', '.')
SET @strPath = 'D:\' + @name + @strPath + '.bak'
BACKUP DATABASE @name TO DISK = @strPath WITH NOINIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,COMPRESSION,BUFFERCOUNT = 50
FETCH NEXT FROM userDB INTO @name
END
CLOSE userDB
DEALLOCATE userDB
取消
评论