3 minute read

dbatools.io = command-line SQL Server Management Studio

This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.

Backup

Taking backup is an essential task for everyone working with any kind of data. SSMS offers a wide range of options for taking backups, and at least the same you can achieve using dbatools.

Backup-DbaDatabase

Backup-DbaDatabase

# take a full copy-only backup
Backup-DbaDatabase -SqlInstance $server -Database BackMeUp -Type Full -CopyOnly -CompressBackup

<#
SqlInstance  Database Type TotalSize DeviceType Start                   Duration End                    
-----------  -------- ---- --------- ---------- -----                   -------- ---                    
e6928404da5d BackMeUp Full 2.71 MB   Disk       2020-08-04 21:44:00.000 00:00:01 2020-08-04 21:44:01.000
#>
# take a differential backup and save as multiple files
# take regular FULL first - not copy-only
Backup-DbaDatabase -SqlInstance $server -Database BackMeUp -Type Full
# take a DIFF now
Backup-DbaDatabase -SqlInstance $server -Database BackMeUp -Type Diff -FileCount 2

<#
SqlInstance  Database Type         TotalSize DeviceType Start                   Duration End                    
-----------  -------- ----         --------- ---------- -----                   -------- ---                    
e6928404da5d BackMeUp Differential 485.00 KB Disk       2020-08-04 21:47:30.000 00:00:01 2020-08-04 21:47:31.000
#>
# take a T-LOG backup
Backup-DbaDatabase -SqlInstance $server -Database BackMeUp -Type Log

<#
SqlInstance  Database Type TotalSize DeviceType Start                   Duration End                    
-----------  -------- ---- --------- ---------- -----                   -------- ---                    
e6928404da5d BackMeUp Log  80.00 KB  Disk       2020-08-04 21:48:51.000 00:00:00 2020-08-04 21:48:51.000
#>

Restore

The whole purpose of taking a backup is to restore it if needed.

Restore-DbaDatabase

Restore-DbaDatabase

# restore a backup next to the existing database (rename database and files)
Restore-DbaDatabase -SqlInstance $server -DatabaseName BackMeUp -WithReplace -Path 'C:\var\opt\mssql\data\BackMeUp_202008042347.bak' -RestoredDatabaseNamePrefix Restored -ReplaceDbNameInFile

<#
ComputerName         : localhost
InstanceName         : MSSQLSERVER
SqlInstance          : e6928404da5d
BackupFile           : C:\var\opt\mssql\data\BackMeUp_202008042347.bak
BackupFilesCount     : 1
BackupSize           : 2.71 MB
CompressedBackupSize : 2.71 MB
Database             : RestoredBackMeUp
Owner                : sa
DatabaseRestoreTime  : 00:00:03
FileRestoreTime      : 00:00:03
NoRecovery           : False
RestoreComplete      : True
RestoredFile         : RestoredBackMeUp.mdf,RestoredBackMeUp_log.ldf
RestoredFilesCount   : 2
Script               : {RESTORE DATABASE [RestoredBackMeUp] FROM  DISK = N'C:\var\opt\mssql\data\BackMeUp_202008042347.bak' WITH  FILE = 1,  MOVE N'BackMeUp' 
                       TO N'/var/opt/mssql/data/RestoredBackMeUp.mdf',  MOVE N'BackMeUp_log' TO N'/var/opt/mssql/data/RestoredBackMeUp_log.ldf',  NOUNLOAD,  
                       REPLACE,  STATS = 10}
RestoreDirectory     : /var/opt/mssql/data
WithReplace          : True
#>

See it

Once backup is taken, or while you are preparing to restore you might want to take a peak what is inside of these magical BAK / TRN files.

Get-DbaDbBackupHistory

Get-DbaDbBackupHistory

# see the backup history including copy-only
Get-DbaDbBackupHistory -SqlInstance $server -Database BackMeUp -IncludeCopyOnly

