Detecting Detached Databases

  • rflewitt

    SSChasing Mays

    Points: 658

    I've many databases on the server I've inherited. Some are detached and rather than manually check each db to see which is attached/detached I'm trying to find a sql script method that will tell me. I've searched through the site but cannot find anything that may help. Is it possible? If so, how? Thanks for any help.

  • rflewitt

    SSChasing Mays

    Points: 658

    Sorry - should have said SQL2000

    - Richard

  • Andy Warren

    SSC Guru

    Points: 119655

    Nothing that I know of. One way would be to pull the list of mdf's, then query against (sysfiles? have to look) to see if its listed. Or you could just try to move or rename them, SQL wont let you if they are in use.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • rflewitt

    SSChasing Mays

    Points: 658

    It appears that once detached the db is no longer know to SQL Server (or at least it looks that way to me). I'm taking the sledge hammer approach and searching for mdf's and then as you suggest comparing them against sysdatabases. I guess I could do this in a dos script but that's a bit beyond me at the moment. Maybe someone will come up with something better - thanks, Richard.

  • Andy Warren

    SSC Guru

    Points: 119655

    Thats correct - once you detach SQL doesnt know anything about it.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • rflewitt

    SSChasing Mays

    Points: 658

    I've knocked together this short script that I run from Master. Forgive any scripting stupidity - I'm not a DBA - but I think you get the general approach. It assumes you have some idea where the .mdf files are. I'm sure someone could improve on it but it does what I need at the moment.

    ------------------------------

    if exists (select name from sysobjects where name ='temp_mdf_files') drop table temp_mdf_files

    if exists (select name from sysobjects where name ='temp_mdf_dbnames') drop table temp_mdf_dbnames

    create table temp_mdf_files (

    full_filename varchar(200))

    create table temp_mdf_dbnames (

    pos_dbname varchar(200))

    insert temp_mdf_files

    exec xp_cmdshell 'dir e:\*.mdf /s/b'

    insert temp_mdf_dbnames

    select reverse(

    substring( reverse(

    substring(full_filename,1,datalength(full_filename)-4)) ,1,patindex('%\%', reverse(full_filename))-5)

    )

    from temp_mdf_files

    order by 1

    select * from temp_mdf_dbnames

    where pos_dbname not in (select name from sysdatabases)

    drop table temp_mdf_files

    drop table temp_mdf_dbnames

  • rflewitt

    SSChasing Mays

    Points: 658

    This teaches me to test a lot more before going public. The above didn't really work but this version does (I think).

    if exists (select name from sysobjects where name ='temp_mdf_files') drop table temp_mdf_files

    create table temp_mdf_files (

    full_filename varchar(200))

    insert temp_mdf_files

    exec xp_cmdshell 'dir e:\*.mdf /s/b'

    insert temp_mdf_files

    exec xp_cmdshell 'dir c:\*.mdf /s/b'

    insert temp_mdf_files

    exec xp_cmdshell 'dir d:\*.mdf /s/b'

    select * from temp_mdf_files

    where Upper(full_filename) not in (select Upper(filename) from sysdatabases)

    order by 1

    drop table temp_mdf_files

  • Jack 95169

    SSC-Addicted

    Points: 401

    Nearly...

    You need to enable XP_cmdshell beforehand

    I've also done a bit of tidying up

    ---- enable these jobs

    ---- show advanced options

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    ---- enable xp_cmdshell

    sp_configure 'xp_cmdshell', 1;

    GO

    RECONFIGURE;

    GO

    ---- hide advanced options

    sp_configure 'show advanced options', 0;

    GO

    RECONFIGURE;

    GO

    -- based on http://www.sqlservercentral.com/Forums/Topic6166-5-1.aspx

    -- create temporary table

    create table #temp_mdf_files

    (

    full_filename varchar(200)

    )

    --populate the temp table with any MDF files found

    insert #temp_mdf_files

    exec xp_cmdshell 'dir c:\*.mdf /s/b'

    insert #temp_mdf_files

    exec xp_cmdshell 'dir d:\*.mdf /s/b'

    insert #temp_mdf_files

    exec xp_cmdshell 'dir e:\*.mdf /s/b'

    --

    select

    -- exclude the subdirectory name

    upper(reverse(substring(reverse(full_filename ), 1,charindex('\', reverse(full_filename ) )-1) )) As MDF_FileName,

    full_filename

    from #temp_mdf_files

    where

    --exclude rows which contain system messages or nulls

    full_filename like '%\%'

    --exclude system databases

    and upper(reverse(substring(reverse(full_filename ), 1,charindex('\', reverse(full_filename ) )-1) ))

    not in ('DISTMDL.MDF', 'MASTER.MDF', 'MODEL.MDF', 'MSDBDATA.MDF' , 'MSSQLSYSTEMRESOURCE.MDF', 'TEMPDB.MDF' )

    -- MDF filename excluding the subdirectory name

    and full_filename

    not in (select Upper(FILEname) from sys.SYSdatabases)

    order by MDF_FileName

    -- Housekeeping

    drop table #temp_mdf_files

    -- disable these jobs

    -- show advanced options

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    -- disable xp_cmdshell

    sp_configure 'xp_cmdshell', 0;

    GO

    RECONFIGURE;

    GO

    -- hide advanced options

    sp_configure 'show advanced options', 0;

    GO

    RECONFIGURE;

    GO

  • Lowell

    SSC Guru

    Points: 323331

    Jack 95169 (12/18/2012)


    Nearly...

    You need to enable XP_cmdshell beforehand

    I've also done a bit of tidying up

    wow Jack you replied to a ten year old thread!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jack 95169

    SSC-Addicted

    Points: 401

    That old? Hope they haven't been waiting all this time for my bug fix...

    :o)

  • MSSQL_NOOB

    SSCommitted

    Points: 1545

    I think it's okay ... search engines promises someone to stumble on this thread and they will have a fix to their issue. I'm just browsing hoping to gain more knowledge and glad to read up on it, 10 years old or not. And yes, we still have MSSQL2000 production db.

  • KrAziGuRl

    Grasshopper

    Points: 12

    Happy you responded as am looking for this solution or similar 🙂

  • Andy Steinke

    Mr or Mrs. 500

    Points: 565

    Thank you!  We may look at building a powershell script to do this, but comparing the mdfs to sys.databases is exactly the concept we needed.  SQL Server DBAs are the best DBAs!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714105

    These days, PoSh would be the way to go. Find all mdfs and then compare them with what's attache. In fact, I was just thinking about updating a script I have. I had one to detach all dbs and then attach a specific list for demos, but it would probably be of use with modifications for finding detached dbs.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply