SQLserver -- 备份脚本

　　

--全量备份
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