--注意：异地备份数据库，需要先开启备份文件夹的网络共享，数据库选择master

--打开允许执行xp_cmdshell EXEC sp_configure 'xp_cmdshell',1exec master..xp_cmdshell 'net use \\192.168.0.139\DatabaseBackup password /user:192.168.0.139\administrator'backup database myDatabase to disk='\\192.168.0.139\DatabaseBackup\myDatabase_04_05.bak' with initexec master..xp_cmdshell 'net use \\192.168.0.139\DatabaseBackup /delete'--关闭允许执行xp_cmdshell EXEC sp_configure'xp_cmdshell', 0

 

 

如果提示无权限错误，请先执行以下脚本，注意数据库要选master

EXEC sp_configure 'show advanced options', 1GORECONFIGURE WITH OVERRIDEGO

 

备份数据库存储过程

USE [master]GO/****** Object:  StoredProcedure [dbo].[Pr_BackupDatabase]    Script Date: 04/05/2014 21:23:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:         王再华-- Create date:    2014.04.05-- Description:    备份数据库服务器的所有数据库到映射文件夹-- =============================================ALTER PROCEDURE [dbo].[Pr_BackupDatabase]ASBEGIN    SET NOCOUNT ON;declare @TargetIP varchar(20)declare @backup varchar(20)declare @username varchar(20)declare @password varchar(20)set @TargetIP='192.168.1.139' --要备份到的服务器IP地址set @backup='myDatabase'          --共享文件夹set @username='administrator' --用户名set @password='password'--密码--1.打开允许执行xp_cmdshell EXEC sp_configure 'xp_cmdshell',1declare @sql1 varchar(2000)set @sql1='master..xp_cmdshell ''net use \\'+@TargetIP+'\'+@backup+' '+@password+' /user:'+@TargetIP+'\'+@username+''''--2.建立共享关系 exec master..xp_cmdshell 'net use \\192.168.0.139\DatabaseBackup password /user:192.168.0.139\administrator'exec(@sql1) declare @sql varchar(2000)/*************************** 循环备份数据库 START ***************************/declare my_cursor cursor scroll dynamic forselect name from sysdatabasesopen my_cursordeclare @name sysnamefetch next from my_cursor into @namewhile(@@fetch_status=0)begin  if(@name<>'master' and @name<>'model' and @name<>'msdb' and @name<>'tempdb' and @name<>'ReportServer' and @name<>'ReportServerTempDB')     begin          --3.执行备份          --backup database myDatabase to disk='\\192.168.0.139\DatabaseBackup\myDatabase_04_05.bak' with init          set @sql='backup database '+@name+' to disk=''\\'+@TargetIP+'\'+@backup+'\'+@name+'_'+CONVERT(varchar(100), GETDATE(), 23)+'.bak'' with init'          exec(@sql)         end  fetch next from my_cursor into @nameendfetch first from my_cursor into @nameclose my_cursordeallocate my_cursor/*************************** 循环备份数据库 END ***************************/declare @sql2 varchar(2000)set @sql2='master..xp_cmdshell ''net use \\'+@TargetIP+'\'+@backup+' /delete'''--4.删除共享关系 exec master..xp_cmdshell 'net use \\192.168.1.222\backup /delete'exec(@sql2)--5.关闭允许执行xp_cmdshell EXEC sp_configure'xp_cmdshell', 0END