SQLserver -- 备份脚本
2023-09-11 14:16:17 时间
--全量备份
exec master.dbo.fullbackup 'D:\DBBak\Bod\','Bod','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_APP_IntelliP\','CJML_APP_IntelliP','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_Auto\','CJML_Auto','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_B2C\','CJML_B2C','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_BaseCenter\','CJML_BaseCenter','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_Car\','CJML_Car','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_CloudShop\','CJML_CloudShop','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_CPP\','CJML_CPP','bak'
go
--exec master.dbo.fullbackup 'D:\DBBak\CJML_DataStatistics\','CJML_DataStatistics','bak'
--go
exec master.dbo.fullbackup 'D:\DBBak\CJML_SALE_CRM\','CJML_SALE_CRM','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_Verify\','CJML_Verify','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CJML_VIN\','CJML_VIN','bak'
go
exec master.dbo.fullbackup 'D:\DBBak\CRM_CloupShop\','CRM_CloupShop','bak'
go
--差异备份
exec master.dbo.diffBackup 'D:\DBBak\Bod\','Bod','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_APP_IntelliP\','CJML_APP_IntelliP','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_Auto\','CJML_Auto','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_B2C\','CJML_B2C','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_BaseCenter\','CJML_BaseCenter','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_Car\','CJML_Car','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_CloudShop\','CJML_CloudShop','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_CPP\','CJML_CPP','diff'
go
--exec master.dbo.diffBackup 'D:\DBBak\CJML_DataStatistics\','CJML_DataStatistics','diff'
--go
exec master.dbo.diffBackup 'D:\DBBak\CJML_SALE_CRM\','CJML_SALE_CRM','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_Verify\','CJML_Verify','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CJML_VIN\','CJML_VIN','diff'
go
exec master.dbo.diffBackup 'D:\DBBak\CRM_CloupShop\','CRM_CloupShop','diff'
go
--日志备份
exec master.dbo.LogBackup 'D:\DBBak\Bod\','Bod','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_APP_IntelliP\','CJML_APP_IntelliP','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_Auto\','CJML_Auto','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_B2C\','CJML_B2C','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_BaseCenter\','CJML_BaseCenter','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_Car\','CJML_Car','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_CloudShop\','CJML_CloudShop','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_CPP\','CJML_CPP','trn'
go
--exec master.dbo.LogBackup 'D:\DBBak\CJML_DataStatistics\','CJML_DataStatistics','trn'
--go
exec master.dbo.LogBackup 'D:\DBBak\CJML_SALE_CRM\','CJML_SALE_CRM','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_Verify\','CJML_Verify','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CJML_VIN\','CJML_VIN','trn'
go
exec master.dbo.LogBackup 'D:\DBBak\CRM_CloupShop\','CRM_CloupShop','trn'
go
定义全量备份存储procedure
USE [master]
GO
/****** Object: StoredProcedure [dbo].[fullbackup] Script Date: 2020-07-30 20:40:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[fullbackup]
(
@backupPath varchar(500),
@databasename varchar(100),
@backuptype varchar(100)
)
as
declare @fullpath varchar(1000)
set @fullpath = @backupPath+@databasename+'_'+@backuptype+'_'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+ '.bak'
backup database @databasename to disk=@fullpath with
compression,buffercount=20,maxtransfersize=2097152,retaindays=15,noformat,noinit,name=N'完整备份',skip,norewind,nounload,stats=10
return
GO
定义日志备份存储procedure
USE [master]
GO
/****** Object: StoredProcedure [dbo].[LogBackup] Script Date: 2020-07-30 20:40:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[LogBackup]
(
@backupPath varchar(500),
@databasename varchar(100),
@backuptype varchar(100)
)
as
declare @fullpath varchar(1000)
set @FullPath = @backuppath+@databasename+'_'+@backuptype+'_'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+ '.trn'
backup log @databasename to disk=@FullPath with
compression,buffercount = 20, maxtransfersize = 2097152 ,retaindays=8,noformat,noinit, NAME=N'事务日志备份',skip,norewind,nounload,stats=10
return
GO
相关文章
- SqlServer 2019 On Linux
- SqlServer备份还原 出现操作系统错误 5(拒绝访问) 的解决方案
- SqlServer中游标中的例子!
- SQLServer中 IsNull,Oracle中Nvl 对空字符,NULL值比较的区别
- 在SqlServer 中解析JSON数据
- com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 ‘DUAL‘ 无效 | Druid双数据源MySQL+SQL server
- sqlserver的触发器练习实例
- 详解sqlserver查询表索引
- sqlserver添加字段 如何更新EF实体模型?
- SqlServer主键
- 怎么样快速完整备份和压缩 很大的 sqlserver 1TB 数据库 -摘自网络
- How to select a OptionSet on ms sqlserver database for Microsoft Dynamics CRM
- SqlServer with递归查询的使用
- ### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。(sql少一个逗号)【??】