sql数据自动完全备份数据库实体

最新推荐文章于 2024-08-04 10:22:19 发布

置顶 kuyz

最新推荐文章于 2024-08-04 10:22:19 发布

阅读量700

点赞数 1

分类专栏： sql 文章标签： sql 自动完全备份数据库实体

本文链接：https://blog.csdn.net/kuyz1/article/details/48287861

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

-- =============================================
-- Author:    <广州_小海>
-- Blog:        1299256571@qq.com
-- Create date: <2015/09/08>
-- Description: <批量备份数据库>
-- =============================================
DECLARE
   @FileName VARCHAR(200),
   @CurrentTime VARCHAR(50),
   @DBName VARCHAR(100),
   @SQL VARCHAR(1000)

SET @CurrentTime = convert(varchar,getdate(),112)

DECLARE CurDBName CURSOR FOR
SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--Execute Backup
SET @FileName = 'E:\DBBackup\' + @DBName + '_' + @CurrentTime
SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
   ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
EXEC(@SQL)

--Get Next DataBase
FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName
go
-- =============================================

-- Author: <广州_小海>

-- Blog: 1299256571@qq.com

-- Create date: <2015/09/08>
-- Description: <批量删除昨天备份数据库>
-- =============================================
DECLARE
   @FileName VARCHAR(200),
   @CurrentTime VARCHAR(50),
   @DBName VARCHAR(100),
   @SQL VARCHAR(1000)

SET @CurrentTime = convert(varchar,dateadd(day,-1,getdate()),112)
--SET @CurrentTime=convert(varchar,getdate(),112)
DECLARE CurDBName CURSOR FOR
SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--Execute Backup 删除昨天的备份数据
SET @FileName = 'E:\DBBackup\' + @DBName + '_' + @CurrentTime+ '.bak'
  set @SQL='del '+@FileName +' /F'
  exec master..xp_cmdshell @SQL
  print @SQL
--Get Next DataBase
FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName

两部分sql脚本go 上一部分是备份每天的数据，下一部分是删除昨天的备份数据。
新建一个作业把sql脚本会