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

本文介绍了如何创建数据库备份到另一台机器中的文件夹，只给出权限？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 如何创建数据库备份到另一台机器中只有权限的文件夹？ 我可以在我的系统中创建数据库备份或何时与给予文件夹的权限共享。其他网络路径未找到问题。我想这样做而不共享在c＃2.0，.Net 2.0中给予相同代码的文件夹和权限。 DB BACK UP UP WITH UPOW 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 - 存储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 SPCREATE 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. 这篇关于如何创建数据库备份到另一台机器中的文件夹，只给出权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！