How do I use variables in sqlserver backup disk path?-CodePudding

the following 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);

Bot the "TO DISK" directive and the "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.

CodePudding user response：

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 in a similar way (instead of hard-coding "MyDatabase"). 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, or use dynamic SQL. e.g.

DECLARE @database sysname = N'DEMO';

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

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

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

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

EXECUTE @context @sql, 
  N'@db sysname, @fullpath nvarchar(1024), @backup_name nvarchar(500)',
  @database, @fullpath, @backup_name;

CodePudding user response：

According to https://docs.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