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

You cannot use variables in this command - those have to be literal strings. The only option you have (within T-SQL) would be to use dynamic SQL e.g. concatenate together your T-SQL statement and then use EXEC sp_executesql to execute it — marc_s
– marc_s, Commented Mar 29, 2020 at 6:30
Honestly, if you do not have much tsql skill your first step should ALWAYS be searching the internet to find solutions that have already been written and verified. Ola has a complete set of administrative utilities here - very popular and well written. Try that. — SMor
– SMor, Commented Mar 29, 2020 at 12:38

sacse · Accepted Answer · 2020-03-29 14:12:49Z

3

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.

edited Mar 29, 2020 at 14:12

answered Mar 29, 2020 at 7:48

sacse

3,8272 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TitoAvang Over a year ago

thank you so much for the big help, i will try these.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related