数据是企业的重要信息,可能也是商业机密.所以我们一定要及时的作好备份,保重数据的安全.
下面是设计了一个存储过程来备份数据.如果要实现自动的备份你可以执行一个任务来自动的处理.
CREATE procedure AutoBackupDataBase
--WITH ENCRYPTION 加密
As
Begin
declare @Str varchar(8)
declare @Str_d varchar(8)
declare @Str_DB varchar(100)
declare @Str_DB_d varchar(100)
declare @Str_LOG varchar(50)
Declare @StrPath varchar(50)
declare @Name sysname
set @Str=convert(char(10),getdate(),112)
set @Str_d=convert(char(10),getdate()-7,112)
set @StrPath = 'E:\BackupData\EveryDayBackup\'
declare DBName cursor for select Name from master.dbo.sysdatabases where name='DG_DB' or name='HKbomdb'
open DBName
FETCH NEXT FROM DBName into @Name
WHILE @@FETCH_STATUS = 0
BEGIN
--删除7天以前的备份
set @Str_DB_d ='del '+@StrPath+@Name+'_DB'+@Str_d
print @Str_DB_d
exec master..xp_cmdshell @str_db_d
set @Str_DB =@StrPath+@Name+'_DB'+@Str
--set @Str_LOG =@StrPath+@Name+'_LOG'+@Str
BACKUP DATABASE @Name TO DISK = @Str_DB WITH NOINIT , NOUNLOAD , NAME = N'所有数据库备份', NOSKIP , STATS = 10, NOFORMAT
--BACKUP LOG bdm TO DISK = @Str_LOG WITH NOINIT , NOUNLOAD , NAME = N'所有数据库日志备份', NOSKIP , STATS = 10, NOFORMAT, NO_TRUNCATE
FETCH NEXT FROM DBName INTO @Name
END
CLOSE DBName
DEALLOCATE DBName
endposted on 2006-01-11 11:11
Harryson 阅读(802)
评论(0) 编辑 收藏 所属分类:
SqlServer