关闭

定时备份数据库SQL

259人阅读 评论(0) 收藏 举报

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

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:311685次
    • 积分:4233
    • 等级:
    • 排名:第7155名
    • 原创:120篇
    • 转载:77篇
    • 译文:0篇
    • 评论:31条
    最新评论