如何创建数据库备份到另一台机器中的文件夹，只给出权限？ [英] How to create db back up to a folder in another Machine with only permissions given?

问题描述

如何创建数据库备份到另一台机器中只有权限的文件夹？



我可以在我的系统中创建数据库备份或何时与给予文件夹的权限共享。其他网络路径未找到问题。我想这样做而不共享在c＃2.0，.Net 2.0中给予相同代码的文件夹和权限。

DB BACK UP UP WITH UPOW SP

< pre lang =SQL> CREATE PROCEDURE [dbo]。[NWPS_CREATE_DBBACKUP]
（

@ p_OriginalDBName varchar （ 1000 ），
@ p_DestinationSQLServer varchar （ 1000 ），
@ p_DestinationBackUpLocation varchar （ 1000 ），
@ p_DBBackUPFILENAME nvarchar （ 1000 ），
@ p_error_code int OUTPUT ，
@ p_error_msg varchar （ 1000 ） OUTPUT

）
AS
SET NOCOUNT ON
BEGIN

DECLARE @ DBName varchar （ 60 ）
DECLARE @ BackName varchar （ 120 ）
DECLARE @ DataName varchar （ 60 ）
DECLARE @ DataFileName < span class =code-keyword> varchar （ 120 ）
DECLARE @ LogName varchar （ 60 ）
DECLARE @ LogFileName varchar （< span class =code-digit> 120 ）
DECLARE @ DiskName varchar （ 120 ）
DECLARE @ error VARCHAR （ 1000 ）
DECLARE @ Result INT - 存储dir DOS命令的结果
DECLARE @ cmd SYSNAME - 存储动态创建的DOS命令
DECLARE @ BackUpLocation varchar （ 1000 ） - 存储动态创建的DOS命令

- SET @ BackUpLocation ='\\'+ @p_DestinationSQLServer ++'：\'+ @ p_DestinationBackUpLocation +'\'+ @ p_OriginalDBName + cast（@p_month as varchar（3））+ cast（@p_year as varchar（4））+ cast（getdate（）as varchar（12））+'new.bak '
SET @ BackUpLocation = ' \\' + @ p_DestinationSQLServer + ' \' + @p_DestinationBackUpLocation + ' \' + @ p_DBBackUPFILENAME

SET @ DiskName = @ BackUpLocation + ' disk'


BACKUP DATABASE @ p_OriginalDBName
TO DISK = @ BackUpLocation
WITH FORMAT，
MEDIANAME = ' Z_SQLServerBackups'，
NAME = ' 完全备份NWPSCS_Archive';

- --USE master
- EXEC sp_addumpdevice'disk'，@ DiskName，@ BootUpLocation
- EXEC sp_helpdevice

select @ BackUpLocation as [ @ p_DestinationBackUpPath ]

SET @ error = @@ error
IF @ error <> 0
GOTO ERROR_HANDLER

SET @ p_error_msg = ' '
SET @ p_error_code = ' 0'
return （ 0 ）

ERROR_HANDLER：
SET @ p_error_msg = ' 过程名称：[NWPS_CREATE_DBBACKUP]。系统错误。' + RTRIM（ convert （ char （ 6 ）， @ error ））
SET @ p_error_code = ' - 1'
返回（ 1 ）

结束

---------------------- ------------------------------------------------

解决方案

这就是我要尝试的：

（1）确保SQL Server数据库的db_owner可以访问您所在的网络位置尝试将备份写入（我假设您在与正在运行的SQL Server实例相同的服务器上备份数据库）

（2）在CREATE PROCEDURE之后添加WITH EXECUTE AS OWNER c这样的ommand（在SQL Server 2014，FYI中将需要WITH EXECUTE AS ...命令）：

 CREATE PROCEDURE [dbo]。[NWPS_CREATE_DBBACKUP] 
 （
 
 @p_OriginalDBName varchar（1000），
 @p_DestinationSQLServer varchar（1000），
 @p_DestinationBackUpLocation varchar（1000），
 @p_DBBackUPFILENAME nvarchar（1000），
 @p_error_code int OUTPUT，
 @p_error_msg varchar（1000）OUTPUT 
 
）
  WITH EXECUTE AS OWNER  
 AS 

