Sqlserver 每天数据备份语句

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

其实主 要是最后几行，前面都是拼接名字用的。

这个语句也可以直接用sql server操作界面备份的时候生成脚本。

这个脚本可以用在JOB，每天生成。