Using dbatools to check your most recent backup

In a previous post DBATOOLS.IO….A DBA timesaver/luxury, I showed you how to install and ran through a quick demo of a very useful tool called dbatools. It has over 500 very useful DBA commands and today I am going to show you one I use often that restores your last backup.

Backups are very important as a DBA, but even more important is the restore process. If your backups do not work, you do not work 😦

Using dbatools, verifying your last backup is very simple. Watch!

By typing or copying in the command below, it will go to the backup location from the -SqlInstance parameter and restore the last backup in that location.

Here are the parameters I used and the reason why:

-SqlInstance, put the name of the instance you want to restore from.

-Destination, you can restore on to a different server, so put your backup server you want to restore on.

-CopyFile, “If this switch is enabled, the backup file will be copied to the destination default backup location unless CopyPath is specified.”

-Database, the name of the database you want to restore.

-NoDrop, if you omit this parameter, it will restore the database, but then drop it after it completes. This will prevent it from being dropped.

-SqlCredential, if you use Sql Server authentication you need to provide a username, it will prompt you for the password. If you use Windows Authentication, you can omit this parameter.

-DestinationCredential, if you use Sql Server authentication you need to provide a username, it will prompt you for the password. If you use Windows Authentication, you can omit this parameter.

Test-DbaLastBackup -SqlInstance gisdb1\gisdb1 -Destination GISDBEDIT\GISSQLEDIT -CopyFile -Database crimegis1 -NODROP -SqlCredential sa -DestinationCredential sa

Here it is prompting for my credentials.

Now the command is scanning the database and determining which backups should be restored.

It looked through the backup history and then the remote directory and found the file and now is restoring the BAK file.

When it is complete, it runs a Dbcc Checkdb to make sure everything is good and displays this information showing you the results of the attempt. It shows the time it took for the restore and the checkdb, and it shows that transaction logs were also restored.

If you do not want to restore the transaction logs, you can use the -IgnoreLogBackup parameter to just restore the BAK file. See below, same commands just adding the -ignorebackup:

Test-DbaLastBackup -SqlInstance gisdb1\gisdb1 -Destination GISDBEDIT\GISSQLEDIT -CopyFile -Database crimegis1 -NODROP -IgnoreLogBackup -SqlCredential sa -DestinationCredential sa

And in the results window, it shows only the BAK was restored.

If we open SSMS, you can see the database got restored in this format: dbatools-testrestore-crimeGIS. There is of course a parameter you can use to restore with a different name (-Prefix).

To read about more that you can do with the Test-DbaLastBackup command, follow this link. https://docs.dbatools.io/Test-DbaLastBackup

There is so much you can do with this command, I just scratched the surface. You can test restore an entire instance, skip the dbcc checkdb, etc.