--新建文件夹
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 FilesMicrosoft SQL Server实例根MSSQL11.SQL0MSSQLBackupAdventureWorks2012_'+@Day
set @cmd=N'mkdir D:Program FilesAdventureWorks2012_'+@Day
--set @cmd=N'mkdir D:dbAdventureWorks2012_'+@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:dbAdventureWorks2012_'+@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:dbAdventureWorks2012_'+@Day+'AdventureWorks2012_'+@strDate+'.trn'
backup log AdventureWorks2012 to disk=@backupLogFile