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

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

 

 1 USE [HMConsume_crp]
 2 GO
 3 /****** 对象:  StoredProcedure [dbo].[HMConsume_BackupDatabase]    脚本日期: 04/21/2010 13:34:42 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 CREATE PROCEDURE [dbo].[HMConsume_BackupDatabase]   
 9        --@databaseName sysname
10 AS  
11 BEGIN  
12        exec master..xp_cmdshell 'net use z: \\10.0.1.82\down "123" /user:huama-k.mysharepoint.huama.com.cn\administrator'
13         ---建立映射 
14 
15  
16 
17        SET NOCOUNT ON;  
18  
19        DECLARE @sqlCommand NVARCHAR(1000)  
20        DECLARE @dateTime NVARCHAR(20)  
21        DECLARE @fileName NVARCHAR(100) 
22        DECLARE @execStr NVARCHAR(1000)
23  
24        SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +  
25        REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   
26 
27        SET @fileName='HMConsume_crp' + @dateTime + '.BAK'
28 
29        SET @sqlCommand = 'BACKUP DATABASE ' + 'HMConsume_crp' +  
30            ' TO DISK = ''D:\AutoBackDB\' + @fileName+''''   
31          
32        EXECUTE sp_executesql @sqlCommand
33        -------备份数据库到指定目录
34 
35        SET @execStr = 'copy D:\AutoBackDB\'+@fileName+' z:'
36 
37        exec master..xp_cmdshell @execStr 
38        --------拷贝备份文件到网络
39        
40        exec master..xp_cmdshell 'net use z: /delete'
41        ------删除映射
42 END
43