数据库定时备份

 --新建文件夹
 sp_configure 'show advanced options', 1;   
GO   
RECONFIGURE;   
GO   
sp_configure 'xp_cmdShell', 1;   
GO   
RECONFIGURE   
GO
declare @currentDate datetime,@Day varchar(20)
set @currentDate=getdate()
set @Day= cast(year(@currentDate) as varchar(4))+'_'+cast(month(@currentDate) as varchar(2))+'_'+cast(day(@currentDate) as varchar(2))
declare @cmd varchar(1000)
--set @cmd=N'mkdir D:\Program Files\Microsoft SQL Server实例根\MSSQL11.SQL0\MSSQL\Backup\AdventureWorks2012_'+@Day
set @cmd=N'mkdir D:\Program Files\AdventureWorks2012_'+@Day
--set @cmd=N'mkdir D:\db\AdventureWorks2012_'+@Day
 select @cmd
exec xp_cmdshell @cmd

--完备
go
declare @strDate varchar(30),@backupFile varchar(200),@backupLogFile varchar(200),@currentDate datetime,@Day varchar(20)
set @currentDate=getdate()
set @Day= cast(year(@currentDate) as varchar(4))+'_'+cast(month(@currentDate) as varchar(2))+'_'+cast(day(@currentDate) as varchar(2))
set @strDate=@Day+'_'+cast(datepart(hour,@currentDate) as varchar(2))+'_'+cast(datepart(minute,@currentDate) as varchar(2))+'_'+cast(datepart(ss,@currentDate) as varchar(2))

set @backupFile = 'D:\db\AdventureWorks2012_'+@Day+'\AdventureWorks2012_'+@strDate+'.bak'
backup database AdventureWorks2012 to disk=@backupFile WITH NOFORMAT, NOINIT, 
NAME = N'AdventureWorks2012-完整 数据库 备份', 
SKIP, NOREWIND, NOUNLOAD

--log备份
go
declare @strDate varchar(30),@backupFile varchar(200),@backupLogFile varchar(200),@currentDate datetime,@Day varchar(20)
set @currentDate=getdate()
set @Day= cast(year(@currentDate) as varchar(4))+'_'+cast(month(@currentDate) as varchar(2))+'_'+cast(day(@currentDate) as varchar(2))
set @strDate=@Day+'_'+cast(datepart(hour,@currentDate) as varchar(2))+'_'+cast(datepart(minute,@currentDate) as varchar(2))+'_'+cast(datepart(ss,@currentDate) as varchar(2))

set @backupLogFile='D:\db\AdventureWorks2012_'+@Day+'\AdventureWorks2012_'+@strDate+'.trn'
backup log AdventureWorks2012 to disk=@backupLogFile