SQL Server Backup Data By User

Abdulhakim M. Elrhumi 356 Reputation points
2020-12-15T12:50:00.333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,150 questions
{count} votes

Accepted answer
  1. CathyJi-MSFT 22,321 Reputation points Microsoft Vendor
    2020-12-16T06:23:26.45+00:00

    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.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.