SQLserver -- 备份脚本

--完整备份(name还原bai显示的名du称)
backup database t to disk='E:\dataBak\log\bak_t_full.bak'
with noinit,name='bak_t_full'
--差异备份zhi
backup database t to disk='E:\dataBak\log\bak_t_differ.bak'
with differential,noinit,name='bak_t_differ'
--日志备份
backup log t to disk='E:\dataBak\log\bak_t_log.bak'
with noinit,name='bak_t_log'
--还原
RESTORE DATABASE 数据库dao名 FROM DISK = '物理路径(备份文件在磁盘的位置zhuan)'

备份和收缩

USE [MCCPS2A]
GO

DELETE FROM [MCCPS2A].[dbo].[Prt_AlarmRecord]
WHERE EndTime < '2020-11-30 23:59:59'
GO

DELETE FROM [MCCPS2A].[dbo].[Sys_OperationLog]
WHERE LogTime < '2020-11-30 23:59:59'
GO

DELETE FROM [MCCPS2A].[dbo].[Prt_BuildingProduction]
WHERE StartTime < '2020-11-30 23:59:59'
GO
BACKUP database MCCPS2A TO disk='D:\MCCPS2A.bak'
with noinit,name='MCCPS2A'

DBCC SHRINKFILE (MesnacMix_Log, 1);
GO

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