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
备份数据库SQL
最新推荐文章于 2024-06-21 21:45:00 发布