当前位置:Gxlcms > 数据库问题 > Sql Server远程备份数据库方式一(合集)

Sql Server远程备份数据库方式一(合集)

时间:2021-07-01 10:21:17 帮助过:9人阅读

--打开允许执行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_configurexp_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+</span>+@backup+ +@password+ /user:+@TargetIP+</span>+@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+</span>+@backup+</span>+@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+</span>+@backup+ /delete‘‘‘
--4.删除共享关系 exec master..xp_cmdshell ‘net use \192.168.1.222\backup /delete‘
exec(@sql2)

--5.关闭允许执行xp_cmdshell
EXEC sp_configurexp_cmdshell, 0

END

技术图片

 

Sql Server远程备份数据库方式一(合集)

标签:backup   代码   init   备份文件   ast   copy   let   arch   fir   

人气教程排行