Seggerman-675349 - Monday, February 26, 2018 1:04 PM
This is a script i made using Adventure Works when i was learning.:
First part is FULL Backup
Second one is Differential backup - you can see WITH DIFFERENTIAL
Third one is Log Backup it explicitly states that it is one.
--This is the full backup part
--Full backup, Keeps the changes of the database to a point in time
backup database [AdventureWorks2012]
TO DISK = N'E:\BACKUPS\AVWFULL181217.bak'--direccion donde se almacenara el full
WITH NOFORMAT,
NOINIT,
NAME =N'ADVENTUREWORKS2012FULLDBBACKUP',
DESCRIPTION='BACKUP AL DIA 18-12-2017',
SKIP,
NOREWIND,
NOUNLOAD,STATS=10
GO
--Differential backup, Keep the changes made to the database till the last full backup
BACKUP DATABASE [AdventureWorks2012]
TO DISK = N'E:\BACKUPS\AVWDIFF181217.bak' --direccion donde se almacenara el differential
WITH DIFFERENTIAL, --diciendo explicitamente que tipo de backup es, aqui podemos ver que es un diferencial
NOFORMAT,
NOINIT,
NAME = N'AdventureWorks2012-Differential Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
--Transaction log backup, Keeps the changes made to the database until the last differential
BACKUP LOG [AdventureWorks2012]
TO DISK = N'E:\BACKUPS\AVWLOG181217.bak'--direccion donde se almacenara el transaccional
WITH NOFORMAT,
NOINIT,
NAME = N'AdventureWorks2012-Log Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
Sorry for the spanish comments its my native language.
As Gail said before you can also declare variables as backupdate as DATE and pass getdate() as result and build a script that creates name for your backup.
Theres an example in the website below.
https://solutioncenter.apexsql.com/create-daily-database-backups-with-unique-names-in-sql-server/