How do I backup a database with a stored procedure in SQL?

Question

I'm trying to create a stored procedure that backs up a database, however whenever I execute it, the folder I'm trying to backup to remains empty.

Here is my stored procedure:

USE [HarvestMan_SoutheskFarm_03_05_22]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Will Sewell
-- Create date: 03-05-2022
-- Description: Procedure to back up a database
-- =============================================

ALTER PROCEDURE [dbo].[BackupDatabases] 
    @name VARCHAR(MAX) = 'HarvestMan_SoutheskFarm_03_05_22' -- DB NAME TO CREATE BACKUP
AS
BEGIN
    DECLARE @path VARCHAR(256) -- path of backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'C:\Users\will.sewell\Documents\Database_Test'

    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    BEGIN
        SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

        BACKUP DATABASE @name TO DISK = @fileName
    END
END

I have tried changing the drive that it is writing to.

I've copy and pasted the database name I want to backup to ensure no spelling mistakes.

I'm assuming it might be a permissions issue or a visible files in the folder.

When I execute the stored procedure, I get this:

Processed 24472 pages for database 'HarvestMan_SoutheskFarm_03_05_22', file 'HarvestMan_dat' on file 2.

Processed 1 pages for database 'HarvestMan_SoutheskFarm_03_05_22', file 'HarvestMan_log' on file 2. BACKUP DATABASE successfully processed 24473 pages in 5.039 seconds (37.941 MB/sec).

Completion time: 2022-05-04T10:27:30.2344290+01:00

Answer 1

create proc SqlDBBackup  
as  
begin  
    declare @path varchar(1000);  
    set @path='D:\Backup\SampleDBTest3' + CONVERT(CHAR(10),
        GETDATE(),
        121) + '.bak';  
    BACKUP DATABASE SampleDBTest to DISK=@path;  
end