正文
sql server异地备份数据库
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
--注意：异地备份数据库，需要先开启备份文件夹的网络共享，数据库选择master
--打开允许执行xp_cmdshell
EXEC sp_configure 'xp_cmdshell',1 exec 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 init exec 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', 1
GO
RECONFIGURE WITH OVERRIDE
GO
备份数据库存储过程
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Pr_BackupDatabase] Script Date: 04/05/2014 21:23:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 王再华
-- Create date: 2014.04.05
-- Description: 备份数据库服务器的所有数据库到映射文件夹
-- =============================================
ALTER PROCEDURE [dbo].[Pr_BackupDatabase] AS
BEGIN
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',1 declare @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
for
select name from sysdatabases open my_cursor
declare @name sysname
fetch next from my_cursor into @name
while(@@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 @name
end
fetch first from my_cursor into @name close my_cursor
deallocate 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', 0 END
