Friday, January 16, 2015

Detach a series of databases in MSSQL

declare CUR cursor for
    SELECT name
    FROM master..sysdatabases
    where name like 'ABC_%'

declare @db varchar(50)
declare @msg varchar(200)

open CUR

fetch next from CUR into @db
while @@FETCH_STATUS = 0
begin
    set @msg = 'detach ' + @db
    raiserror (@msg, 0, 1) with nowait
   
    exec sp_detach_db @db, 'true'
   
    fetch next from CUR into @db
end

close CUR
deallocate CUR


---- Below is batch script to quickly remove disk files. Note you can use multiple wild card match. ----

ECHO OFF

FOR /f "tokens=*" %%i in ('DIR /a:d /b D:\mssql_db\*abc* D:\mssql_db\*xyz*') DO (
    ECHO %%i
    rmdir /s /q "D:\mssql_db\%%i"
)

Blog Archive

Followers