定时备份数据库SQL

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_BACKUPDATABASE_PMSOD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_BACKUPDATABASE_PMSOD]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

 

 

CREATE          PROC SP_BACKUPDATABASE_PMSOD
/*
'程序代号：dbo.SP_BACKUPDATABASE_PMSOD
'程序名称：定时备份数据库
'目　　的：定时备份数据库
作者：wind add 2007/07/16
exec  SP_BACKUPDATABASE_PMSOD
*/

AS

DECLARE @URL VARCHAR(1000)
DECLARE @USERID VARCHAR(100)
DECLARE @PASWORD VARCHAR(100)
DECLARE @BakName VARCHAR(100)
DECLARE @EXECSQL VARCHAR(2000)
DECLARE @SERVERNAME VARCHAR(100)
DECLARE @LocalFileName VARCHAR(100)
BEGIN
SET @SERVERNAME='192.168.1.12'
SET @URL='\\192.168.1.12\PMSBackup'
SET @USERID='pms'
SET @PASWORD='pms'
SET @BakName='W:\PMSOD_db_'+replace(replace(replace(convert(varchar(19),getDate(),120),' ','-'),':','-'),'-','') +'.BAK'
SET @EXECSQL='net use W: '+@URL+' "'+@PASWORD+'" /user:'+@SERVERNAME+'\'+@USERID
SET @LocalFileName='F:\JXPMSV2\OLD DB BACKUP\PMSOD_db_'+replace(replace(replace(convert(varchar(19),getDate(),120),' ','-'),':','-'),'-','')+'.BAK'
PRINT @EXECSQL
exec master..xp_cmdshell @EXECSQL
backup database PMSOD to disk=@LocalFileName
backup database PMSOD to disk=@BakName
exec master..xp_cmdshell 'net use W: /delete'
END

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO