How do I use variables in SQL Server backup disk path?

Question

The following SQL statement works fine:

DECLARE @database VARCHAR(30) = 'DEMO';

BACKUP LOG @database
TO DISK = N'C:\zbackups\DEMO.trn' 
WITH NOFORMAT, NOINIT,  
NAME = N'MyDatabase Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

DBCC SHRINKFILE ('DEMO_log', 500);

This however does not:

DECLARE @database VARCHAR(30) = 'DEMO';

BACKUP LOG @database
TO DISK = 'C:\zbackups\' + @database + '.trn' 
WITH NOFORMAT, NOINIT,  
NAME = 'MyDatabase Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

DBCC SHRINKFILE (@database + '_log', 500);

Both the TO DISK directive and the DBCC SHRINKFILE parameters throw an error when I attempt to concatenate.

How do I use a variable in these location? Would also like to append some datetime parameters to the file path but haven't started researching that yet.

Side note: SQL Server 2008 has been completely unsupported for 3 years; it is long past time you sorted out your upgrade path. — Thom A
– Thom A ♦, Commented Jul 11, 2022 at 19:22
If you are developing maintenance scripts for your servers, I would advise heading on over to ola.hallengren.com. — Randy in Marin
– Randy in Marin, Commented Jul 11, 2022 at 19:40
A reality check for the shrink is to compare the log file size to the size of the data files. A 10 MB data file and a 35 GB log file calls for a shrink to fix years of a missing transaction log backup. If the log size is at it's "stable" growth limit, it would be a waste resources to shrink it. — Randy in Marin
– Randy in Marin, Commented Jul 11, 2022 at 19:52
Also why NOINIT? Do you not care if the .trn file eventually contains 50,000 log backups and grows to 5 TB in size? Do you only care about the last log backup anyway? It seems not, this cycle seems to be optimized simply to allow you to shrink. In which case maybe you just shouldn't be in full recovery. See the cleverly-placed links in my answer for more info. — Aaron Bertrand
– Aaron Bertrand, Commented Jul 11, 2022 at 19:52
@Larnu Agreed. Our upgrade path is in place and mostly waiting on... leadership, to buy in. — danielson317
– danielson317, Commented Jul 12, 2022 at 0:19

Aaron Bertrand · Accepted Answer · 2022-07-11 20:36:39Z

The problem is simply that you can't pass an expression there. Instead of:

TO  DISK = 'C:\zbackups\' + @database + '.trn'

You need:

DECLARE @fullpath nvarchar(1024);
SET @fullpath = 'C:\zbackups\' + @database + '.trn';

...

TO DISK = @fullpath

...

You can also parameterize the backup name and log file name in a similar way (instead of hard-coding NAME = 'MyDatabase Log Backup'). But to get the shrink operation to run in the right context (if you really, really, really, really need it, that is), and you can rely on every single database being configured in the same way, you're going to have to hard-code the database name somewhere (e.g. in a USE statement), or use dynamic SQL to set the context to the right database, e.g.

DECLARE @database sysname = N'DEMO';

DECLARE @fullpath    nvarchar(1024) = N'C:\zbackups\' + @database + '.trn',
        @backup_name nvarchar(500)  = @database + N' log backup',
        @log_name    nvarchar(500)  = @database _ N'_log';

DECLARE @sql nvarchar(max) = N'BACKUP LOG @db
  TO DISK = @fullpath
  WITH NOFORMAT, NOINIT, NAME = @backup_name, 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

DBCC SHRINKFILE(@log, 500);';

DECLARE @context nvarchar(1024) = QUOTENAME(@database)
  + N'.sys.sp_executesql';

EXECUTE @context @sql, -- run @sql but in the context of @database
  N'@db sysname, @log nvarchar(500), 
    @fullpath nvarchar(1024), @backup_name nvarchar(500)',
    @database, @log_name, @fullpath, @backup_name;

Randy in Marin · Accepted Answer · 2022-07-11 19:35:53Z

According to https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16 a variable can be used. I think an expression would be listed if allowed. The same goes for DBCC SHRINKFILE. Try

DECLARE @database VARCHAR(30) = 'DEMO';
DECLARE @target VARCHAR(260) = 'C:\zbackups\' + @database + '.trn';
DECLARE @logical_name VARCHAR(128) = @database + '_log';

BACKUP LOG @database
TO  DISK = @target
WITH NOFORMAT, NOINIT,  
NAME = 'MyDatabase Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

DBCC SHRINKFILE (@logical_name, 500);

If the logical file name does not always follow the dbname_log pattern, perhaps lookup the file id and use that instead of the logical name in SHRINKFILE. Be careful of database scope on the connection.

DECLARE @file_id int

SELECT @file_id = file_id 
FROM sys.database_files
WHERE physical_name = @target

Collectives™ on Stack Overflow

How do I use variables in SQL Server backup disk path?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related