What is DBA Tools?
A collection of modules for SQL Server DBAs. It initially started out as ‘sqlmigration’, but has now grown into a collection of various commands that help automate DBA tasks and encourage best practices.
to see just how easy it is to migrate an entire SQL instance in one command Longer session here
Installing it is as easy as
which will get you over 80 commands . Visit https://dbatools.io/functions/ to find out more information about them
Following that session I wrote a Powershell Script to gather information about the last used date for databases which I blogged about here and then a T-SQL script to take a final backup and create a SQL Agent Job to restore from that back up which I blogged about here The team have used this solution (updated to load the DBA Database and a report instead of using Excel) ever since and it proved invaluable when a read-only database was dropped and could quickly and easily be restored with no fuss.
I was chatting with Chrissy LeMaire who founded DBATools b | t about this process and when she asked for contributions in the SQL Server Community Slack I offered my help and she suggested I write this command. I have learnt so much. I thoroughly enjoyed and highly recommend working on projects collaboratively to improve your skills. It is amazing to work with such incredible professional PowerShell people.
I went back to the basics and thought about what was required and watched one of my favourite videos again. Grant Fritcheys Backup Rant
I decided that the process should be as follows
- Performs a DBCC CHECKDB
- Database is backed up WITH CHECKSUM
- Database is restored with VERIFY ONLY on the source
- An Agent Job is created to easily restore from that backup
- The database is dropped
- The Agent Job restores the database
- performs a DBCC CHECKDB and drops the database for a final time
This (hopefully) passes all of Grants checks. This is how I created the command
I check that the SQL Agent is running otherwise we wont be able to run the job. I use a while loop with a timeout like this
There are a lot more checks and logic than I will describe here to make sure that the process is as robust as possible. For example, the script can exit after errors are found using DBCC CHECKDB or continue and label the database backup file and restore job appropriately. Unless the force option is used it will exit if the job name already exists. We have tried to think of everything but if something has been missed or you have suggestions let us know (details at end of post)
The only thing I didn’t add was a LARGE RED POP UP SAYING ARE YOU SURE YOU WANT TO DROP THIS DATABASE but I considered it!!
Performs a DBCC CHECKDB
Running DBCC CHECKDB with Powershell is as easy as this
Database is backed up WITH CHECKSUM
Stuart Moore is my go to for doing backups and restores with SMO
I ensured that the backup was performed with checksum like this
Database is restored with VERIFY ONLY on the source
I used SMO all the way through this command and performed the restore verify only like this
An Agent Job is created to easily restore from that backup
First I created a category for the Agent Job
and then generated the TSQL for the restore step by using the script method on the Restore SMO object
This is how to create an Agent Job
and then to add a job step to run the restore command
The database is dropped
We try 3 different methods to drop the database
The Agent Job restores the database
To run the Agent Job I call the start method of the Job SMO Object
Then we drop the database for the final time with the confidence that we have a safe backup and an easy one click method to restore it from that backup (as long as the backup is in the same location)
There are further details on the functions page on dbatools
Some videos of it in action are on YouTube http://dbatools.io/video
You can take a look at the code on GitHub here
You can install it with
You too can also become a contributor https://dbatools.io/join-us/ Come and write a command to make it easy for DBAs to (this bit is up to your imagination).