SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detecting Detached Databases


Detecting Detached Databases

Author
Message
rflewitt
rflewitt
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1
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
rflewitt
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1
Sorry - should have said SQL2000

- Richard



Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11389 Visits: 2730
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/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
rflewitt
rflewitt
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1
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
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11389 Visits: 2730
Thats correct - once you detach SQL doesnt know anything about it.

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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
rflewitt
rflewitt
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1
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
rflewitt
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1
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
Jack 95169
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 175
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
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27978 Visits: 39922
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
Jack 95169
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 175
That old? Hope they haven't been waiting all this time for my bug fix...
:o)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search