存储过程：异地备份数据库文件

USE [HMConsume_crp]
GO
/****** 对象:  StoredProcedure [dbo].[HMConsume_BackupDatabase]    脚本日期: 04/21/2010 13:34:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMConsume_BackupDatabase]   
       --@databaseName sysname
AS  
BEGIN  
       exec master..xp_cmdshell 'net use z: \\10.0.1.82\down "123" /user:huama-k.mysharepoint.huama.com.cn\administrator'
        ---建立映射

 

       SET NOCOUNT ON;  
 
       DECLARE @sqlCommand NVARCHAR(1000)  
       DECLARE @dateTime NVARCHAR(20)  
       DECLARE @fileName NVARCHAR(100)
       DECLARE @execStr NVARCHAR(1000)
 
       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +  
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   

       SET @fileName='HMConsume_crp' + @dateTime + '.BAK'

       SET @sqlCommand = 'BACKUP DATABASE ' + 'HMConsume_crp' +  
           ' TO DISK = ''D:\AutoBackDB\' + @fileName+''''   
         
       EXECUTE sp_executesql @sqlCommand
       -------备份数据库到指定目录

       SET @execStr = 'copy D:\AutoBackDB\'+@fileName+' z:'

       exec master..xp_cmdshell @execStr
       --------拷贝备份文件到网络
       
       exec master..xp_cmdshell 'net use z: /delete'
       ------删除映射
END