备份数据库SQL

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


-- execute AutoBackupDataBase
ALTER    procedure AutoBackupDataBase
--WITH ENCRYPTION  加密
As
Begin
declare @curDate varchar(12)
declare @before7Date varchar(8)

declare @dbSavePath varchar(100)
declare @dbLogSavePath varchar(100)
declare @dbDeletPath varchar(100)
declare @dbLogDeletPath varchar(100)

Declare @saveMainPath varchar(50)
declare @dbPath varchar(125)
declare @Name sysname

declare @trunLog varchar(512)
--select convert(char(10),getdate(),112)
--select convert(char(10),getdate()-3,112)

--select convert(char(20),getdate(),120)
--select getdate()

set @curDate = convert(char(8),getdate(),112)+right('00' + (cast(datepart(hh,getdate()) as varchar(2))),2)+right('00' + (cast(datepart(mi,getdate()) as varchar(2))),2)
set @before7Date=convert(char(10),getdate()-7,112)
set @saveMainPath =  'D:\database\'

--select * from master.dbo.sysdatabases

declare DBName cursor for select Name from master.dbo.sysdatabases where name !='tempdb'
open DBName
FETCH NEXT FROM DBName into @Name
WHILE @@FETCH_STATUS = 0
    BEGIN
	set @dbPath = @saveMainPath + @Name+'\'+@Name
        set @dbDeletPath ='del  '+@dbPath+'_DB_'+@before7Date+'*.bak' 
	set @dbLogDeletPath ='del  '+@dbPath+'_tlog_'+@before7Date+'*.trn' 

        print @dbDeletPath
	print @dbLogDeletPath

        exec master..xp_cmdshell @dbDeletPath   
	exec master..xp_cmdshell @dbLogDeletPath   
     
        set @dbSavePath =@dbPath+'_DB_'+@curDate+'.bak' 
	print @dbSavePath
        --set @Str_LOG =@StrPath+@Name+'_LOG'+@Str
        BACKUP DATABASE @Name TO  DISK = @dbSavePath 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 
	
	/*if @Name != 'master' and @Name != 'model' and @Name != 'msdb'
	    begin
		set @dbLogSavePath =@dbPath+'_tlog_'+@curDate+'.trn'
		--set @trunLog = 'USE ' + @Name +' BACKUP LOG '+@Name+' to disk = '''+ @dbLogSavePath + ''' WITH NOINIT ,  NOUNLOAD ,  NAME = N'' 数据库日志备份'',  NOSKIP ,  STATS = 10,  NOFORMAT,  NO_TRUNCATE  DBCC SHRINKFILE ( '+@Name + '_Log)'
		if @Name = 'GPT2006' or @Name = 'TRAINING' or @Name = 'WTD2005' or @Name = 'DevArea'
		    begin
			set @trunLog = 'USE ' + @Name +' BACKUP LOG '+@Name+' to disk = '''+ @dbLogSavePath + ''' WITH NOINIT ,  NOUNLOAD ,  NAME = N'' 数据库日志备份'',  NOSKIP ,  STATS = 10,  NOFORMAT,  NO_TRUNCATE  DBCC SHRINKFILE ( ajstest_log)'	
                    end
		else
		    begin
			--set @trunLog = 'USE ' + @Name +' BACKUP LOG '+@Name+' to disk = '''+ @dbLogSavePath + ''' WITH NOINIT ,  NOUNLOAD ,  NAME = N'' 数据库日志备份'',  NOSKIP ,  STATS = 10,  NOFORMAT,  NO_TRUNCATE  DBCC SHRINKFILE ( DevelopmentArea_Log)'	
			set @trunLog = 'USE ' + @Name +' BACKUP LOG '+@Name+' to disk = '''+ @dbLogSavePath + ''' WITH NOINIT ,  NOUNLOAD ,  NAME = N'' 数据库日志备份'',  NOSKIP ,  STATS = 10,  NOFORMAT,  NO_TRUNCATE  DBCC SHRINKFILE ( '+@Name + '_Log)'	
		    end
		print @trunLog
		execute (@trunLog)
	    end
	*/
	--set @dbLogSavePath =@dbPath+'_tlog_'+@curDate+'.trn'
	--set @trunLog = 'USE ' + @Name +' BACKUP LOG '+@Name+' to disk = '''+ @dbLogSavePath + ''' WITH NOINIT ,  NOUNLOAD ,  NAME = N'' 数据库日志备份'',  NOSKIP ,  STATS = 10,  NOFORMAT,  NO_TRUNCATE  DBCC SHRINKFILE ( '+@Name + '_Log)'
	--print @trunLog
	--execute (@trunLog)
        FETCH NEXT FROM DBName INTO @Name
    END
CLOSE DBName
DEALLOCATE DBName

end  



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER   procedure AutoBackupDataBaseTlog
--WITH ENCRYPTION  加密
As
Begin
declare @curDate varchar(12)
declare @dbLogSavePath varchar(100)

Declare @saveMainPath varchar(50)
declare @dbPath varchar(125)
declare @Name sysname

declare @trunLog varchar(100)

set @curDate = convert(char(8),getdate(),112)+right('00' + (cast(datepart(hh,getdate()) as varchar(2))),2)+right('00' + (cast(datepart(mi,getdate()) as varchar(2))),2)
set @saveMainPath =  'D:\database\'

declare DBName cursor for select Name from master.dbo.sysdatabases where name != 'master' and name != 'tempdb'
open DBName
FETCH NEXT FROM DBName into @Name
WHILE @@FETCH_STATUS = 0
    BEGIN
	set @dbPath = @saveMainPath + @Name+'\'+@Name
        
        set @dbLogSavePath =@dbPath+'_tlog_'+@curDate+'.trn' 
	print @dbLogSavePath
        BACKUP LOG @Name TO  DISK = @dbLogSavePath WITH  NOINIT ,  NOUNLOAD ,  NAME = N' 数据库日志备份',  NOSKIP ,  STATS = 10,  NOFORMAT,  NO_TRUNCATE 
	
	FETCH NEXT FROM DBName INTO @Name
    END
CLOSE DBName
DEALLOCATE DBName

end  


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO