SQL Server BACKUP FULL by variables

Question

I’m trying to generate a full backup by variables in SQL Server

The script I’m using is as follows

declare @fecha varchar(50)
declare @query varchar(500)
declare @dbname varchar(500);
declare @SERVERNAME varchar (50)
SET @dbname='PruebaBackup'
SET @SERVERNAME=(SELECT @@SERVERNAME AS 'SERVER NAME')
SET @fecha=(select convert(varchar, getdate(), 23))

SET @query=('BACKUP DATABASE [PruebaBackup] TO  DISK = N''R:\SQL_Backup\Temporal\@SERVERNAME'+@dbname+@fecha+'.bak'''+
'WITH NOFORMAT, NOINIT,  NAME = N''@dbname-Full Database Backup''
, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10')

exec(@query)

But when I get the backup the host name is not printed, you can advise me on how to use the variable to print it in the name, please

Answer 1

In your current statement you are including variables as text in your concatenated string, because you are not using single quotes in the right place.

You might want to try this solution:

_{(split over multiple columns to increase readability)}

SET @query = 'BACKUP DATABASE [PruebaBackup] 
TO  DISK = N''R:\SQL_Backup\Temporal\' + @SERVERNAME + '\' + @dbname  + @fecha + '.bak''' + ' 
WITH NOFORMAT, NOINIT, NAME = N''' + @dbname + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'

Using @@SERVERNAME might not be a good solution, because it contains a backslash if you are using a named instance. E.g.

SERVERNAME\INSTANCENAME

So with your data you would receive something like this:

BACKUP DATABASE [PruebaBackup] 
TO  DISK = N'R:\SQL_Backup\Temporal\SERVERNAME\INSTANCENAME\PruebaBackup2023-05-26.bak' WITH NOFORMAT, NOINIT, NAME = N'PruebaBackup-Full Database Backup', SKIP, NOREWIND, COPY_ONLY, NOUNLOAD, COMPRESSION, STATS = 10

It can work, but might not be what you are intending.

---

I noticed that you are not using the COPY_ONLY option in your backup script. Doing so resets the DIFF flag of the pages that were changed since the last FULL backup. Following an example output of a database page which show the DIFF (1:6) = NOT CHANGED allocation status:

...
Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL
DIFF (1:6) = NOT CHANGED            ML (1:7) = NOT MIN_LOGGED    
...

In short it is recommended to use the COPY_ONLY (BACKUP Database Syntax) parameter in your backup statement to not break Differential backup chains when performing out-of-place backups, that are not part of your company's backup strategy.

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

^{Reference: Copy-Only Backups (Microsoft Learn | SQL Docs)}

Or in simple terms: If your company is backing up databases with a third-party tool and the schedule creates a FULL backup on Saturday and DIFF backups on all other days at 8pm, then if you create an out-of-place backup, this will tell the backup that the next DIFF backup is based on your out-of-place backup.

If you where to delete this out-of-place FULL backup file, then the subsequent DIFF could not be used to restore your database to a consistent state using a previous FULL because the DIFF will reference your out-of-place FULL backup which might have been deleted.

Here a graphical representation of which FULL backup a DIFF backup will reference when creating an Out-Of-Place (OOPL) FULL backup without using the COPY_ONLY option.

          | SA | SO | MO | TU | WE | TH | FR
----------+----+----+----+----+----+----+----
FULL 8pm  | F1 |    |    |    |    |    |   
DIFF 8pm  |    | D1 | D1 | D1 | D2 | D2 | D2
OOPL 2pm  |    |    |    |    | F2 |    |

The DIFF (D2) backup on Wednesday which is created a 8pm will reference the OOPL backup (F2) which was created at 2pm. If you delete this file, then it is not possible to restore the database using the DIFFs created afterwards, until the third-party backup tool creates a new FULL (F3) backup.

          | SA | SO | MO | TU | WE | TH | FR | SA | SO | MO
----------+----+----+----+----+----+----+----+----+----+----+
FULL 8pm  | F1 |    |    |    |    |    |    | F3 |    |    |
DIFF 8pm  |    | D1 | D1 | D1 | D2 | D2 | D2 |    | D3 | D3 |
OOPL 2pm  |    |    |    |    | F2 |    |    |    |    |    |

So it is better to use the COPY_ONLY flag in out-of-place FULL backups. The graphical representation will then look this:

          | SA | SO | MO | TU | WE | TH | FR
----------+----+----+----+----+----+----+----
FULL 8pm  | F1 |    |    |    |    |    |   
DIFF 8pm  |    | D1 | D1 | D1 | D1 | D1 | D1
OOPL 2pm  |    |    |    |    | F2 |    |

The FULL backup (F2) does not break the Differential backup chain (D1). They all still reference the FULL backup (F1)

So your script might be better created with the COPY_ONLY flag included:

SET @query = 'BACKUP DATABASE [PruebaBackup] 
TO  DISK = N''R:\SQL_Backup\Temporal\' + @SERVERNAME + '\' + @dbname  + @fecha + '.bak''' + ' 
WITH NOFORMAT, NOINIT, NAME = N''' + @dbname + '-Full Database Backup'', SKIP, NOREWIND, COPY_ONLY, NOUNLOAD, COMPRESSION, STATS = 10'

Answer 2

Note that the backup command accepts variables for many of the options. I.e., no need to use dynamic SQL for your case:

DECLARE @filename nvarchar(256)
DECLARE @bname nvarchar(128)
DECLARE @fecha varchar(23) = (select convert(varchar, getdate(), 23))
DECLARE @dbname sysname = N'PruebaBackup'

SET @filename = CONCAT(N'R:\SQL_Backup\Temporal\', @@SERVERNAME, '\', @dbname, @fecha, '.bak')
SET @bname = CONCAT(@dbname, '-Full Database Backup')

--Debugging
PRINT @filename
PRINT @bname

--The backup command
BACKUP DATABASE @dbname
TO  DISK = @filename
WITH NOFORMAT, NOINIT, NAME = @bname, COMPRESSION, STATS = 10