sql server备份数据库
数据是企业的重要信息,可能也是商业机密.所以我们一定要及时的作好备份,保重数据的安全.下面是设计了一个存储过程来备份数据.如果要实现自动的备份你可以执行一个任务来自动的处理.
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifCREATE procedure AutoBackupDataBase
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif--WITH ENCRYPTION 加密
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifAs
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifBegin
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare @Str varchar(8)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare @Str_d varchar(8)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare @Str_DB varchar(100)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare @Str_DB_d varchar(100)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare @Str_LOG varchar(50)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifDeclare @StrPath varchar(50)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare @Name sysname
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifset @Str=convert(char(10),getdate(),112)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifset @Str_d=convert(char(10),getdate()-7,112)
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifset @StrPath = 'E:\BackupData\EveryDayBackup\'
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifdeclare DBName cursor for select Name from master.dbo.sysdatabases where name='DG_DB' or name='HKbomdb'
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifopen DBName
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifFETCH NEXT FROM DBName into @Name
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifWHILE @@FETCH_STATUS = 0
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif BEGIN
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif --删除7天以前的备份
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif set @Str_DB_d ='del '+@StrPath+@Name+'_DB'+@Str_d
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif print @Str_DB_d
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif exec master..xp_cmdshell @str_db_d
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif set @Str_DB =@StrPath+@Name+'_DB'+@Str
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif --set @Str_LOG =@StrPath+@Name+'_LOG'+@Str
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif BACKUP DATABASE @Name TO DISK = @Str_DB WITH NOINIT , NOUNLOAD , NAME = N'所有数据库备份', NOSKIP , STATS = 10, NOFORMAT
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif --BACKUP LOG bdm TO DISK = @Str_LOG WITH NOINIT , NOUNLOAD , NAME = N'所有数据库日志备份', NOSKIP , STATS = 10, NOFORMAT, NO_TRUNCATE
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif FETCH NEXT FROM DBName INTO @Name
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif END
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifCLOSE DBName
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifDEALLOCATE DBName
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gif
http://hgq0011.iyunv.com/Images/OutliningIndicators/None.gifend
页:
[1]