dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Remove-SqlDatabaseSafely

This command will safely remove a SQL Database by performing a number of checks then creating an Agent Job to restore it.

  1. Performs a DBCC CHECKDB
  2. Backs up the database WITH CHECKSUM
  3. Restores with VERIFY ONLY on the source
  4. Creates and Agent job to easily restore from that backup
  5. Drops the database
  6. The Agent Job restores the database
  7. Performs a DBCC CHECKDB and drops the database for a final time

The idea came from Grant Fritchey’s backup rant, “Verify your backups, now!”.

By default the initial DBCC CHECKDB is performed. By default the jobs and databases are created on the same server. Use -DestinationServer to use a seperate server

It will start the SQL Agent Service on the Destination Server if it is not running.

Screenshot

remove-sqldatabasesafely

Scenario

It is easy to add databases. But not, it seems so easy to remove them in some shops. There can be many reasons for this. The project to upgrade a system doesn’t consider removing the older version of the database. The knowledge about a database is forgotten in the mists of time and in a risk averse environment no-one wants to remove it “in case something breaks”. The system owner of the database cannot be identified or cannot/will not sign off on removing a database. SQL Servers get “found” and handed to the DBA team without any information about them.

Whatever the cause, there comes a time in a database’s life when it is no longer required and it is time to drop it. As a good and responsible DBA we want to ensure that we do not risk losing the data.

Equally you may use a script such as this one from Aaron Bertrand to examine the sys.dm_db_index_usage_stats to see when indexes last had reads and writes (of course, there are caveats to that method) to identify databases that could be removed due to lack of use. What you might find using this method is that you identify a database that has no writes and cannot find a system owner and then drop it. 1 hour later you have people at your desk asking what happened to this very important read-only database and you need to put it back quick smart.

Alternatively, you may drop a database following all the correct process and with the required sign off from the system owner and 6 months later there is a requirement to restore the database for some analysis or investigation.

In this situation it would be wonderful to have an easy way to get the database back. Those are some of the scenarios that Remove-SQLDatabaseSafely will help you with

With as many checks as we can think of, this command provides a simple method to create an Agent Job to restore a database from a backup that has had restore verify only run against it, has been restored, has had DBCC CHECKDB against it

Example

The SQL Server Fade2Black has a database called RideTheLightning which has been identified for deletion. The server will continue to exist so you want to drop the database and save the backup on the server and create an agent job on the server to restore it from that backup

Remove-SqlDatabaseSafely -SqlServer ‘Fade2Black’ -Databases RideTheLightning -BackupFolder ‘C:\MSSQL\Backup\Rationalised – DO NOT DELETE’

Example

The SQL Server Fade2Black has two databases DemoNCIndex and RemoveTestDatabase which have been identified for deletion. The server will continue to exist so you want to drop the databases and save the backups on the server and create a agent jobs on the server to restore it from that backup

$Databases = ‘DemoNCIndex’,’RemoveTestDatabase’
Remove-SqlDatabaseSafely -SqlServer ‘Fade2Black’ -Databases $Databases -BackupFolder ‘C:\MSSQL\Backup\Rationalised – DO NOT DELETE’

Example

The server Fade2Black has a database called RidetheLightning that you have identified for deletion and you have a backup server called JusticeForAll where you can restore databases if required.

Remove-SqlDatabaseSafely -SqlServer ‘Fade2Black’ -DestinationServer JusticeForAll  -Databases RideTheLightning -BackupFolder ‘\BACKUPSERVER\BACKUPSHARE\MSSQL\Rationalised – DO NOT DELETE’

Example

You have a server IronMaiden and a lot of databases in a variable. You are confident in your DBCC checks and don’t need or want to run DBCC CheckDB prior to backing up the database on the drive Z. You want to create the jobs to restore the databases on TheWildHearts server. You need to set the Job Owner to THEBEARD\Rob to make sure your Jobs have the correct permissions. TheWildHearts is not set up in the same way as IronMaiden so the restore command will use the default paths on TheWildHearts

Remove-SqlDatabaseSafely -SqlServer IronMaiden -Databases $Databases -DestinationServer TheWildHearts -DBCCErrorFolder C:\DBCCErrors -BackupFolder z:\Backups -NoDBCCCheck -UseDefaultFilePaths -JobOwner ‘THEBEARD\Rob’

Example

IronMaiden is being decommisioned and all of the databases need to be removed. You have TheWildHearts server available as your backup server in case any of the databases are required. You don’t have time to worry about DBCC checks, you just want to get the databases removed safely or you are confident that you perform DBCC check regularly so you run the command below to backup all of the servers, perform a restore verify only, create agent jobs on TheWildHearts and output any DBCC Errors to a share on TheWildHearts. You need to ensure that the databases can be restored without worrying about filepaths so you set the restore job to use the default paths on TheWildHearts

Remove-SqlDatabaseSafely -SqlServer IronMaiden -AllDatabases -DestinationServer TheWildHearts -DBCCErrorFolder \TheWildHearts\DBCCErrors -BackupFolder z:\Backups -UseDefaultFilePaths -ContinueAfterDbccError

Author

This command was created by Rob Sewell. You can find Rob on Twitter and his blog.

Get-Help

From PowerShell, execute Get-Help Remove-SqlDatabaseSafely -Detailed for more information on this function.

Source Code

Want to see the source code? View Remove-SqlDatabaseSafely.ps1 on GitHub
 

Related commands