Friday, October 22, 2010

Remove SharePoint 2010 Databases

I’ve installed and uninstalled SharePoint many times on my VMs and in the lab.  Problem with uninstalling SharePoint 2010 is that it leaves behind it’s many databases in SQL Server.  So not to create a mess when I reinstall, I have a SQL Query I run to do a cleanup by first running the below query which loops tough all the non-system databases in SQL server to generate a second query which I’ll run to remove SharePoint databases from previous install.

Query 1:

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity','Adventure*')
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
   PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
  print 'DROP DATABASE [' +  @dbname + ']'
  print 'go'
  print 'print ''Drop of ' + upper(@dbname) + ' database successfully completed'''
  print 'go'
  PRINT ' '
  FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '-->  All databases successfully dropped'''

The output of this query gives me a second cleanup query in the SQL Server Management studio output screen (shown below).

image 

Simply copying the output from the first query into a new query window and executing now drops all the dbs.  Go into the folders where the .mdf and .ldf files resided and verify everything is gone.  You now have a clean slate in SQL for fresh install of SharePoint.