SQL Server数据库备份、差异备份、日志备份脚本

​1，sp脚本

USE [master]GO/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 2021/10/22 10:04:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Parameter1: 备份类型 F=全部, D=差异, L=日志ALTER PROCEDURE [dbo].[sp_BackupDatabase]@backupType CHAR(1)ASBEGINSET NOCOUNT ON;declare @filepath_backup varchar(100)declare @dateTime varchar(30),@del_time_stamp varchar(50)DECLARE @sqlCommand NVARCHAR(1000)DECLARE @sourcePath nvarchar(max) DECLARE @destionationPath nvarchar(max) DECLARE @cmdStr nvarchar(max) ---创建数据库对应文件夹EXECUTE master.dbo.xp_create_subdir N'C:\Database_BackUp\Full\'EXECUTE master.dbo.xp_create_subdir N'C:\Database_BackUp\Difference\'EXECUTE master.dbo.xp_create_subdir N'C:\Database_BackUp\Log_Bak\'IF @backupType = 'F'set @filepath_backup='C:\Database_BackUp\Full\'IF @backupType = 'D'set @filepath_backup='C:\Database_BackUp\Difference\'IF @backupType = 'L'set @filepath_backup='C:\Database_BackUp\Log_Bak\'SET ANSI_WARNINGS OFFSET @dateTime = replace(convert(varchar,current_timestamp, 112)+'_'+convert(varchar,current_timestamp, 108),':','')----删除超过14天的备份文件DECLARE @delete_time datetimeset @delete_time = getdate() -14EXECUTE master.dbo.xp_delete_file 0,N'C:\Database_BackUp',N'trn',@delete_time,1EXECUTE master.dbo.xp_delete_file 0,N'C:\Database_BackUp',N'bak',@delete_time,1SELECT @dateTime = replace(convert(varchar,current_timestamp, 112)+'_'+convert(varchar,current_timestamp, 108),':','')declare db_info cursor for SELECT NAME,recovery_model FROM MASTER.SYS.databases where state = 0 ---只处理online的数据库 and name in ('OperationSystem') ----填写需要备份的数据库declare @databaseName nvarchar(128) declare @recovery_model intOPEN db_info fetch next from db_info into @databaseName,@recovery_model while @@fetch_status=0 Begin ---recovery_model 1 : FULL 2 : BULK_LOGGED 3:SIMPLEIF @backupType = 'F' begin SET @sqlCommand = 'BACKUP DATABASE '+ @databaseName +' TO DISK = '''+ @filepath_backup + ''+ @databaseName +'_Full_'+@dateTime+'.BAK'' with STATS = 10, INIT, COMPRESSION, CHECKSUM ' set @sourcePath = @filepath_backup + ''+ @databaseName +'_Full_'+@dateTime+'.BAK' endIF @backupType = 'D' and @databaseName not in ('master','msdb','model')begin SET @sqlCommand = 'BACKUP DATABASE '+ @databaseName +' TO DISK = '''+ @filepath_backup + ''+ @databaseName + '_Diff_' + @dateTime + '.BAK '' WITH DIFFERENTIAL, STATS = 10, INIT, COMPRESSION' set @sourcePath = @filepath_backup + ''+ @databaseName +'_Diff_'+@dateTime+'.BAK' endIF @backupType = 'L' and @recovery_model <> 3 and @databaseName not in ('master','msdb','model')begin SET @sqlCommand = 'BACKUP LOG '+ @databaseName +' TO DISK = '''+ @filepath_backup + '' + @databaseName +'_Log_' + @dateTime + '.TRN'' with STATS = 10, INIT, COMPRESSION' set @sourcePath = @filepath_backup + ''+ @databaseName +'_Log_'+@dateTime+'.TRN' endprint @sqlCommandEXECUTE sp_executesql @sqlCommand set @destionationPath = REPLACE(@sourcePath,'C:\Database_BackUp','x:')/*exec sp_configure 'show advanced options', 1 --允许配置高级选项reconfigure --重新配置exec sp_configure 'xp_cmdshell', 1 --启用xp_cmdshellreconfigure --重新配置--配置共享路径用户名和密码exec master..xp_cmdshell 'net use x: \\Server-QC-DB2\Database_BackUp P@ssw0rd /user:Server-QC-DB2\dbbackuper'--exec sp_configure 'xp_cmdshell', 0 --执行完成后出于安全考虑可以将xp_cmdshell关闭*/set @cmdStr = 'exec master..xp_cmdshell ''copy '+@sourcePath+' '+@destionationPath+''''EXEC(@cmdStr)fetch next from db_info into @databaseName,@recovery_model End close db_info deallocate db_info PRINT '-- Backup completed successfully at '+convert(varchar, getdate(), 120) SET ANSI_WARNINGS ONEND

2，SQL agent任务指令：

[dbo].[sp_BackupDatabase] 'F'

3，别忘了每周agent任务，跑一下日志的收缩

USE OperationSystem;GO ALTER DATABASE OperationSystemSET RECOVERY SIMPLE; --设置简单恢复模式GODBCC SHRINKFILE (OperationSystem_log, 1);GOALTER DATABASE OperationSystemSET RECOVERY FULL; --恢复为原模式GO