CREATE PROCEDURE [dbo].[DBBAK.UnicomDB]
@wheredb varchar(100) --接受,xm,fz,nd,pt
AS
BEGIN
--备份
Declare @theYear varchar(4)
Declare @theMonth varchar(2)
Declare @theDay varchar(2)
Declare @theMinu varchar(2) --取名用的,为了区别地区
Declare @FileName varchar(50)
Set @theYear = cast(year(getdate()) as varchar(4))
Set @theMonth = cast(month(getdate()) as varchar(2))
Set @theDay = cast(day(getdate()) as varchar(2))
Set @theMinu = cast(DATEPART(MI,GETDATE()) as varchar(2))
If len(@theMonth) = 1
Begin
Set @theMonth = '0' + @theMonth
End
If len(@theDay) = 1
Begin
Set @theDay = '0' + @theDay
End
Set @FileName = @theYear+@theMonth+@theDay
Declare @sql nvarchar(500)
--厦门联通
If @wheredb = 'xm'
Begin
DBCC SHRINKDATABASE(N'UnicomDB')
Set @sql = 'BACKUP DATABASE [UnicomDB] TO DISK = N''D:\SQLBAK\DB\XM_UnicomDB_'+@FileName+'_'+@theMinu+'.rar'' WITH NOFORMAT, NOINIT, NAME = N''UnicomDB-完整 数据库 备份'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
End
execute sp_executesql @sql
END
@wheredb varchar(100) --接受,xm,fz,nd,pt
AS
BEGIN
--备份
Declare @theYear varchar(4)
Declare @theMonth varchar(2)
Declare @theDay varchar(2)
Declare @theMinu varchar(2) --取名用的,为了区别地区
Declare @FileName varchar(50)
Set @theYear = cast(year(getdate()) as varchar(4))
Set @theMonth = cast(month(getdate()) as varchar(2))
Set @theDay = cast(day(getdate()) as varchar(2))
Set @theMinu = cast(DATEPART(MI,GETDATE()) as varchar(2))
If len(@theMonth) = 1
Begin
Set @theMonth = '0' + @theMonth
End
If len(@theDay) = 1
Begin
Set @theDay = '0' + @theDay
End
Set @FileName = @theYear+@theMonth+@theDay
Declare @sql nvarchar(500)
--厦门联通
If @wheredb = 'xm'
Begin
DBCC SHRINKDATABASE(N'UnicomDB')
Set @sql = 'BACKUP DATABASE [UnicomDB] TO DISK = N''D:\SQLBAK\DB\XM_UnicomDB_'+@FileName+'_'+@theMinu+'.rar'' WITH NOFORMAT, NOINIT, NAME = N''UnicomDB-完整 数据库 备份'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
End
execute sp_executesql @sql
END
其实主 要是最后几行,前面都是拼接名字用的。
这个语句也可以直接用sql server操作界面备份的时候生成脚本。
这个脚本可以用在JOB,每天生成。