数据库 · 2011-10-14

sql server自动备份任务的sql

适合每天自动备份，自动命名：数据库名_日期.bak

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DECLARE @dbpath nvarchar(50)
--settings begin
USE AgentPlatform
set @dbpath='f:\bak\'
--settings end
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @dbname nvarchar(50)
SET @dbname=(Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid))
DECLARE @dbdate nvarchar(50)
select @dbdate=replace( cast(getdate() as char(10)),' ','')
DECLARE @FullPath nvarchar(1000)
set @FullPath = @dbpath + @dbname + '_' + @dbdate + '.bak'
SELECT @FullPath
backup database @dbname to disk=@FullPath
with INIT