sql server备份数据库

wcdz

      数据是企业的重要信息,可能也是商业机密.所以我们一定要及时的作好备份,保重数据的安全.
下面是设计了一个存储过程来备份数据.如果要实现自动的备份你可以执行一个任务来自动的处理.
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