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

Question

1.00/5 (2 votes)

See more:

.NET2.0

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

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 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

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

Posted 17-Jun-14 4:18am

Rahul 105

Updated 18-Jun-14 18:04pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TheSqlGuy · Answer 1 · 2014-06-20T16:48:00

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.