CREATE proc [dbo].[PROC_BACKUPJOINTOWN]
as
declare @sql nvarchar(4000),@return_value int,@par nvarchar(1000)--D:\testback\JOINTOWN20130916.bak
if not exists(
select * from master..sysdatabases
where name='JOINTOWN'
)
BEGIN--数据库不存在
return
end
else
BEGIN
SELECT @par=CONVERT(VARCHAR(8), GETDATE(), 112) ;
select @sql='BACKUP DATABASE JOINTOWN to disk=''D:\testback\JOINTOWN'+@par+'.bak''' ;
EXECUTE @return_value= sp_executesql @sql ;
SELECT @return_value;
IF @return_value=0
BEGIN
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell' ,1;
RECONFIGURE;
SELECT @par=CONVERT(VARCHAR(8), GETDATE()-1, 112) ;
select @sql='del D:\testback\JOINTOWN'+ @par+'.bak';
EXEC master..xp_cmdshell @sql,NO_OUTPUT;
END
ELSE
BEGIN
RETURN
END
END