• Seggerman-675349 - Monday, February 26, 2018 1:04 PM

    thanks folks but it doesn't quite answer my question
    do I  create a full backup to a device - and set the device through T-SQL so the file name has a date as part of it
    drop the device and recreate with a new name the indicates it is a diff
    run a differential backup
    is this workable?
    if I create backups to disk will it automatically create a differential backup ?

    thanks

    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/