SQL Server 2005异地备份

本方案采用备份至本地然后copy到文件服务器的方法。

SQL server 2005打了sp2的补丁后好像存储过程xp_cmdshell是不能直接用的

1. 显示高级选项（仅需执行一次）

1. EXEC sp_configure 'show advanced options', 1  
2.   
3. GO  
4.   
5. RECONFIGURE  
6.   
7. GO  

2. 允许执行xp_cmdshell

1. EXEC sp_configure 'xp_cmdshell', 1  
2.   
3. GO  
4.   
5. RECONFIGURE  
6.   
7. GO  

3. 添加映射驱动器

1. declare @string nvarchar(200)  
2.   
3. set @string = 'net use z: \\192.168.1.2\D$\db_backup "123456" /user:fileserver\administrator'  
4.   
5. exec master..xp_cmdshell @string  

其中192.168.1.2为文件服务器的地址，db_backup为该服务器的共享文件夹，fileserver为机器名，administrator 123456 分别为共享时设置的用户名密码。

4. 备份数据库至本地

1. declare @date datetime  
2.   
3. set @date = GetDate()  
4.   
5. declare @str nvarchar(100)  
6.   
7. set @str = 'd:\mydb'+ convert(nvarchar(12), @date, 112) +'.bak'  
8.   
9. backup database mydb to disk=@str with init  

With init为覆盖同名文件（本例设计为1天执行一次，不会出现覆盖的情况）。

5. 拷贝到文件服务器

1. declare @str1 nvarchar(100)  
2.   
3. set @str1 = 'copy '+ @str +' z:'  
4.   
5. exec master..xp_cmdshell @str1  

6. 删除映射以及本地备份

1. exec master..xp_cmdshell 'net use z: /delete'  
2.   
3. declare @str2 nvarchar(100)  
4.   
5. set @str2 = 'del '+@str+''  
6.   
7. exec master..xp_cmdshell @str2  

7.关闭允许执行cmdshell

1. EXEC sp_configure 'xp_cmdshell', 0  
2.   
3. GO  
4.   
5. RECONFIGURE  
6.   
7. GO  

建立sql server 作业执行步骤2-7，成功备份