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
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
Leave a Reply