执行此操作将允许执行过程，就像db_owner正在运行过程一样，无论调用过程的是哪个帐户，只要调用帐户具有EXECUTE权限。

How to create db back up to a folder in another Machine with only permissions given?

I am able to create the DB Back Up in my system or when shared with permissions given to a folder. Else network path not found issue comes. I want to do so without sharing the folder and permissions given to the same code wise in c#2.0, .Net 2.0.
DB BACK UP CREATED WITH BELOW SP

CREATE PROCEDURE [dbo].[NWPS_CREATE_DBBACKUP]    
(    
      
 @p_OriginalDBName varchar(1000),     
 @p_DestinationSQLServer varchar(1000),    
 @p_DestinationBackUpLocation varchar(1000),    
 @p_DBBackUPFILENAME nvarchar(1000) ,    
 @p_error_code int OUTPUT,    
 @p_error_msg varchar(1000) OUTPUT    
       
)    
AS    
SET  NOCOUNT ON    
BEGIN    
    
 DECLARE @DBName varchar(60)    
 DECLARE @BackName varchar(120)    
 DECLARE @DataName varchar(60)    
 DECLARE @DataFileName varchar(120)    
 DECLARE @LogName varchar(60)    
 DECLARE @LogFileName varchar(120)    
 DECLARE @DiskName varchar(120)    
 DECLARE @error  VARCHAR(1000)     
 DECLARE @Result INT -- stores the result of the dir DOS command    
 DECLARE @cmd SYSNAME -- stores the dynamically created DOS command    
 DECLARE @BackUpLocation varchar(1000) -- stores the dynamically created DOS command     
    
    --SET @BackUpLocation='\\'+ @p_DestinationSQLServer ++':\'+ @p_DestinationBackUpLocation+'\'+@p_OriginalDBName+cast(@p_month as varchar(3))+cast(@p_year  as varchar(4))+cast(getdate() as varchar(12))+'new.bak'    
 SET @BackUpLocation='\\'+ @p_DestinationSQLServer +'\'+ @p_DestinationBackUpLocation+'\'+@p_DBBackUPFILENAME    
    
 SET @DiskName=@BackUpLocation+'disk'    
    
    
 BACKUP DATABASE @p_OriginalDBName     
 TO DISK = @BackUpLocation      
    WITH FORMAT,    
    MEDIANAME = 'Z_SQLServerBackups',    
    NAME = 'Full Backup NWPSCS_Archive';    
    
-- --USE master    
-- EXEC sp_addumpdevice 'disk', @DiskName, @BackUpLocation    
-- EXEC sp_helpdevice    
    
select @BackUpLocation as [@p_DestinationBackUpPath]    
     
 SET @error= @@error    
 IF @error <> 0    
   GOTO ERROR_HANDLER      
    
 SET @p_error_msg = ''     
 SET @p_error_code='0'    
 return(0)    
    
 ERROR_HANDLER:    
  SET @p_error_msg = 'Procedure Name : [NWPS_CREATE_DBBACKUP]. System Error.' + RTRIM(convert(char(6),@error))     
  SET @p_error_code =  '-1'    
  Return(1)    
    
END    

----------------------------------------------------------------------

解决方案

This is what I would try:
(1) Be sure the db_owner for the SQL Server database has access to the network location you are trying to write the backup to (I assume you are backing up the database on the same server as the SQL Server instance being ran)
(2) Add WITH EXECUTE AS OWNER after the CREATE PROCEDURE command as such (the WITH EXECUTE AS... command will be required in SQL Server 2014, FYI):

CREATE PROCEDURE [dbo].[NWPS_CREATE_DBBACKUP]    
(    
      
 @p_OriginalDBName varchar(1000),     
 @p_DestinationSQLServer varchar(1000),    
 @p_DestinationBackUpLocation varchar(1000),    
 @p_DBBackUPFILENAME nvarchar(1000) ,    
 @p_error_code int OUTPUT,    
 @p_error_msg varchar(1000) OUTPUT    
       
)
WITH EXECUTE AS OWNER    
AS 

Doing this will allow the procedure to execute as if the db_owner was running the procedure regardless of what account is calling the procedure as long as the calling account has EXECUTE permission.

这篇关于如何创建数据库备份到另一台机器中的文件夹，只给出权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！