Hi

I look after a network with hundreads of servers and db's, quite often users unattach their DB's without removing the MDF file after. This cuases a pain as we are limited on space.

Anyway, I am trying to achive some type of excel spreadsheet, powershell or html page which shows unattached DB's VS attached DB's. What woild be better is that the unnacthed DB's get highlightered in red to bring it to our attenction.

Heres where I've got too.. I only need to sort the data and display now..

1. This is just a cmd file: sqlcmd -s SERVERNAME -i FindAtDbs.sql -o output.csv

(SQLCMD code is: SELECT [name] FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb','ReportServer','reportservertempdb'); )
as you can see I dont what to show master Db etc.

It creates a file like this....

name
-------------------------------------------------------------------------------------------------------------------------------- 
DB1
DB2
DB3
DB4
and so on

(6 rows affected)

2. I then use powershell to delete the last line  whcih you can see shows (6 rows affected) and the first two lines name & --------. Here is the script.


(Get-Content c:\output.csv) | where {$_.readcount -gt 2} | Set-Content c:\output.csv

$a = gc c:\output.csv; $c = $($a.Count -1) ; $c..$($c -1) | % {$a[$_] = Out-Null } ; sc -path c:\output.csv -va $a


3. Now I need to list the MDF files so I use the below (CMD) code.

dir /s /b E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\*.MDF > dblist.csv

4. Now I am left with two files. the first the file which shows all MDF's. heres a example.

E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\101105_RV_2011Rnwl_v2_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110022_PNWH_FL_Q12011_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110022_PNWH_FL_Q12011_v10_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110062_JIIC_2011_EDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110062_JIIC_2011_RDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110119_Jubilee_2011_EDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110404_Allianz_UK_Tender_v11_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110404_Allianz_UK_Tender_v11_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110462_Generali_Q2_2011_USHU.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110500_TMNF_AU_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110500_TMNF_AU_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110555_Fortis_ASR_EDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110555_Fortis_ASR_RDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110562_Eureko_2011_EDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110562_Eureko_2011_RDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110657_SignalIduna_EC_2011Rnwl_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110657_SignalIduna_EC_2011Rnwl_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110657_Signal_Iduna_Q2_2011_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110657_Signal_Iduna_Q2_2011_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110672_DEVK_2011Rnwl_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110672_DEVK_2011Rnwl_v10_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110725_TirolerVers_2011Rnwl_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\110725_TirolerVers_2011Rnwl_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\201011_JUB_US_RDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\2011_Munich_Re_France_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_10Q4_10Q4FX_USD_JPEQTS_10_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q3_09Q3FX_USD_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q3_10Q3FX_USD_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q3_10Q3FX_USD_v10_iPOT.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q3_10Q3FX_USD_v10_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q3_TESTING_EDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q3_TESTING_RDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q4_09Q3FX_USD_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q4_10Q4FX_USD_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q4_10Q4FX_USD_v10_R.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q4_10Q4FX_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_2010Q4_DLMProfiles.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_AIR_AOG_OEP.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_AIR_Detailed_OEP.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_KR_APAC_2010Q4_10Q4FX_USD_v10_E.MDF E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_OG_Int_CAR_EAR_2011_v10_E.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_Q3_2010_EDM_v10.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_Q3_TEST_EDM.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_Renewal_2010_DLMProfiles.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ACE_RMS_v11_ELT.mdf E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AFU_EDM_OUTWARDS.MDF


5. The 2nd is the output.csv which looks like this.

101105_RV_2011Rnwl_v2_R.mdf
110022_PNWH_FL_Q12011_v10_E.mdf
110022_PNWH_FL_Q12011_v10_R.mdf
110062_JIIC_2011_EDM.mdf 110062_JIIC_2011_RDM.mdf
110119_Jubilee_2011_EDM.mdf
110404_Allianz_UK_Tender_v11_E.mdf
110404_Allianz_UK_Tender_v11_R.mdf
110462_Generali_Q2_2011_USHU.mdf 110500_TMNF_AU_E.mdf


and so on.


Now I have two questions. 1 how do I remove the E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ from the file?

and 2 once thats been removed whats the best method to display both on a page - as stated earlier highlighting the ones which are NOT duplicates as there the ones which shouldnt be on the systems.

many thanks,

or perhaps a email alert saying, this, this and this database MDF file is not attached. Thats seems even better.

No one seems to have the skills to work this one out. I knew it wouldnt be a easy thing to do! If someone has the knowledge I'd be rateful to hear from you. regards

You can remove occurrences of text in a line by looping through each line and using the replace operator with an empty second parameter. In other words, "replace with nothing".

e.g.

$a = 'stuff one', 'stuff two', 'stuff three'
$a | foreach-object { $_ -replace 'stuff ' }

The outpue would be:
one
two
three

To your second question, one way to do this is to put both sets in an object. First of all, this means don't write to CSV during the script unless you need it for reference later. That's a waste of disk space. Do the work in memory, and write out to CSV if you want as the output at the end. Then use sort-object and compare-object cmdlets, or the contains operator. You might want to read the help files on all of these, there are examples.

So, let's say you have two sets:

$a = 'one', 'two', 'three'
$b = 'blue', 'black', 'three'

 
 Compare-Object -ReferenceObject $a -DifferenceObject $b -IncludeEqual 
  
 InputObject                                 SideIndicator                              
 -----------                                 -------------                              
 three                                       ==                                         
 blue                                        =>                                         
 black                                       =>                                         
 one                                         <=                                         
 two                                         <=                                         
 

PS> $a -contains 'three'
True

Hope this helps