In a previous post I wrote about how easy it was to restore a whole SQL Servers user databases from a  directory using the dbatools module. Maybe it is a good idea to look at for disaster recovery scenarios but even PowerShell is going to be useless if your backups don’t work

But setting up a solution to test your backups (technically test your restores) is difficult isn’t it?

Lets use the dbatools module and see how easy it is

The dbatools module has a command called Test-DbaLastBackup if you look at the page or at the help using

Get-Help Test-DbaLastBackup -ShowWindow

you will see that this command

Restores all or some of the latest backups and performs a consistency check

1. Gathers information about the last full backups

2. Restores the backups to the Destination with a new name. If no Destination is specified, the originating SqlServer will be used.

3. The database is restored as “dbatools-testrestore-$databaseName” by default, but you can change dbatools-testrestore to whatever you would like using -Prefix

4. The internal file names are also renamed to prevent conflicts with original database

5. A consistency check is then performed

6. And the test database is finally dropped

So, if you only have one SQL Server but want to ensure that you are testing your backup files then as along as you have the diskspace you can simply run

Test-DbaLastBackup -SqlServer sql2016n2

and the latest backups that have been taken will be restored using a different name with different filenames, checked for consistency and then dropped

and as you can see an object is returned

SourceServer  : SQL2016N2

TestServer    : SQL2016N2

Database      : FadetoBlack

FileExists    : True

RestoreResult : Success

DbccResult    : Success

SizeMB        : 1243.26

BackupTaken   : 3/18/2017 12:36:07 PM

BackupFiles   : Z:\SQL2016N2\FadetoBlack\FULL_COPY_ONLY\SQL2016N2_FadetoBlack_FULL_COPY_ONLY_20170318_123607.bak

which shows the Server, the database, if the file exists, the restore result, the DBCC result, the size of the backup file, when it was taken and the path used

You don’t have to use the same server and in many shops you would not want to. You can specify a destination server and you can also pipe the results to Out-GridView to enable easy filtering.

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 | OGV

Note you need to be backing up to a shared location ie a path that starts \\ I have fudged this a little in the demo for the keen eyed

Maybe you only want to test the backups for the important databases or some backups are restored using other means and you don’t need to test them this way. There is a databases parameter which you can tab through the database names

In the ISE you can see the drop down of database names

If you have limited space you might not want to test the largest databases so you can use the MaxMb parameter to only restore databases under this size. In the example below, you can see that Fadetoblack was skipped and the system databases were skipped as they are not backing up to a shared location

The databases are restored onto the server using a different name and the files are also named differently to avoid any conflicts. The default prefix is dbatools-testrestore- but you can change this using the prefix switch if you wish

You may not want to use your special, super quick storage for performing your test restores. If you have separate data drives that you would like to use for the restores, you can specify those with the -DataDirectory and -LogDirectory. If you do not use these switches then the command will use the default data and log locations.

Its possible to reduce the amount of checks that are done. If you only want to do a Verify Only on the backup then you can use the -VerifyOnly switch, you can skip the DBCC check by using the -NoCheck switch and you can leave the test restore databases on the server using the -NoDrop switch

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

 Install-Module dbatools

Then you can use

 Update-dbatools