Day 3 – Backup-DbaDatabase Filenames and paths

Yesterday we looked at the basics of using the Backup-DbaDatabase function. In all of those examples the backup files were going to the default backup location of our SQL Server instance, and using the default name generation process of Backup-DbaDatabase.

This often isn’t what you want, so we offer you plenty of options on how to create and handle paths

The Defaults

By default if nothing else is specified backups will be created in the Default Backup directory for the target SQL Server Instance.

The file names will be created to match the following pattern:

DatabaseName_yyyyMMddHHmm-x-of-y

Breaking down to

  • – DatabaseName – self explanatory
  • – yyyy – 4 digit year
  • – MM – 2 digit month
  • – dd – 2 digit day of month
  • – HH – 24 hour format hour
  • – mm – 2 digit minutes

This is generated before the backup starts, so think of it as the starting time.

So if I kick off a backup of master now (29/04/2020 10:58) I’ll get the following files:

master_202004291058.bak

in the default backup location

The only other default file name section kicks in when you use striping (we’ll cover this in more detail on day 4), when it becomes:

    DatabaseName_yyyyMMddHHmm-x-of-y
    
  • – x-of-y – which part of the stripe the file is
  • So if I kicked of a 3 striped backup of master now (29/04/2020 10:58) I’ll get the following files:

    master_202004291058-1-of-3.bak
    master_202004291058-2-of-3.bak
    master_202004291058-3-of-3.bak
    
    

    We set the backup file extension using the standard SQL Server conventions:

    • – Full Backup – .bak
    • – Differential Backup – .bak
    • – Transaction Log Backup – .trn

    Now the defaults are explained let’s start doing something different

    Path Creation

    In the next section we’ll be discussing pushing backups to other locations. By default Backup-DbaDatabase will **NOT** create folders that do not exist.

    To override this behavior using the -BuildPath switch

    By default we will us xp_dirtree on the SQL instance to check the path exits. Unfortunately this is know to fail in a couple of cases, the most common being:

    • – The Sql Instance has write only permissions to the backup folder (common security permission)
    • – When the SQL Instance is on a *nix OS as xp_dirtree is not implemented

    In these case you can use -IgnoreFileChecks to skip the path test. However, if doing this it is recommended that you test the paths some other way.

    Basic path manipulation

    The most options are changing the file paths. The simplest method is to just provide a new new Path parameter:

    Backup-DbaDatabase -SqlInstance server1\instance -Database Master -Path c:\backups\
    
    

    Would create the backup file:

    c:\backups\master_202004291058.bak
    
    

    You can provide a UNC path if you want to backup to a different server. Just be mindful that the SQL Server instance doing the backup has to have write access directly to the share. dbatools is just giving instructions to the remote instance. Just because you can write to it doesn’t mean the instance can.

    Backup-DbaDatabase -SqlInstance server1\instance -Database Master -Path \\backups\sql$\instance
    
    

    Would create the backup file:

    \\backups\sql$\instance\master_202004291058.bak
    
    

    Now, what about if you want to stripe across multiple folders? Just pass in multiple paths:

    Backup-DbaDatabase -SqlInstance server1\instance -Database Master -Path \\backups\sql$\instance, \\backups2\sql$\instance
    
    

    Would create the backup files:

    \\backups\sql$\instance\master_202004291058-1-of-2.bak
    \\backups2\sql$\instance\master_202004291058-2-of-2.bak
    
    

    Note that this overrides the FileCount parameter and defaults, and will strip across the multiple folders

    What happens if you need to specify a specific filename? That’s where the FilePath parameter comes in:

    Backup-DbaDatabase -SqlInstance server1\instance -Database Master -FilePath \\backups\sql\EndOfYear2020_master.bak
    
    

    Will create just the single backup file \\backups\sql\EndOfYear2020_master.bak as requested

    If you want to backup each database into it’s own folder under a backup root, then the -CreateFolder switch will do that for you:

    Backup-DbaDatabase -SqlInstance server1\instance -Database Master -FilePath \\backups\sql\ -CreateFolder
    
    

    Will give you backups in

    \\backups\sql\master
    \\backups\sql\msdb
    \\backups\sql\model
    ...etc...
    
    

    More Complex file paths examples.

    Now as an example, let’s imagine you want to replicate Ola’s backup structure, so that backups are split up into folders like this:

    serverName$InstanceName/DatabaseName/BackupType
    
    

    This is where the -ReplaceInName switch comes into play. When this is specified, any of these values will be replace in Path or FilePath

    • – InstanceName – will be replaced with the instance Name
    • – ServerName – will be replaced with the server name
    • – DBname – will be replaced with the database name
    • – Timestamp – will be replaced with the timestamp (either the default, or the format provided)
    • – BackupType – will be replaced with Full, Log orDifferential as appropriate

    So this command:

    Backup-DbaDatabase -SqlInstance server1\instance -Database Master  -BackupType Diff -Path c:\backups\ServerName\InstanceName\DBname\BackupType\DBname-BackupType-TimeStamp.bak -ReplaceInName
    
    

    Will create a set of backups like:

    c:\backups\server1\instance\db1\Differential\db1-Differential-202004291058.bak
    c:\backups\server1\instance\db2\Differential\db2-Differential-202004291128.bak
    ...etc...
    
    

    You can also manipulate the TimeStamp format as well using the -TimeStampFormat parameter. This accepts all of the .Net date and time formatting options, Custom Date and Time format strings.

    So if you used this

    Backup-DbaDatabase -SqlInstance server1\instance -Database master -TimeStampFormat dddd-dd-MMMM-yyyy-gg-HHmmss
    
    

    You would end up with a backup named:

    Wednesday-29-April-2020-AD-133127.bak
    
    

    You can combine the 2 options which means there’s almost no limit to how you can manipulate the backup paths

    Conclusion

    Backup-DbaDatabase makes it easy to take the backups you want, have them stored where you want and named however you want.

    All posts in this series can be found at 31 Days of dbatools Backup and Restores