Drop/Delete databases older than 180 Days

We have  quite a few Dev servers where developers go on creating new databases for their developement work and testing. Whatever databases are needed are moved to QA servers after initial architecture is finalized.

They wanted DBAs to delete databases which were created earlier than six months and drop them.  Now, to manually scan through 100 plus servers and drop databases older than six months is tedious task.

I created a job with following script to do this on first Sunday of every month. Variables in this script can be changed to suit your purpose. Please find the script below.

 

— ** Change Admin DB to appropriate DB or can use TempTable
—  Drop Old Table if any
IF EXISTS (SELECT * FROM Admin..sysobjects WHERE Name = ‘ToDropOldDB’and xtype = ‘U’)
DROP TABLE ToDropOldDB
GO

–Create new table and insert data in to it
select name into admin..ToDropOldDB from master..sysdatabases
where crdate < GETDATE() – 180 and dbid > 5
–** Change the number of days as per your need in above line.

DECLARE @Name nvarchar(128);
DECLARE DropDB_cursor CURSOR FOR 
SELECT Name
FROM Admin.dbo.ToDropOldDB

OPEN DropDB_cursor  
FETCH NEXT FROM DropDB_cursor INTO @Name

WHILE @@FETCH_STATUS = 0  
BEGIN 
USE [master]

–** Remove comments from Alter and Drop or Detach lines as you need ** —
  –ALTER DATABASE [@DB_Name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
–DROP DATABASE [@dbName]
–EXEC master.dbo.sp_detach_db @dbname = @Name, @skipchecks = ‘false’

Print @Name
FETCH NEXT FROM DropDB_cursor INTO @Name
end
CLOSE DropDB_cursor  
DEALLOCATE DropDB_cursor

— ** Can Drop Table But I want to keep it to refer at a later date
— ** what databases were dropped/detached  by this Job
— ** or a select from this can be E-Mailed

–IF EXISTS (SELECT * FROM Admin..sysobjects WHERE Name = ‘ToDropOldDB’and xtype = ‘U’)
–DROP TABLE ToDropOldDB

About: Niraj Kumar


Leave a Reply

Your email address will not be published. Required fields are marked *