<#
SqlInstance  Database Type         TotalSize DeviceType Start                   Duration End                    
-----------  -------- ----         --------- ---------- -----                   -------- ---                    
e6928404da5d BackMeUp Differential 485.00 KB Disk       2020-08-04 21:47:30.000 00:00:01 2020-08-04 21:47:31.000
e6928404da5d BackMeUp Log          80.00 KB  Disk       2020-08-04 21:48:51.000 00:00:00 2020-08-04 21:48:51.000
e6928404da5d BackMeUp Full         2.71 MB   Disk       2020-08-04 21:47:25.000 00:00:01 2020-08-04 21:47:26.000
e6928404da5d BackMeUp Full         2.71 MB   Disk       2020-08-04 21:44:00.000 00:00:01 2020-08-04 21:44:01.000
#>

Get-DbaBackupInformation

# see the details of the backup file with this extra command
Get-DbaBackupInformation -SqlInstance $server -Path 'C:\var\opt\mssql\data\BackMeUp_202008042347.bak'

<#
SqlInstance  Database Type     TotalSize DeviceType Start                   Duration End                    
-----------  -------- ----     --------- ---------- -----                   -------- ---                    
e6928404da5d BackMeUp Database 2.71 MB   Disk       2020-08-04 21:47:25.000 00:00:01 2020-08-04 21:47:26.000
#>

Get-DbaLastBackup

# get overall details of the last backup on the instance
Get-DbaLastBackup -SqlInstance $server | Format-Table

<#
ComputerName InstanceName SqlInstance  Database         LastFullBackup          LastDiffBackup          LastLogBackup          
------------ ------------ -----------  --------         --------------          --------------          -------------          
localhost    MSSQLSERVER  e6928404da5d BackMeUp         2020-08-04 21:47:26.000 2020-08-04 21:47:31.000 2020-08-04 21:48:51.000
localhost    MSSQLSERVER  e6928404da5d master                                                                                  
localhost    MSSQLSERVER  e6928404da5d model                                                                                   
localhost    MSSQLSERVER  e6928404da5d msdb                                                                                    
localhost    MSSQLSERVER  e6928404da5d RestoredBackMeUp     
#>

Get-DbaDbRestoreHistory

# see the restore history
Get-DbaDbRestoreHistory -SqlInstance $server | Format-Table

<#
BackupFinishDate    ComputerName Database         Date                From                                            InstanceName RestoreType SqlInstance  To  
----------------    ------------ --------         ----                ----                                            ------------ ----------- -----------  --  
04/08/2020 21:47:26 localhost    RestoredBackMeUp 04/08/2020 22:17:56 C:\var\opt\mssql\data\BackMeUp_202008042347.bak MSSQLSERVER  Database    e6928404da5d /...
#>

Measure-DbaBackupThroughput

# see some stats about backup performance
Measure-DbaBackupThroughput -SqlInstance $server -Database BackMeUp | FT

<#
ComputerName InstanceName SqlInstance  Database AvgThroughput AvgSize AvgDuration MinThroughput MaxThroughput MinBackupDate          
------------ ------------ -----------  -------- ------------- ------- ----------- ------------- ------------- -------------          
localhost    MSSQLSERVER  e6928404da5d BackMeUp 2.71 MB       2.71 MB 00:00:01    2.71 MB       2.71 MB       2020-08-04 21:47:25.000
#>

Bonus: Snapshots

Snapshot might be considered as special kind of backup and while SSMS does not help you to create them easily without T-SQL, dbatools can help here.

New-DbaDbSnapshot

New-DbaDbSnapshot

# create a new snapshot
New-DbaDbSnapshot -SqlInstance $server -Database BackMeUp

Get-DbaDbSnapshot

# get all the snapshots 
Get-DbaDbSnapshot -SqlInstance $server -Database BackMeUp

Restore-DbaDbSnapshot

# restore from snapshot
Restore-DbaDbSnapshot -SqlInstance $server -Database BackMeUp

Remove-DbaDbSnapshot

# finally, remove the snapshot
Remove-DbaDbSnapshot -SqlInstance $server -Database BackMeUp

That should sum up this week. dbatools have your back 🙂

Thank you,

Mikey