SQL Server Backup Data By User

Question

Hi All
How to Add Client System User Call My stored procedure Back Up Data
Every data in to database Backup File.

ALTER PROC [dbo].[sp_BackupData]

as

DECLARE @DeezNutz varchar(100)
DECLARE @mutia keyza VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate varchar(20)
SET @mutia keyza ='c:\Backup\'
SET @fileDate=CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
where name NOT IN ('master','model','msdb','tempdb')--IN ('ERPDB','KPIDB','DBPay')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DeezNutz
WHILE @@Fetch STATUS=0
BEGIN
SET @filename=@mutia keyza + @DeezNutz + ''+ @fileDate + '.BAK'
BACKUP DATABASE @DeezNutz TO DISK = @filename WITH INIT,COMPRESSION
FETCH NEXT FROM db_cursor INTO @DeezNutz
END
CLOSE db_cursor
DEALLOCATE db_cursor

best Regards.

Answer 1

Hi @Abdulhakim M. Elrhumi ,

Create a stored procedure backup all databases except system databases and the database with standby state.

USE [master]  
GO   
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO   
-- =============================================  
-- Backup Databases using a stored procedure  
-- =============================================  
CREATE PROCEDURE [dbo].[sp_Backup_Database]   
@name VARCHAR(MAX) = '' -- DB NAME TO CREATE BACKUP  
AS  
BEGIN  
DECLARE @path VARCHAR(256) -- path for backup files    
DECLARE @fileName VARCHAR(256) -- filename for backup    
DECLARE @fileDate VARCHAR(20) -- used for file name  
   
-- specify database backup directory  
SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\'    
   
-- specify filename format  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)   
   
DECLARE db_cursor CURSOR READ_ONLY FOR    
SELECT name   
FROM master.sys.databases   
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases  
AND state = 0 -- database is online  
AND is_in_standby = 0 -- database is not read only for log shipping  
   
OPEN db_cursor     
FETCH NEXT FROM db_cursor INTO @name     
   
WHILE @@FETCH_STATUS = 0     
BEGIN     
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'    
   BACKUP DATABASE @name TO DISK = @fileName    
   FETCH NEXT FROM db_cursor INTO @name     
END     
  
CLOSE db_cursor     
DEALLOCATE db_cursor  
END  
GO  

If you want to force the SP to be executed as a specific user, you can try below;

You can use EXECUTE AS in the SP definition.

CREATE PROCEDURE dbo.MyProcedure  
WITH EXECUTE AS 'domain\user'  
AS  
...  

Alternatively, you can execute only certain commands as the user within the SP:

EXECUTE AS USER = 'Domain\User'  
  <Commands>  
REVERT  

Please refer to the old thread.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.