SQL Server database backup script: How to use parameters for file path/location?

Question

I use a simple script to backup database in SQL Server:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
GO

My goal is to backup several databases using a script.

This will be used to different clients so I have use parameters that I can edit every time I need to run the script.

SQL Server says that my script has a syntax error.

Can you guys check my query, please?

declare @loc nvarchar(200) = 'D:\BAK\'
declare @client nvarchar(50) = 'CLIENT001_'
declare @date nvarchar(50) = '2020-02-29'

BACKUP DATABASE DB1
TO DISK = @loc + @client + 'DB1_' + @date + '.BAK'
GO

BACKUP DATABASE DB2
TO DISK = @loc + @client + 'DB2_' + @date + '.BAK'
GO

BACKUP DATABASE DB3
TO DISK = @loc + @client + 'DB3' + @date + '.BAK'
GO

BACKUP DATABASE DB4
TO DISK = @loc + @client + 'DB4' + @date + '.BAK'
GO

Answer 1

You can use dynamic SQL to achieve that. Also, GO limits the scope of variables (use only one at the end of the batch).

declare @loc nvarchar(200) = 'D:\BAK\'
declare @client nvarchar(50) = 'CLIENT001_'
declare @date nvarchar(50) = '2020-02-29'

exec(' BACKUP DATABASE DB1 TO DISK = '''+@loc + @client + 'DB1_' + @date + '.BAK'+''' ')
exec(' BACKUP DATABASE DB2 TO DISK = '''+@loc + @client + 'DB2_' + @date + '.BAK'+''' ')
exec(' BACKUP DATABASE DB3 TO DISK = '''+@loc + @client + 'DB3_' + @date + '.BAK'+''' ')
exec(' BACKUP DATABASE DB4 TO DISK = '''+@loc + @client + 'DB4_' + @date + '.BAK'+''' ')
go

You could also add databases to backup in your dynamic script like below:

declare @loc nvarchar(200) = 'D:\BAK\'
declare @client nvarchar(50) = 'CLIENT001_'
declare @date nvarchar(50) = '2020-02-29'
DECLARE @dbs_to_backup VARCHAR(1000)= 'DB1,DB2,DB3,DB4';
DECLARE @final_loc2 NVARCHAR(500)= @date + '.BAK';

drop table if exists #temp

SELECT replace(final_string, '##', value) string_to_execute, ROW_NUMBER() over (order by (select null)) rn
into #temp
FROM
(
    SELECT 'BACKUP DATABASE ## TO DISK = ''' + @loc + '##_' + @final_loc2 + '''' final_string
) t
CROSS APPLY
(
    SELECT value
    FROM STRING_SPLIT(@dbs_to_backup, ',')
) tt;

declare @i int = 1
declare @sql nvarchar(1000)

while ((select max(rn) from #temp) >= @i)
begin
    set @sql = (select string_to_execute from #temp where rn = @i)
    print @sql
    exec sp_executesql @sql
    set @i += 1
end

HTH.