SQL Server Performance

Detach multiple databases

Discussion in 'SQL Server 2008 General DBA Questions' started by mojo1979, Nov 10, 2011.

  1. mojo1979 New Member

    Hi,

    I have around 100 db's attached to my SQL 2008 server. I want to detach around 50 of these and archive them onto a file share. I obviously don't want to do one at a time and would prefer to script it somehow.

    All 50 databases are located in the same folder - i.e 2009-01.

    Any help would be appreciated.

    Cheers
  2. Luis Martin Moderator

  3. johnson_ef Member

    What I used to do in this case is, use the below script for each DB and kept if for my activity time. Hope this will helpful for any specific DBs (50 out of 100)
    Yes, the scripts in the link in which Luis also does the same. Ignore if you find its a duplicate post.
    ------------------------------------------------------------------------------------------------------------------
    USE [master]
    GO
    ALTER DATABASE [Testing] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    USE [master]
    GO
    EXEC master.dbo.sp_detach_db @dbname = N'Testing'
    GO
    ------------------------------------------------------------------------------------------------------------------
    -Johnson
  4. satya Moderator

    Johnson
    Its simple to perform the DETACH process when you know the path and database names, however its on production server its better to do it step by step.

Share This Page